Help with command line text manipulation

[6thElement]

I need a hand getting my head around something I'm trying to do with bulk loading CSV data into Oracle using sqlldr.

Take this as an example of two lines I've cut from the csv file which contain examples of the issues I'm having:

"2005004940","VVVGGT80~K112345","FL~FL","MAR MERC"
"2005006028","G11AAB","FL","WHI VAN"

The problems come from some of the fields are delimited again using a tilde and I need to record those further delimited values against the first column again. If even possible, adding on a further value of the row number for that first column, although I can work around this part in SQL. There may be several tilde delimited values in a column.

A perfect output is below:

"2005004940","VVGGT801","FL","MAR MERC","1"
"2005004940","K112345","FL","","2"
"2005006028","G11AAB","FL","WHI VAN","1"

 

[kiwi]

sed and awk will almost certainly do the job, working out how is a different matter though

 

[whitecrook]

search and replace '~' with '","' ? That should be fairly straightforward, somthineg along the lines of sed s/~/","/g <- I just made that up but should be similar