List:General Discussion« Previous MessageNext Message »
From:Jake Peavy Date:March 14 2006 7:38pm
Subject:Re: 'Into outfile' doesn't include the column names. How can it be done?
View as plain text  
On 3/14/06, SGreen@stripped <SGreen@stripped> wrote:

>
>
> "Jake Peavy" <djstunks@stripped> wrote on 03/14/2006 01:52:28 PM:
>
>
> > On 3/10/06, Jake Peavy <djstunks@stripped> wrote:
> > >
> > >  On 3/7/06, C.R.Vegelin <cr.vegelin@stripped> wrote:
> > >
> > > > Hi Ariel,
> > > >
> > > > Maybe this example helps you to create CSV output from MySQL.
> > > > The first SELECT generates the headerline; the second the data.
> > > > ( SELECT 'FieldA','FieldB','FieldC', ... )
> > > > UNION
> > > > ( SELECT `FieldA`, `FieldB`, `FieldC`, ...
> > > > INTO OUTFILE 'D:/MySQL Datafiles/myFile.csv'
> > > > FIELDS TERMINATED BY ','
> > > > LINES TERMINATED BY '\r\n'
> > > > FROM ... ...
> > > > GROUP BY `FieldA`, `FieldB`, `FieldC`, ...
> > > > );
> > > >
> > > > Don't forget the braces ( and ).
> > > > HTH, Cor
> > >
> > >
> > >  So, to all the SQL wizards out there...
> > >
> > > How would one perform this same operation, but using the
> > > INFORMATION_SCHEMA virtual db to provide the column headings in the
> first
> > > row rather having to hand type them as shown above?
> > >
> > > Bonus marks for beginning the line with a #....
> > >
> > > My attempt stalled as soon as I got one column of data in the result
> set
> > > with a row for each column of the target table.
> > >
> > > SELECT COLUMNS FROM INFORMATION_SCHEMA WHERE TABLE_SCHEMA = 'test' AND
> > > TABLE_NAME = 'testtable'
> > >
> > > Ideas?
> > >
> > > TIA,
> > >  -jp
> > >
> > >
> >
> > Nobody has any ideas at all?  (sheds solitary tear)
> >
> > -jp
>
> Nope, no suggestions.
>
> You are mixing purposes (not to mention data types) when you add column
> names to the output of INTO OUTFILE. Have you looked at using the CSV
> storage engine or mysqldump as alternatives?
>

Yes, but based on the docs it would appear that it doesn't produce column
names either (presumably because the table definition is stored in the
.frm):

mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM test;
+------+------------+
| i    | c          |
+------+------------+
|    1 | record one |
|    2 | record two |
+------+------------+
2 rows in set (0.00 sec)

If you examine the test.CSV file in the database directory after executing
the preceding statements, its contents look like this:

"1","record one"
"2","record two"

I didn't actually try this, however, because my version hasn't been built to
include the CSV engine.  I could, of course, rebuild, but it didn't look
like it would do what I wanted anyway.

I think this (INTO OUTFILE w/column names) would be used infrequently, but
it would be handy to send a small subset of data to someone so they could
pivot it anyway they like in Excel.

Appreciate the info though, if anyone else has any ideas....

How would one transform a single column of rows into a single row of
columns?  Seems like part of a crosstab query...

-jp

Thread
'Into outfile' doesn't include the column names. How can it be done?Ariel S├ínchez Mora6 Mar
  • Re: 'Into outfile' doesn't include the column names. How can it be done?SGreen6 Mar
  • RE: 'Into outfile' doesn't include the column names. How can it be done?Marciano [Intercol]6 Mar
  • Re: 'Into outfile' doesn't include the column names. How can it be done?C.R.Vegelin7 Mar
    • Re: 'Into outfile' doesn't include the column names. How can it be done?Jake Peavy10 Mar
      • Re: 'Into outfile' doesn't include the column names. How can it be done?Jake Peavy14 Mar
        • Re: 'Into outfile' doesn't include the column names. How can it be done?SGreen14 Mar
          • Re: 'Into outfile' doesn't include the column names. How can it be done?Jake Peavy14 Mar
Re: 'Into outfile' doesn't include the column names. How can itbe done?Paul DuBois6 Mar