[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.

[ddaily]

Ok, set session sql_mode = ' ';

worked.

Thanks.

[ddaily]

Here's the code for double quotes on the second field only:

example: 1,”hispan”,100

/*[1:29:49 PM][ 0 ms]*/ load data local infile 'C:/foof/racetestdq.csv' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by '”' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

[ddaily]

🙁 Sorry about the quote, I must have punch the reply too soon.

My real message was both methods worked, ' and ” double quotes.

Thanks, now I can get on with really using this cool software.

🙂

[ddaily]

peterlaursen wrote on Oct 18 2005, 08:33 PM:

You could have written that in the first place!

Now I have been spending no more than 20 hours with aspects of this CSV-tool.

You know how your code reads the user input and what it does with it. 

Nobody else has a chance.

[post=”7643″]<{POST_SNAPBACK}>[/post]

[ddaily]

Data import loaded fine using MySQL-Front, another GUI interface software,

but it's not as good as SQLyog (plus my German isn't good enough to understand the error messages! 😮 ).

What I truly don't understand is why such a simple operation is not working?

I've tried several other settings to no avail.

[ddaily]

CSV file mailed.

Here's the history file:

History file attempting to load racetable from a file

fails also if the file is on my local C: drive

/*[9:35:20 AM][ 0 ms]*/ show variables like '%character%'

/*[9:35:20 AM][ 0 ms]*/ Set character_set_connection=latin1

/*[9:35:20 AM][ 0 ms]*/ Set character_set_results=latin1

/*[9:35:20 AM][ 0 ms]*/ Set character_set_client=latin1

/*[9:35:20 AM][ 0 ms]*/ use `fsbo`

/*[9:35:25 AM][ 16 ms]*/ show table status from `fsbo`

/*[9:35:26 AM][ 0 ms]*/ show tables from `fsbo`

/*[9:35:30 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show keys from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show create table `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show keys from `fsbo`.`racetable`

/*[9:35:30 AM][ 0 ms]*/ show create table `fsbo`.`racetable`

/*[9:36:25 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:36:25 AM][ 0 ms]*/ show keys from `fsbo`.`racetable`

/*[9:36:25 AM][ 0 ms]*/ select * from `fsbo`.`racetable` limit 0, 50

/*[9:36:33 AM][ 0 ms]*/ show tables from `fsbo`

/*[9:36:33 AM][ 0 ms]*/ show full fields from `fsbo`.`racetable`

/*[9:36:34 AM][ 0 ms]*/ load data local infile 'U:/racetabl.asc' into table `fsbo`.`racetable` fields escaped by '\' terminated by ',' optionally enclosed by ''' lines terminated by 'rn' ( `racecode`, `racedsc`, `racetot` )

[ddaily]

I couldn't get the file to attach. Not a good day all in all.

Data: '1','hispanic','100'

'2','caucasian','200'

mysql table: racecode [int(11), NOT NULL]

racedsc [tinytext, NULL]

racetot [int(11), NULL]

The import CSV box has:

terminated by ,

optionally enclosed by: '

Escaped by: \

lines terminated by: rn

I've tried different settings, all result in “Error while importing data”

Does the location of the file affect importing?

I did get it imported using another package (but i'm not paying

$65 for an import only program!).

This should be a simple thing and I'm hoping I'm just not seeing the obvious.

Thanks.

[Author]

Posts