We help IT Professionals succeed at work.

mysqlimport csv

ststesting
ststesting asked
on
I'm importing a csv using mysqlimport as seen below.

mysqlimport --local --user=root -p --fields-terminated-by=, --lines-terminated-by="\n"
--fields-enclosed-by="\"" --ignore-lines=1 --replace db c:/tblcsv.csv

This works fine on my dev machines (win 7/wamp 2.0).

Doing this on my server (2008) only imports the first record.  So I went for --lines-terminated-by="\r\n" and "\\r\\n" but get still only get the first record.

ODDITIES AND NOTES
I noticed that after successfully importing on a dev machine, the last field in the records (which are blank in the csv) have " in them after importing.

The first field in the csv is blank. (autoinc when imported)
Comment
Watch Question

If you use local make sure that the file is on the same machine where you launch the import. if you are on that machine try the import but without --local.
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Can you post a sample of your file?  If, as you suspect, there are some odd characters on the ends of the lines, it's difficult to give you a possible solution without looking at the lines (I want to do an octal dump on it to see exactly what is at the end of the lines)

Also, you can't do a cut-and-paste, as that will strip any control characters.  Maybe copy it, open it up in a text editor, and then delete lines 5 on, and then post that here?
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Octal dump shows windows style line endings, so "\r\n" should be what you want.  When I do your import on my Mac I see the same as on your dev machine - the last field is not empty.  However, when I use "\r\n", the field is correctly blank.

It's odd that you tried both "\r\n" and "\\r\\n" and in *both* cases got the first record but no subsequent records.

What happens if you don't specify '--lines-terminated-by' at all?  The default is "\n", so it should load, but the last field should be messed up (contain a double quote and a carriage return).  I'm just seeing if we can get more than one row to load at this point.

Author

Commented:
Thanks for the reply/help

All of \n , \r\n, \\r\\n, "blank"
just bring in the first record on the server.



Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Weird... have you tried importing the records from another machine?  ie go to your dev machine and run your import command:
mysqlimport --local --fields-terminated-by="," --lines-terminated-by="\r\n" --fields-enclosed-by='"' --ignore-lines=1 --replace db c:/tblcsv.csv

Open in new window


Maybe 'mysqlimport' on your other server has some issue (or is old).

Author

Commented:
Running the command on my machine seems to work fine besides plugging in the mystery " and T.  I'm not using those columns anyway.


The csv I'm bringing in doesn't have a unique key.

I made a bat that adds a column to the beginning of the file, and then when it's imported, I was hoping to use the empty field at the beginning to let it autoinc on import.  

So the CSV has the first field as blank , fielda, fieldb,
or "","fielda","fieldb",

It looks like this is acceptable on my machine but not the server, and it must have a value.  Maybe I can add it as the last field...

If I use the --columns= option, can the field names have spaces?  ( I assume not and I can't get around it)  
IT Supervisor
Top Expert 2009
Commented:
Having a blank field in your CSV should be fine to get a unique key, as long as the table you're importing to has it defined for that column (which is sounds like you've already done).

Yes, you can define columns with spaces, but it's a little tricky:
 
--columns='`Subscriber ID`,`Subscription Name`'

Open in new window


The entire argument to --columns= must be enclosed in single quotes.  Each field name within that argument must be enclosed with *backticks* and comma separated.

Author

Commented:
Moving the primary AI column to the end of the table fixes this for me as a workaround.  

Thanks so much for your help and the --columns syntax, I couldn't find that anywhere.!
Nem SchlechtIT Supervisor
Top Expert 2009

Commented:
Yeah, that's a tricky one!  Just gotta remember that MySQL likes using backticks.  Glad I could help. ;-)