LOAD DATA INFILE in MySQL

[polorboy]

Ok, I have a huge file in csv format that I need to add to a table in MySQL. I have the table made and setup with the correct columns and stuff, and my csv file is setup in the exact same way. I am trying to use LOAD DATA INFILE with MySQL to have it automaticaly load all the data into the table but I keep getting an error telling me that "Row 1 doesn't contain data for all columns". The thing is that it doesn't but I need to have those columns in there so that the data can be entered at a later date. How can I use the LOAD DATA INFILE to add a ton of data to a MySQL table without having information filled in all the columns right away. The columns that are empty are address information and stuff that is going to be added to the table at a later date and when new data is entered. Any help would be great. Thanks.

[NightShift58]

Use ",," (w/o quotes) as a placeholder for empty columns.column1,column2,,column4,,column6

[chuk4shep7]

i found that you need a value in all rows in all fields. enter \N in all the empty rows. If you are using auto_increment, enter \N for all rows. Save .csv file as tab delimited text file. Use 'load data infile' to insert rows.

i hope this helps

:eek:

[NightShift58]

If you're going to use a tab-delimited file instead of a comma-delimited (which is what a .csv is), the same principle applies.

The error you are getting is based on missing columns. Instead of ",," use "\t\t" (double tabs) in a tab-delimited file.

[chuk4shep7]

nightshift58. Whats your point? The problem is missing data, not columns.

polorboy: just make sure there is a value for all rows in all fields. use \N for empty fields. (columns to you, nightshift58)

[NightShift58]

nightshift58. Whats your point? The problem is missing data, not columns.Please re-read the OP. Here's a quote from the posted error message:Row 1 doesn't contain data for all columns". The problem are not the missing rows but the number of columns that are are expected in each row.

As the server tries to import the CSV into a table, it knows that the table consists of - for example - 12 fields/columns. If all 12 fields/columns are required, when it reads the first line of the CSV file and only finds 10 fields/columns, it will tell you that something is wrong, as it did in this case. If the (missing) fields/columns in the CSV were properly delimited, this error would possibly not occur.

Adding the characters "\N" where empty fields/columns exist will not help, if the table fields are defined as "NOT NULL". In case you meant "\n", then, by default, that would be the row/line delimiters and not a field/column delimiter.

polorboy: just make sure there is a value for all rows in all fields. use \N for empty fields.If that were possible, the OP wouldn't have posted in the first place.(columns to you, nightshift58)No, not columns to me. Columns to MySQL (see MySQL error message) and the OP, who used that word to describe his format. In formulating an answer, I simply tried to use the same words to mean the same things he and the server were using. The CSV likely originated from Excel and I could have referred to "cells", even "tuples". But what would be the point - other than risk confusing everyone as to what I meant?

Suggested reading on field/column and row/line delimiters:
http://dev.mysql.com/doc/refman/4.1/en/load-data.html

[chuk4shep7]

Apart from contradicting yourself, Yours isnt the only way to do things.

When you learn a bit more you'll find a lot of things are possible.

Grow up a bit.

[polorboy]

Well, to stop this childish bickering before it goes any further I created my MySQL table based on my csv file, and I know for a fact that they both have the correct amount of columns in them and all the empty columns in my csv file are delimited by ",,". So, that should stop that. I have been reading into it and it does seem that MySQL wants to have all the data filled in for all the fields before it is added with that command. Do any of you guys know what I could do to fool MySQL into thinking that there is something in those fields while leaving them empty so I can add data later?

[NightShift58]

Apart from contradicting yourself, Yours isnt the only way to do things.

When you learn a bit more you'll find a lot of things are possible.

Grow up a bit.My answer was related to the question being asked and trying to find a solution.

It seems you would rather talk about me. I don't think this forum was meant for that, so excuse me if I don't indulge you.

On the flip side, I'll have to admit that I would very much like to grow "up". At my age, the only growing I've been doing is "wide".

[NightShift58]

Do any of you guys know what I could do to fool MySQL into thinking that there is something in those fields while leaving them empty so I can add data later?If you use the field delimiter clause[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']you could create empty fields ,'','',, or any other default value you want to use that doesn't evaluate to NULL (or \N).

[polorboy]

On the flip side, I'll have to admit that I would very much like to grow "up". At my age, the only growing I've been doing is "wide".


lol, ditto... :p