CODEWITHSUNDEEP
mysql
Fiend Busa
Fiend Busa

Reputation: 57

Row does not contain data for all columns

Im trying to import a text file containing:

http://pastebin.com/qhzrq3M7

Into my database using the command

Load data local infile 'C:/Users/Gary/Desktop/XML/jobs.txt' 
INTO Table jobs 
fields terminated by '\t';

But I keep getting the error Row 1-13 doesn't contain data for all columns

Upvotes: 3

Views: 17820

Answers (5)

zeusstl
zeusstl

Reputation: 1865

Make sure there are no "\"s at the end of any field. In the csv viewed as text this would look like "\," which is obviously a no-no, since that comma will be ignored so you won't have enough columns.

(This primarily applies when you don't have field encasings like quotes around each field.)

Upvotes: 0

Luis Niebla
Luis Niebla

Reputation: 11

If you're on Windows, make sure to use the LINES TERMINATED BY \r\n as explained by the mariadb docs

Upvotes: 1

dctalbot
dctalbot

Reputation: 113

Make sure the last field of each row ends with \t. Alternatively, use LINES TERMINATED BY

LOAD DATA LOCAL INFILE 'C:/Users/Gary/Desktop/XML/jobs.txt' INTO TABLE jobs COLUMNS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r';

\r is a carriage return character, similar to the newline character (i.e. \n)

Upvotes: 3

Rajasekar Kalisamy
Rajasekar Kalisamy

Reputation: 99

I faced same issue. How i fixed the issue:

  1. Try to open the CSV file using Notepad++ (text editor)
  2. I've seen a blank line at the end of my file, I've deleted it.

-- Hurrah, it resolved my issue.

Below URL also can help you out to resolve the issue. http://www.thoughtspot.com/blog/5-magic-fixes-most-common-csv-file-problems

Upvotes: 2

Andras
Andras

Reputation: 3055

sounds like load data local infile expects to see a value for each column.

You can edit the file by hand (to delete those rows -- could be blank lines), or you can create a temp table, insert the rows into a single column, and write a mysql command to split the rows on tab and insert the values into the target table

Upvotes: 0

Related Questions