Databases
StackOverflow2013
Postsdbo
POImporting nulls and commas into MYSQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImporting nulls and commas into MYSQL
    primarykey
    data
    text
    <p>I am importing a file into MYSQL like this:</p> <pre><code>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) </code></pre> <p>And a sample of some of the import data:</p> <pre><code>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 </code></pre> <p>The first problem is using \N. When looking at my database after importing, instead of getting nulls, i get "N" in the iIngredients field.</p> <p>The second problem is most of the rows don't get imported, here is a sample of the errors I am getting:</p> <pre><code>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 </code></pre> <p>I've verified that every row has any data that may contain commas to be wrapped in "".</p> <p>Any ideas what might be going on?</p> <p><strong>EDIT:</strong> I was able to solve the \N issue by just using "" for string rows and \N for non-string rows.</p> <p><strong>EDIT 2:</strong> 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 ".</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJustin Mathieu
    1. USJustin Mathieu
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COCan you go through the `\N`s and replace them with blanks so you are inserting `,,`? Can you show us some of the rows that were truncated?
      singulars
      1. POImporting nulls and commas into MYSQL
      1. USKermit
    2. COThis is a bug in MySQL itself. [See the link](http://www.briandunning.com/errors/529)
      singulars
      1. POImporting nulls and commas into MYSQL
      1. USbkizer1
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload