Importing a csv into a table

[zzapper]

I've just been using Yog's Import a csv into a table feature. And had lots of trouble because I was using a Unix format csv on WinXP. This caused me to get horrible 'null' characters inserted into my data.

solutions:

1) import the csv into Excel and let it put the correct dos returns in.

2) copy into a DOS editor which saves with proper DOS CR.

What I would like to know for future reference is

a) What would be the syntax of a MySQL query which would filter out these wretched nuls??

😎 Was it possible to set the YOG import wizard to recognise the unix file format?

Have a good one!

[Ritesh]

Quote:

Was it possible to set the YOG import wizard to recognise the unix file format?

Yes.

Instead of specifying rn as line terminators, you should have specified n.

[zzapper]

Ritesh,

I tried alternatively r and n w/o success.

The query I tried to use to clean up the data was

update tbl_county_lookup set countyname=replace(countyname,'&','and');

That works for a character such as '&' but I couldn't get it to work for null where I tried N and NULL.

Come on folks this must be a fairly common problem!!!

[Ritesh]

Can you mail me the CSV file and the table structure if the data is not confidential?

[zzapper]

Ritesh,

Sorry was me having a bad-hair day.

The csv line terminator I required was rn

Non-ascii characters inserted into mysql all seem to display as a hollow box

The mysql I needed to filter it out (having got the line terminator wrong) was

update test set txt = replace(txt,'r','');

[Author]

Posts