Load Data Problem

[ddaily]

I'm importing a large amount of data into a table. The first row loads fine, but for the rest of the rows the first field loads improperly keeping the “.

load data infile 'hrpdf.csv' into table pdfhr

FIELDS TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'r'

(ssn,bigname,street,city,state,zipcode,homephone,fullperc,acrdate,hiredate,

probend,apptbud,loareason,loareturn,termcode,homedept,unitnumb,

empstat,p_t,p_f,emptype,cmpsmail,wphone,awphone,voicemail,faxnumb,

campadd,email,permaddr,permcity,permstate,permzip,emcont,emphone,

aemphone,ctznvisa,citcntry,resident,race,hispan,handic,sex,milstat,

incrmo,jobentry,jobclass,bargnunit,unioncode,edate,lastpaid,

uwid,salary,step,aptno) ;

FIRST ROWS: (second row start with “832780374” and ends up truncated)

“831544256”,”AARHAUS, PATRICK A”,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”,”98059-3331″,”4252713671″,”100.00″,”1978-06-12″,”1978-06-12″,””,”743564″,”00″,””,”00″,”043560″,”0153″,”A”,”P”,”F”,”B”,”352160″,”2066851483″,”2066851416″,””,””,”Physical Plant Annex 4″,”[email protected]”,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”,”98059-3331″,”Aarhaus, Mike”,”2069378450″,””,”CT”,”US”,””,”800″,”999″,”A”,”M”,”X”,”12″,”1978-06-12″,”8828″,”06″,”01″,”2005-10-24″,”2005-10-15″,”875003841″,”4,089.00″,” K”,”1″

“532780374”,”ABBOTT, JAMES (MIKE)”,”13733 50th Dr. SE”,”EVERETT”,”WA”,”98208-9558″,”4253378011″,”100.00″,”2000-04-17″,”2000-04-17″,””,”143511″,”00″,””,”00″,”143511″,”0150″,”A”,”P”,”F”,”B”,”354285″,”2066851580″,””,””,”2066851877″,”Maint. & Alts. Shop 53B”,””,””,””,””,””,”Stacey Abbott”,”4252103070″,”4253378011″,”CT”,”US”,””,”800″,”999″,”A”,”M”,”X”,”5″,”2000-04-17″,”8848″,”06″,”01″,”2005-10-24″,”2005-10-15″,”874003997″,”3,722.00″,” K”,”1″

RESULT:

“831544256”,\N,\N,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”,”98059-3331″,”4252713671″,\N,100.000,1978-06-12,1978-06-12,0000-00-00,\N,\N,”743564″,\N,\N,\N,\N,\N,0,0000-00-00,\N,0,\N,\N,”043560″,”0153″,”A”,”P”,”F”,”B”,\N,\N,\N,”352160″,”2066851483″,\N,”2066851416″,\N,””,””,”Physical Plant Annex 4″,”[email protected]”,\N,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”,”98059-3331″,”Aarhaus, Mike”,”2069378450″,””,\N,\N,”CT”,\N,”US”,””,\N,800,999,”A”,”M”,”X”,\N,\N,12,1978-06-12,”8828″,\N,\N,\N,\N,4.00,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,”06″,”01″,2005-10-24,\N,2005-10-15,\N,\N,\N,\N,\N,” K”,\N,\N,\N,\N,\N,\N,\N,”875003841″,”AARHAUS, PATRICK A”,1,\N,\N,\N

“

“8327803”,\N,\N,”13733 50th Dr. SE”,”EVERETT”,”WA”,”98208-9558″,”4253378011″,\N,100.000,2000-04-17,2000-04-17,0000-00-00,\N,\N,”143511″,\N,\N,\N,\N,\N,0,0000-00-00,\N,0,\N,\N,”143511″,”0150″,”A”,”P”,”F”,”B”,\N,\N,\N,”354285″,”2066851580″,\N,””,\N,””,”2066851877″,”Maint. & Alts. Shop 53B”,””,\N,””,””,””,””,”Stacey Abbott”,”4252103070″,”4253378011″,\N,\N,”CT”,\N,”US”,””,\N,800,999,”A”,”M”,”X”,\N,\N,5,2000-04-17,”8848″,\N,\N,\N,\N,3.00,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,\N,”06″,”01″,2005-10-24,\N,2005-10-15,\N,\N,\N,\N,\N,” K”,\N,\N,\N,\N,\N,\N,\N,”874003997″,”ABBOTT, JAMES (MIKE)”,1,\N,\N,\N

“

Thanks for any help.

[peterlaursen]

Please paste in the 'create statement for the table' too!

And inform about MySQL and otehr program versions.

Do you use SQLyog? How? csv-import functionality or just executing an external script?

You are sure that lines must not be terminated by 'rn' (depends on the origins of the file – most windows' program use 'rn' or in ASCII/ANSI

http://www.webyog.com/faq/28_73_en.html

[peterlaursen]

you'd better explain

Quote:

FIRST ROWS: (second row start with “832780374” and ends up truncated)

There is no “832780374” in the data! Do you mean that “532780374” becomes “8327803” ?

How did you print out the data/result? Where did you get those \N 's from?

Please use ONLY SQLyog and no other program when posting here.

But this is interesting: “8327803”

the ” -character has been escaped!

You will need to tell THE WHOLE STORY. Where do the data come from? How did you generate the txt/csv file. How EXACTLY did you try to import (which program which functionality) ? How do you list the output? What are the program versions. We can't GUESS all these things!!!

Did you consider using a better method than LOAD DATA …

Also try execute this SQL

Code:

select @@sql_mode;

. I do suspect that the server is running in ANSI-mode of some variant of it.

[peterlaursen]

I think I got it!

You will need to use an ESCAPED BY too, because this

Quote:

AARHAUS, PATRICK A

contains a comma. And you also use comma as field delimiter.

Or you could create a new fille with another delimiter and enclose-character that is not in the dataset. Could be ; (semicolon) or | (pipe) for instance.

When the MySQL parser has made the first escape-parsing error everything becomes unpredictable. The delimiter-character (probably!) is 'higher' in the evaluation hierachy than the enclose-character.

I copied the first 5 fields of your two first row and imported from SQLyog GUI. I executed

Code:

load data local infile 'C:/Documents and Settings/Peter/Skrivebord/ttt.csv' into table `test`.`ttt` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `id`, `t1`, `t2`, `t3`, `t4` )

next I exported the table to .csv (using defaults) and the file reads:

Quote:

“831544256”,”AARHAUS, PATRICK A”,”9026 133RD AVE SE”,”NEWCASTLE”,”WA”

“532780374”,”ABBOTT, JAMES (MIKE)”,”13733 50th Dr. SE”,”EVERETT”,”WA”

However a SELECT * returns

Code:

       id  t1                    t2                 t3         t4   
———  ——————–  —————–  ———  ——
831544256  AARHAUS, PATRICK A    9026 133RD AVE SE  NEWCASTLE  WA   
532780374  ABBOTT, JAMES (MIKE)  13733 50th Dr. SE  EVERETT    WA   

It is a matter of escaping! Or to be perfectly sure that the data don't contain any character having a special meaning with MySQL or that you use for another purpose!

I too get weird results when not escaping – even with that reduced dataset!

[peterlaursen]

Please check this: http://www.webyog.com/faq/6_26_en.html .

It says

Quote:

* Also a reproduceable test case is always much appreciated

You'll need to give every bit of information needed to reproduce the problem.

And a reproduceable test case is a test case – that is a simplified one with a few data rows and colums only and ALL relevant information. You are supposed to do most of the work yourself to solve your own problems and structure things so that others can help.

Sorry, but I have to say it that way. No one can use the messy printout in your first post for anything!

[ddaily]

Thanks for the help. I will try and be more thorough in my descriptions.

I am using SQLyog on a WIN XP PC. Not my ideal situation, but I have to get a few things up and functional in a very short time.

The ESCAPED BY '\' did the trick then the 'rn' worked also.

This works:

load data infile 'hrpdf.csv' into table pdfhr

FIELDS ESCAPED BY '\' TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'rn'

(ssn,bigname,street,city,state,zipcode,homephone,fullperc,acrdate,hiredate,

probend,apptbud,loareason,loareturn,termcode,homedept,unitnumb,

empstat,p_t,p_f,emptype,cmpsmail,wphone,awphone,voicemail,faxnumb,

campadd,email,permaddr,permcity,permstate,permzip,emcont,emphone,

aemphone,ctznvisa,citcntry,resident,race,hispan,handic,sex,milstat,

incrmo,jobentry,jobclass,bargnunit,unioncode,edate,lastpaid,

uwid,salary,step,aptno) ;

This should end my data import difficulties. Now on to more exotic code.

Thanks once again, you really go out of your way to help.

[Author]

Posts