I'm using mysql client to export the records of a table into a CSV file. Everything works fine, except when the records have null fields.
mysql --execute="SELECT * FROM users"| sed 's/\t/","/g;s/^/"/;s/$/"/;' > users.csv
"John","Smith","john@smith.com","NULL","NULL","347-1234566"
"John","Smith","john@smith.com",NULL,NULL,"347-1234566"
Try:
mysql --execute="SELECT * FROM users"| sed 's/\t/","/g; s/^/"/; s/$/"/; s/"NULL"/NULL/g' > users.csv
The only change to your command is the addition of s/"NULL"/NULL/g
.
If sed
supported look-ahead or look-behind regex features, there might be more complex ways of doing this, but it doesn't have such features.
With this tab-separated file as sample input:
$ cat file
John Smith john@smith.com NULL NULL 347-1234566
You can achieve the output that you want with:
$ sed 's/\t/","/g; s/^/"/; s/$/"/; s/"NULL"/NULL/g' file
"John","Smith","john@smith.com",NULL,NULL,"347-1234566 "