Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I got this csv exported from a mysql table in this way

select *  INTO OUTFILE '/tmp/myfile.csv'   FIELDS TERMINATED BY ',' FROM bouncemap

I need to use sed to replace the ip addresses with start * value in this csv and replace some other bounce code always with star * char.
The problem is that after using sed on this csv file, I try to import it into mysql's table in this way

LOAD DATA LOCAL INFILE '/tmp/myfile.csv' INTO TABLE table FIELDS TERMINATED BY ',' 

and the import process skipped more than 70% of the record.
I've noticed that if I export the table as csv and then re-import in the mysql table, without using sed everything is fine and all the record are imported.
I have also noticed that if I replace the ip address with hash # char instead than star the number of not imported record rise like 80%.
I am using mysql 5.1 on debian 6.0.5

Any idea or suggestion? Cheers

share|improve this question
1  
Why are you bothering with this? You could just do your changes via SQL queries. –  Michael Hampton Mar 27 '13 at 11:48
    
I've tried but I prefer use tool like sed,awk in these case. Also because I am not still so good at mysql queries :D –  marcodv Mar 27 '13 at 17:00

1 Answer 1

Ok guys the problem was that some record was too long and mysql added some extra \n char to the record during the export process.
So it means that I got some row with one record empty.
So my colleague suggested to use this little trick

select bouncetype, replace (bouncereason,'\n','')  INTO OUTFILE '/tmp/.csv' FIELDS TERMINATED BY ',' from bouncemap;

replace in this case change \n newline with nothing also if the line is too long.

So I managed to use sed for manipulate the file extracted, then imported it and mysql didn't complaint :D

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.