Unix. sed command. Replace a expression containing \n. Database migration from Sybase to MySQL. Windows-Unix plain text encoding

Question

I am migrating a database from Sybase to MySQL. For that, I am exporting the tables in .csv files and then loading them in the new tables in MySQL. When doing this, NULL values are converted to 0 in MySQL, so I need to edit the .csv files to find where there are no values and replace them with a NULL.

Edited: I am exporting the .csv files from Sybase in Windows and working with them in Unix virtual machine.

Null values may appear in middle columns: 3,,,4,5 --here it should look like 3,NULL, NULL,4,5 For doing this, I used successfully:

sed -i -e 's/,,/,NULL,/g' file_name.csv

(and run it two times).

The problem is when the NULL values are in the last column: 3,4,5, -- This should look like 3,4,5,NULL

In the text editor, I used find and replace successfully: Find: ,\r\n Replace: ,NULL\r\n

I want to automatize this from the Unix terminal, but it is not working. I tried:

sed -i -e 's/,\r\n/,NULL\r\n/' file_name.csv

sed -i -e 's/,\\r\\n/,NULL\\r\\n/' file_name.csv

Thank you!

Use sed -i 's/,$/,NULL/' file_name.csv. You may need to use dos2unix to convert lnebreaks to \n only. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Oct 24, 2018 at 16:11
This will work if the line starts, ends or contains a null: sed -i -e 's/^,/NULL,/' -e 's/,,/,NULL,/g' -e 's/,$/,NULL/' file_name.csv — kenlukas
– kenlukas, Commented Oct 24, 2018 at 16:26
@bertagp, what OS are you doing this on. It worked as expected for me on CentOS7 and Ubuntu 16.04. It didn't work on MacOS — kenlukas
– kenlukas, Commented Oct 24, 2018 at 17:06
@WiktorStribiżew, You were right with converting line breaks, as I was exporting the .csv files in Windows and then using them in the Unix virtual machine. What I did exactly was: dos2unix -n file_in.csv file_out.csv sed -i 's/,$/,NULL/g' file_out.csv .Thank you very much everyone. — InspiredCat
– InspiredCat, Commented Oct 24, 2018 at 23:49

Wiktor Stribiżew · Accepted Answer · 2018-10-25 06:48:52Z

1

Since you have Windows CRLF endings, you need to run dos2unix on the input files.

Then, all you need is to match , at the end of the lines only and replace them with a ,NULL.

Here is the example:

dos2unix -n file_in.csv file_out.csv
sed -i 's/,$/,NULL/' file_out.csv

Note:

-i will change the file_out.csv
,$ matches a , at the end of a line ($)
Since sed operates on lines, you do not need g modifier, as there is only 1 end of a line on each line.

answered Oct 25, 2018 at 6:48

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Walter A · Accepted Answer · 2018-10-24 21:03:02Z

0

Use twice

sed -ir 's/(^|,)(,|$)/\1null\2/g' file_name.csv

or one time

sed -ir 's/(^|,)(,|$)/\1null\2/g;s/(^|,)(,|$)/\1null\2/g' file_name.csv

or one time

sed -ir ':a;s/(^|,)(,|$)/\1null\2/g;ta' file_name.csv

This will change an empty line into null.

answered Oct 24, 2018 at 21:03

Walter A

20.2k2 gold badges29 silver badges46 bronze badges

Collectives™ on Stack Overflow

Unix. sed command. Replace a expression containing \n. Database migration from Sybase to MySQL. Windows-Unix plain text encoding

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related