1

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!

InspiredCat
  • 27
  • 2
  • 7
  • 1
    Use `sed -i 's/,$/,NULL/' file_name.csv`. You may need to use `dos2unix` to convert lnebreaks to `\n` only. – Wiktor Stribiżew Oct 24 '18 at 16:11
  • 1
    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 Oct 24 '18 at 16:26
  • @kenlukas This emptied the file – InspiredCat Oct 24 '18 at 16:43
  • @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 Oct 24 '18 at 17:06
  • Possible duplicate of [Replace empty CSV value with NULL using sed](https://stackoverflow.com/questions/38205541/replace-empty-csv-value-with-null-using-sed) – kenlukas Oct 24 '18 at 17:10
  • 1
    @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 Oct 24 '18 at 23:49

2 Answers2

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.
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
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.

Walter A
  • 19,067
  • 2
  • 23
  • 43