CODEWITHSUNDEEP
mysqlimport
Dave
Dave

Reputation: 59

Import text file into MySql

I have been trying for the last day or so to import a text file of records exported from Access into MySQL using phpMyAdmin. The fields are delimited by a pipe "|" and the fields are surrounds by quotes "". I am not totally sure of the line endings, however, I have tried using auto, \N, \r, ] \r\n all to no avail. It only imports the first record and that is it. Very maddening. I have search the internet for clues and they all say to make sure the field types and numbers all match ... which they do. I am at my wits end so I am hoping someone can save me from this ;-) Basic format of the text file is as follows:

|"CBCF"|"CBOFT"|"Provincial"|"yes"|"Newsroom"|"Control Room"|"Prog. Director"|"News Director"|"OPS Manager"|"Engineer/IT"|"General Manager"|"General Sales Manager"|""|""|""|"514-597-5353"|""|"514-597-5353"|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|""|"613-XXX-XXXX"|""|""|""|""|""|""|""|""|""|""|"[email protected]"|""|""|""|""|""|""|""|""|""|""|"EASTERN"|"Radio-Canada French Services"|15/5/2008 9:38:12|22/7/2010 15:00:28|"***Provincial contact for RDI, our 24 news service, and Radio Internet service."

Upvotes: 1

Views: 3257

Answers (2)

Dave Filchak
Dave Filchak

Reputation: 51

In case this helps someone, this is what I did to get the import to work.

After receiving all sorts of duplicate key errors or only the first record importing I looked closer at how I had my PRIMARY KEY set up. It was set up as TINY INT and AUTO INCREMENT. So I deleted the column and then re-inserted it back in, this time as SMALL INT, unsigned, AUTO INCREMENT, and then the import worked properly. So the keys here where to make sure the CSV or delimited text file was formated properly ( I recommend a "pipe" character as the delimiter and no quotes around fields ), that your database table fields match your text file exactly ( at least in terms of the number of columns ) and that your PRIMARY KEY is set up correctly. At least this is what worked for me.

Dave

Upvotes: 1

Mark Loeser
Mark Loeser

Reputation: 18627

Have you tried using mysqlimport on the command line? It should be as simple as:

mysqlimport --fields-terminated-by="|" --fields-enclosed-by="\"" your_db table_name.txt

Upvotes: 0

Related Questions