Import CSV with column names that contain spaces

Hi all,

I am attempting to import a csv which has a heading row in the
following general form:

"col1","col2","col3","column #4","col5","column number six","col/field
#7","col8","col9","col10","column 11"

and I want data from columns 1, 2, 3, 4, 6, 8, 10, and 11

I have attempted many perturbations of the following SQL query:

LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
TABLE mydatabase.mytable
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (`col1`,`col2`,`col3`,`column #4`,`column number
six`,`col8`,`col10`,`column 11`);

The resultant set of data ends up that all columns, in sequential
order, are loaded (and truncated where "appropriate" based on the
table definition) disregarding any column specifiers. I have
attempted to stuff @dummy values in the columns where appropriate and
just haven't succeeded in getting the syntax correct.

I'm not married to having spaces in the column names (certainly!) but
I haven't determined the syntax to get the LOAD query to do the
expected translation from the csv header to my column names.

I am running on a _very_ stock copy of MySQL 5.1.23 on my laptop for
dev. Eventually this is going to be hosted on GoDaddy, the challenges
of which I'll have to figure out in due time...I'm sure...*gulp*

TIA

"keep the net free" --for those of you who remember

On 7 Jul, 06:10, miniman <rustywe...@gmail.com> wrote:
> Hi all,
>
> I have attempted many perturbations of the following SQL query:

perturbation (Noun):
a disturbance of motion, course, arrangement, or state of equilibrium;
especially : a disturbance of the regular and usually elliptical
course of motion of a celestial body that is produced by some force
additional to that which causes its regular motion

I don't think it is perturbations that you have been attempting!

On Jul 7, 2:05 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 7 Jul, 06:10, miniman <rustywe...@gmail.com> wrote:
>
> > Hi all,
>
> > I have attempted many perturbations of the following SQL query:
>
> perturbation (Noun):
> a disturbance of motion, course, arrangement, or state of equilibrium;
> especially : a disturbance of the regular and usually elliptical
> course of motion of a celestial body that is produced by some force
> additional to that which causes its regular motion
>
> I don't think it is perturbations that you have been attempting!

He's probably just feeling a bit perturbed.

On Jul 7, 2:10*am, miniman <rustywe...@gmail.com> wrote:
> Hi all,
>
> I am attempting to import a csv which has a heading row in the
> following general form:
>
> "col1","col2","col3","column #4","col5","column number six","col/field
> #7","col8","col9","col10","column 11"
>
> and I want data from columns 1, 2, 3, 4, 6, 8, 10, and 11
>
> I have attempted many perturbations of the following SQL query:

permutations ?

>
> LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
> TABLE mydatabase.mytable
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> LINES TERMINATED BY '\r\n'
> IGNORE 1 LINES (`col1`,`col2`,`col3`,`column #4`,`column number
> six`,`col8`,`col10`,`column 11`);

This form works for me:

LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
TABLE mydatabase.mytable
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1,col2,col3,col4,@skip,col6,@skip,col8,@skip,c ol10,col11);


>
> The resultant set of data ends up that all columns, in sequential
> order, are loaded ...
>
> TIA
>
> "keep the net free" --for those of you who remember

On Jul 7, 6:55 am, toby <t...@telegraphics.com.au> wrote:
> On Jul 7, 2:10 am, miniman <rustywe...@gmail.com> wrote:
>
> > Hi all,
>
> > I am attempting to import a csv which has a heading row in the
> > following general form:
>
> > "col1","col2","col3","column #4","col5","column number six","col/field
> > #7","col8","col9","col10","column 11"
>
> > and I want data from columns 1, 2, 3, 4, 6, 8, 10, and 11
>
> > I have attempted many perturbations of the following SQL query:
>
> permutations ?
>

Thank you for the suggestions of a seemingly more appropriate term for
the changes I have made to the query I used. Please refer to the
following paper and wikipedia article on the subeject:

http://www.rpi.edu/~holmes/Perturbation/TC.html

http://en.wikipedia.org/wiki/Perturbation_theory

I began with the obvious solution, and changed it up yo. I ain't
found the action that gets me some satisfaction so I post my shout out
to y'all.


>
>
> > LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
> > TABLE mydatabase.mytable
> > FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> > LINES TERMINATED BY '\r\n'
> > IGNORE 1 LINES (`col1`,`col2`,`col3`,`column #4`,`column number
> > six`,`col8`,`col10`,`column 11`);
>
> This form works for me:
>
> LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
> TABLE mydatabase.mytable
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> LINES TERMINATED BY '\r\n'
> IGNORE 1 LINES
> (col1,col2,col3,col4,@skip,col6,@skip,col8,@skip,c ol10,col11);
>

This is approximately what I meant by the @dummy variables used throw
the unwanted column into the bit bucket. The catch here is that some
of the columns have spaces and/or reserved words for names (not my db,
I'm a unwitting user ;) so, as I pointed out above, I quoted the
columns for safety's sake and still no juice. Thank you for your
constructive suggestion Toby *bow*

I am going to give in and just do pre-processing of the file to map
the source column names to something more sane. I hoped this would be
a simple matter of quoting away reserved words and spaces and have
spent too long not correcting other dba's egregious desgin flaw.

On Jul 7, 12:12*pm, miniman <rustywe...@gmail.com> wrote:
> On Jul 7, 6:55 am, toby <t...@telegraphics.com.au> wrote:
>
>
>
> > On Jul 7, 2:10 am, miniman <rustywe...@gmail.com> wrote:
>
> > > Hi all,
>
> > > I am attempting to import a csv which has a heading row in the
> > > following general form:
>
> > > "col1","col2","col3","column #4","col5","column number six","col/field
> > > #7","col8","col9","col10","column 11"
>
> > > and I want data from columns 1, 2, 3, 4, 6, 8, 10, and 11
>
> > > I have attempted many perturbations of the following SQL query:
>
> > * * * * * * * * * * * * permutations ?
>
> Thank you for the suggestions of a seemingly more appropriate term for
> the changes I have made to the query I used. *Please refer to the
> following paper and wikipedia article on the subeject:
>
> http://www.rpi.edu/~holmes/Perturbation/TC.html
>
> http://en.wikipedia.org/wiki/Perturbation_theory

I know what perturbation means. In fact I would never have picked on
it, except that other posters questioned your choice of word without,
oddly, mentioning what alternative they had in mind.

>
> I began with the obvious solution, and changed it up yo. *I ain't
> found the action that gets me some satisfaction so I post my shout out
> to y'all.
>
>
>
>
>
> > > LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
> > > TABLE mydatabase.mytable
> > > FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> > > LINES TERMINATED BY '\r\n'
> > > IGNORE 1 LINES (`col1`,`col2`,`col3`,`column #4`,`column number
> > > six`,`col8`,`col10`,`column 11`);
>
> > This form works for me:
>
> > LOAD DATA LOCAL INFILE 'c:/mysql/data/mydatabase/mydata.csv' INTO
> > TABLE mydatabase.mytable
> > FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
> > LINES TERMINATED BY '\r\n'
> > IGNORE 1 LINES
> > (col1,col2,col3,col4,@skip,col6,@skip,col8,@skip,c ol10,col11);
>
> This is approximately what I meant by the @dummy variables used throw
> the unwanted column into the bit bucket. *The catch here is that some
> of the columns have spaces and/or reserved words for names (not my db,
> I'm a unwitting user ;) so, as I pointed out above, I quoted the
> columns for safety's sake and still no juice. *Thank you for your
> constructive suggestion Toby *bow*

It works exactly as advertised for me (5.0.40). If you can show that
spaces in column names are the bug, then remove the spaces and that's
your workaround (and please report the bug to MySQL).

>
> I am going to give in and just do pre-processing of the file to map
> the source column names to something more sane. *I hoped this would be
> a simple matter of quoting away reserved words and spaces

On the face of it, that should be all that's required. However you did
state that you weren't attached to the idea of column names with
spaces.

> and have
> spent too long not correcting other dba's egregious desgin flaw.

<snip>

> It works exactly as advertised for me (5.0.40). If you can show that
> spaces in column names are the bug, then remove the spaces and that's
> your workaround (and please report the bug to MySQL).
>
</snip>

I recreated all column names to well formed identifiers, yet still non-
contiguous in the csv, and still the load reads in contiguous columns
as opposed to the column-referenced values. It must be a case of
PEBKAC. I'll post the root cause and solution if ever found.

Thank you Toby *bow*