CODEWITHSUNDEEP
mysqlcsvimportnull
Justin Mathieu
Justin Mathieu

Reputation: 471

Importing nulls and commas into MYSQL

I am importing a file into MYSQL like this:

LOAD DATA LOCAL INFILE 'C:\\Users\\Justin.Mathieu\\Documents\\data.csv' into table Ingredients
fields terminated by ','
optionally enclosed by '"' 
lines terminated by '\n'
(iVendor, iName, iOrderNumber, iCaseQty, iUnitSize, iDrainSize, iUnitSoldBy, iCaseCost, iVolumeConversion, iUnitLabel, iIngredients)

And a sample of some of the import data:

VendorName,"Apple Whole Peeled Cored Grd A Fz",61318,1,\N,\N,\N,38.17,\N,\N,\N
VendorName,"Apples- Applesauce Sweetened",10738,6,\N,\N,-1,24,\N,\N,\N
VendorName,"Applesauce Unswtn Fancy",10676,6,106,\N,-1,23.97,\N,\N,"Apples, Water, Erythorbic Acid (To Maintain Color)"
VendorName,Apron 24"X42" White Reg Wt - Dispenser ,81463,1,\N,\N,\N,9.98,\N,\N,\N
VendorName,"Artichoke Heart 40-50Cnt Quartered,     Marintated",10722,6,88,\N,-1,71.46,\N,,"Artichoke Hearts, Vegetable Oil (Soya Or Sunflower), Water, Vinegar"
VendorName,"Artichoke Heart Crse Ct",77334,6,\N,55,\N,49.16,\N,\N,\N
VendorName,"Artichoke Heart Quarters",11804,6,\N,\N,-1,62.46,\N,\N,"Artichoke Hearts, Water, Salt And Citric Acid"
VendorName,"Artichoke Quarters Mixed Cuts",D6808,6,\N,\N,\N,60.26,\N,\N,\N
VendorName,"Bacon Bit Real Ref",56188,2,\N,\N,\N,51.22,\N,\N,\N
VendorName,"Barley Pearled Dry Us #1 Standard",33050,2,\N,\N,\N,21.38,\N,\N,\N

The first problem is using \N. When looking at my database after importing, instead of getting nulls, i get "N" in the iIngredients field.

The second problem is most of the rows don't get imported, here is a sample of the errors I am getting:

164 row(s) affected, 64 warning(s): 
1262 Row 3 was truncated; it contained more data than there were input columns 
1262 Row 5 was truncated; it contained more data than there were input columns 
1262 Row 9 was truncated; it contained more data than there were input columns 
1262 Row 10 was truncated; it contained more data than there were input columns 
1262 Row 11 was truncated; it contained more data than there were input columns 
1262 Row 12 was truncated; it contained more data than there were input columns 
Records: 164  Deleted: 0  Skipped: 0  Warnings: 94

I've verified that every row has any data that may contain commas to be wrapped in "".

Any ideas what might be going on?

EDIT: I was able to solve the \N issue by just using "" for string rows and \N for non-string rows.

EDIT 2: In the rows that got truncated, the iIngredients field contains all of the data for the following row, as if it isn't detecting a newline for rows that end in ".

Upvotes: 1

Views: 3289

Answers (3)

iglen_
iglen_

Reputation: 311

mysql doesn't recognize '\n' for new line

Upvotes: 0

Devart
Devart

Reputation: 122032

It looks like a problem with line separators, check if they are '\n' in your file, or try to use '\r\n' in LOAD DATA command.

Also, I do not see anything wrong with \N, it works as is.

Upvotes: 0

Justin Mathieu
Justin Mathieu

Reputation: 471

Well after noodling around enough I figured it out.

The issue is that when I was converting the file to insert "/N" for null fields (in C#), I was using:

Write(rowData + Environment.NewLine);

Apparently MySQL really didn't like that, so I changed it to:

Write(rowData + "\n");

That seemed to work well enough.

Upvotes: 2

Related Questions