2

I have a flat file (txt/csv) with a single header and many records. I want to use awk/sed/unix tools to strip leading zeros from fields 3 and 5 only on the records (could be one or many leading zeroes). Looked at several solutions, but most don't seem to account for the values being double quoted. Example:

"ACCOUNT","REAL","022000046977525","REAL","00000220000488","ONLINE",......

Tried some awk and sed, with printf, regex, etc. Am I missing something already published? ideas? thanks.

Desired output:

"ACCOUNT","REAL","22000046977525","REAL","220000488","ONLINE",......
3
  • @Jesse_b - not on the header. added for clarity on that one, could use something like NR >1 if in awk, for example. thanks
    – sirabhorn
    Dec 13, 2019 at 18:33
  • Can any of your quoted fields contain commas or escaped quotes or newlines? If the answer is yes then it becomes a harder problem and if the answer is no then - why are they quoted?
    – Ed Morton
    Dec 13, 2019 at 20:13
  • Ed Morton - they could, but Jesse_b's awk solution targets action only to be taken on fields 3 and 5, which will always be wholly numeric. thanks
    – sirabhorn
    Dec 13, 2019 at 21:42

3 Answers 3

2

Using awk:

awk -F, '{OFS=","; sub(/"0+/, "\"", $3); sub(/"0+/, "\"", $5)}1'

Shouldn't be an issue unless your header actually has leading zeros but if needed you could do:

awk -F, 'NR > 1{OFS=","; sub(/^"0+/, "\"", $3); sub(/^"0+/, "\"", $5)}1'

This will replace the " and all leading zeros in fields 3 and 5 with just a quote.

1
  • Great solution and approach on the two fields in question. Thanks for the quick reply!
    – sirabhorn
    Dec 13, 2019 at 18:58
1

Using Miller, given a CSV file with headers

$ cat file.csv
"000001","000002","000003","000004","000005","000006","000007"
"ACCOUNT","REAL","022000046977525","REAL","00000220000488","ONLINE",......

(silly field names chosen to demonstrate that leading zeros in the header are not affected) then

$ mlr --csv --ofmt '%.0f' --quote-all put '$000003=$000003; $000005=$000005' file.csv
"000001","000002","000003","000004","000005","000006","000007"
"ACCOUNT","REAL","22000046977525","REAL","220000488","ONLINE","......"

Alternatively, with csvformat (from python csvkit) and numfmt (from GNU Coreutils):

$ csvformat file.csv | numfmt -d, --header --field 3,5 --format '%.0f' | csvformat -U2
"000001","000002","000003","000004","000005","000006","000007"
"ACCOUNT","REAL","22000046977525","REAL","220000488","ONLINE","......"
0

Using GNU sed:

$ sed -re '
   s/","/\n/4;s//\n/2
   s/\n0*([0-9])/","\1/g
' file.csv

Assuming that all the fields are quoted.

Mark the third and fifth fields by a newline and remove any leading zeros. In case all are zeros, it will preserve last rather than make the field vanish.

You must log in to answer this question.

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