Mysql load data local infile?

Column names in CSV are not necessary, so you should add IGNORE 1 LINES clause.

Up vote 1 down vote favorite share g+ share fb share tw.

I'm trying to load data into a mysql table using LOAD DATA LOCAL INFILE using the code below. Mysql: LOAD DATA INFILE '/var/www/vhosts/domain.com/httpdocs/test1.csv' INTO TABLE temp_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (recloc,client_acc) Edit: changed LOAD DATA LOCAL INFILE to LOADA DATA INFILE, removed SET id=null, added IGNORE 1 LINES I'm getting no errors and no imported records. I believe the issue is related to the column names but i'm having a hard time fully understanding what those names should be.

Should they be the actual column names within the CSV? Or the field names in the DB Table? I would also like the have an auto_incremented primary key (id).

CSV: recloc,client_acc "NLGSX3","CORPORATE" "7SC3BA","QUALITY ASSURANCE" "3B9OHF","90717-6710" Any suggestions to what I may be doing wrong? Thanks! Mysql import load-data-infile link|improve this question edited Dec 14 '10 at 18:18 asked Dec 14 '10 at 16:44Stephen116212 87% accept rate.

Column names in CSV are not necessary, so you should add IGNORE 1 LINES clause. Columns in your query (recloc,client_acc) need to match columns in table. First column from CSV will be inserted into recloc, second into client_acc.

If you don't specifu AUTO_INCREMENT column in the statement, but there is one in the table, it should fill automatically.

IGNORE 1 LINES" added, removed "SET id=null", Still no luck. I'm also storing the CSV in the directory of the import script but i've also tried the server path. Shouldn't I be getting some kind of mysql error?

– Stephen Dec 14 '10 at 17:31 After more research I changed LOAD DATA LOCAL INFILE to LOAD DATA INFILE since i'm using files stored on the server. – Stephen Dec 14 '10 at 18:18.

Up vote 0 down vote favorite share g+ share fb share tw.

I use LOAD DATA INFILE in mysql. In my input file I have "x" character but I have to save it to database as NULL. How can I do it?

Mysql null load-data-infile link|improve this question asked Jan 18 '11 at 11:29Alock Leo626 89% accept rate.

Load data infile... into table ... fields terminated by... lines terminated by ... ( my_field... ) set my_field = if(my_field = 'x', null, my_field).

It is pretty useful but it first assigns x to date then changes to null. Which is violates the rule that "date can not be 'x'" – Alock Leo Jan 18 '11 at 11:49 huh? As it's loading it checks the value of my_field.

If it's 'x' it writes a null otherwise it writes the original value. – f00 Jan 18 '11 at 11:51 oh it's a date field - would have been helpful if you'd mentioned that earlier! – f00 Jan 18 '11 at 12:08.

LOAD DATA INFILE was not designed to substitute data during import. Consider doing a UPDATE table SET column = REPLACE(column, "x", ""); query after the import.

But there is one problem; the column contains "date" then I can not save it as "x". So I can not update the colum. – Alock Leo Jan 18 '11 at 11:35 @Alock not sure I understand, but if you need to change its type, then do a ALTER TABLE – Pekka Jan 18 '11 at 11:36 I have data type column.

It consists lots of date. I read the input file. If the input is "x", then I need to save NULL to database.

Therefore I can not alter column type. – Alock Leo Jan 18 '11 at 11:39 @ALock aha, I see! That's different.

But the date should be stored as 0000-00-00 in that case anyway, shouldn't it? Either way, I would edit the question to make clear what you need. – Pekka Jan 18 '11 at 11:40.

Load data infile... into table ... fields terminated by... lines terminated by ... ( @var... ) set my_field = if(@var = 'x', null, @var); Thanks to pekka.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.