Hi Linux Experts,

I have the following problem to solve:

-Below is the CSV file give

firstname,lastname,password,username,notes,city,phonenumber
fred,smith, notgood1, fredsmith, this user\, is the first in this file, Brighton,345698
Peter, Bloggs, anotherbad,peterbloggs,,London,987123
Jo, cooper, notmuch, jcooper, this user is Jo, Brighton, 456987
john, carter,nearlyempty,jcarter,This note is actually very long\, but really doesn't say anything very useful,,345777
sam,jones,passing, samjones, Not much of a note really, Manchester, 135790

- capitalise the first letter of the two name fields
- sanitise the formatting
- move the username column to the beginning of each line
- the phone number is missing the area code - look up the city in the following table, and add it to the beginning of the phone number column:

City, Area Code
London, 5
Brighton, 6
Manchester, 7

Provide the corrected CSV file.

One of the problems I have is that whenever I use the comma as FS the output for column 5 is the following

cat Test.csv | awk 'BEGIN { FS = "," } {print $5 }'
notes
this user\

this user is Jo
This note is actually very long\
Not much of a note really

It stops in the middle of the entry because it sees the comma but what I am trying to achieve is to produce the full entry for column 5 like this:

this user\, is the first in this file
This note is actually very long\, but really doesn't say anything very useful

I have to probably escape somehow the FS in the text but so far no joy with completing this task. Also can you kindly help out for the rest of the requirements.
I really appreciate your help in advance.

Ivan

To quote Wikipedia on the topic, "'CSV' is not a single, well-defined format." RFC 4180 is the most commonly used standard, and there the only defined quoting method is use of the double quote character (") to enclose entire fields, and that needs to be used for fields that contain line breaks, commas, and double quote characters. A literal double quote character within a field is escaped by preceding it with another double quote character.

Parsing a CSV file in awk is not a trivial undertaking. I've attached an awk script that does parse CSV files and extract fields from them. It does not handle the embedded newline case, but perhaps it will serve as a useful example.

Attached Files

csvextract.txt (2.7 KB, 17 views)

The OP resurrected an old thread that was (very) similar. There were suggestions there for how this could be handled.

awk does have another variable called FPAT which is often used for csv files but it is generally suited to solving the issue of commas in quotes to not be considered.

This does appear to be an unusual format to have an escape included in input data.

phew ... it wasn't immediately obvious (well not to me):

you can replace \, for example you can use QQQ (or whatever you want), do your job and replace back at the end. You can try sed to do that easily. Other way could be to use perl or other language with better parsing possibilities.