asked on

Attempting to use mysqlimport to import csv file

I'm using the following syntax to import a csv file using mysqlimport:

mysqlimport -c security,notional_amount,cusip,category -d -h localhost -u root -p maiden_lane C:\dev\mysqlimport\securities_stage.csv

But I keep getting the following error:
mysqlimport: Error: 1261, Row 1 doesn't contain data for all columns, when using
table: securities_stage

I suspect it has something to do with the -c (columns) flag. I'm not sure if I have that specified in the correct syntax.

Here's the structure of the first few lines of the csv file:

"ACABS_07-3A X","5,596,000","00083MAA9","Cash"
"AIRLE_06-1 C 144A","200,000","009368AD3","Cash"
"APID_07-5 C 144A","3,000,000","03761XAF7","Cash"

k_murli_krishna

There is sets of 4 string data in your CSV file. Check the number of columns in table. It should be more. See what data is mapping to what columns. Also, see for unmapped columns that they should be NULL or have a DEFAULT defined.

This is apart from PK column unless it is an auto-increment one. Also, see order of columns to match with order of data since we cannot customize like in an INSERT.

Normally, new line/carriage return character will be present at end of each row in CSV file. Check this by opening in text editor and using right arrow key whether automatically going to new line or not.

gmckeown99

mysqlimport assumes tab-delimited by default. You have to add the --fields-enclosed-by=" and --fields-terminated-by=, to import the data you pasted in.

ASKER CERTIFIED SOLUTION

k_murli_krishna

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

opike

ASKER

Well I think I made progress. I changed the command line statement to this:

mysqlimport --columns=security,notional_amount,cusip,category -d -h localhost -u root -p --fields-terminated-by=, --fields-enclosed-by="\"" --fields-escaped-by=\\ --debug-info maiden_lane C:\dev\mysqlimport\securities_stage.csv

And now I'm getting a different error message:
mysqlimport: Error: 1265, Data truncated for column 'Notional_Amount' at row 1,
when using table: securities_stage

I wonder if the commas in the numeric value are causing problems?

opike

ASKER

I did a test where I took just the first 4 lines and manually removed the commas from the numbers in the second column and that worked.

Any suggestions on how I can get it to work with the commas in there?

k_murli_krishna

An example from one of my links:

mysqlimport --fields-optionally-enclosed-by=""" --fields-terminated-by=, --lines-terminated-by="\r\n" --user=YOUR_USERNAME --password YOUR_DATABASE YOUR_TABLE.csv

Your --fields-enclosed-by="\"" is wrong. You need to correct it as above. If still there is some problem, do not specify --fields-escaped-by=\\ since you do not have \ as a character in your data.

But this may be okay. One more thing you can try is --fields-terminated-by=',' OR --fields-terminated-by="," i.e. specifying comma in single/double quotes.

k_murli_krishna

For some of the options refer to last link in my previous post.

opike

ASKER

I can get it to work for the most part by mysqlimport doesn't appear to be able to handle the following situation:

"ACABS_07-3A X","5,596,000","00083MAA9","Cash"

where commas are contained inside a quoted value.

k_murli_krishna

Try this:

LOAD DATA INFILE 'C:\dev\mysqlimport\securities_stage.csv' INTO TABLE securities_stage (security,notional_amount,cusip,category)
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

If there are no headings, then IGNORE 1 LINES can be omitted. Also, please refer to my very first post to keep in mind once again. You can load data files by using the mysqlimport utility; it basically operates by sending a LOAD DATA INFILE statement to the server.