0

I have a 200,000+ line file that I need to update.

My issue is that I need to use commas to separate the fields; however in the original file there are commas placed in the "comment section." The comment section is delimited by "". Not all lines have this comment field populated and it may not contain any commas.

So when using awk with -F, my columns are skewed due to the commas in the comment section.

Here is an example of a line in my file:

XX,YYY,ZZZ,XXXX,Y,,,QQQQ,AAAA,BBBBB,,,XXX,YYY,,,,,ZZZ,QQ,AAAAAA,YY,ZZZZZZZZ,"Zona Industriale, Via Golgi snc",QQ.ZZZZZZZZ,QQ.ZZZZZZ,31.5,2,0,MACRO,#N/A

In the above I need to replace: "Zona **Industriale, Via Golgi snc" with "Zona **Industriale Via Golgi snc"

So long story short the , needs to be removed between the ""s in the line above and in about 70% of the existing file.

  • 1
    Welome to Vi and Vim! I've edited your question for formatting and clarity. Please check that it still retains the meaning you wanted. – D. Ben Knoble Nov 23 '20 at 18:09
  • If using awk on a CSV file is your goal, then see this answer about using GNU awk and FPAT to recognize quoted fields even if they include commas. – filbranden Nov 24 '20 at 1:19
1

I copied your example line in a test and found a search and replace command that will find your commas within quotes and delete them.

%s/".*\zs,\ze.*"/

The breakdown:

  • %s/ for performing the search
  • " for the beginning of your comment
  • .* for any number of any characters
  • \zs for the command to start the match
  • , for the comma in question
  • \ze for the command to end the match
  • .* for any number of any characters
  • " for the ending of your comment
  • / and nothing after the slash because you just want it deleted.

It worked for me, let me know if it doesn't work for you.

Update:

After doing some more searching, it looks like this is the correct answer for multiple replacements:

%s/"\([^"]|""\)*"/\=substitute(submatch(0), ",", " ", "g")/g

Which searches for everything between quotes, and then calls the substitute command to submatch 0 which is everything contained in the previous search command, the next argument is the string to search for, the third argument is what to replace it with, and I believe the last argument is to make it global for the file.

Taken from this previous vi/vim question.

  • 1
    This only handles a single comma in the quotes; you would need multiple applications to get the rest. – D. Ben Knoble Nov 23 '20 at 18:18
  • I tested it by putting multiple commas inside the quote and the command found them all and deleted them. I wondered if it would. – Vee Nov 23 '20 at 18:22
  • Never mind, you're correct, I didn't see the remaining ones clearly. – Vee Nov 23 '20 at 18:25
  • Thanks for the quick response... tried it and all looks good, there is some additional massaging that needs to be done but outside the scope of this question. – Idiot_wind Nov 23 '20 at 18:32
  • Welcome! I tried to make sure it worked for multiple entries, because that seemed reasonable and thought it did, but it didn't. You can use @: and specify a count to use the previous search command multiple times, which might help speed things up. – Vee Nov 23 '20 at 18:44
0

If all the lines have at most one "" field, you could try

global/"/normal! f"f,x

and repeat (@: the first repeat, then @@ for the rest, with a count if you like) until the commas are gone.

But I would really suggest using an actual CSV parser; e.g., python's pandas library could do something like

data = pandas.read_csv(..., names=[...])
data['comments'].apply(lambda f: f.replace(',', ''))
data.to_csv(...)

Thought at that point, you might as well admit you cannot parse a CSV with awk -F,

  • Thank you for your quick response, I failed to share that some of the lines have multiple fields that have the " ". I will be trying your suggestion once I have time to play a bit. Thank you again for your suggestion! – Idiot_wind Nov 23 '20 at 18:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.