mysqllinuxunixsedfile-encodings

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

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!

Solution

  • 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: