0

Suppose I have a CSV file like this:

1,"abc",,,,
2,"def",,,"ghi",
3,,"jkl",,,"mno"

Now I need to transform it in 3 steps:

  1. Replace all empty spaces with the String NULL
  2. Add at the beginning of each line the String VALUES (
  3. Add at the end of each line the string );

So that I have:

VALUES (1,"abc",NULL,NULL,NULL,NULL);
VALUES (2,"def",NULL,NULL,"ghi",NULL);
VALUES (3,NULL,"jkl",NULL,NULL,"mno");

I would like to use Notepad++ or something similar.

This is needed in order to transform the csv into a valid sql file.

1
  • You can do it by using "Replace" of Notepad++ with RegEx (regular expression) Sep 17, 2015 at 13:53

1 Answer 1

2

I believe I got it down to two find/replace expressions.

Press Ctrl+H to bring up the Replace dialog.

1,"abc",,,,
2,"def",,,"ghi",
3,,"jkl",,,"mno"

Find what: ^([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),([^,]*)$
Replace with: VALUES\(NULL\1,NULL\2,NULL\3,NULL\4,NULL\5,NULL\6\);

VALUES(NULL1,NULL"abc",NULL,NULL,NULL,NULL);
VALUES(NULL2,NULL"def",NULL,NULL,NULL"ghi",NULL);
VALUES(NULL3,NULL,NULL"jkl",NULL,NULL,NULL"mno");

Find what: NULL([^,\)])
Replace with: \1

VALUES(1,"abc",NULL,NULL,NULL,NULL);
VALUES(2,"def",NULL,NULL,"ghi",NULL);
VALUES(3,NULL,"jkl",NULL,NULL,"mno");
3
  • Looks good, +1 ! Sep 17, 2015 at 14:21
  • 2
    Looks OK, since it offers no explanation of what is going on, which may actually help the OP learn something. +0 Sep 17, 2015 at 15:39
  • 1
    Basically adds Null after every comma. Then removes null wherever there is some value after it. May 2, 2016 at 6:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .