4

I have an excel file that I converted to csv. When converted, it looks like the following example (Please note that there are 100+ columns in the csv. This is a minified version):

,Product,"  ",Citty,"   ",Price
,Name," ",Location,"    ",Per Unit
,banana,"   ",CA,"  ",5.7
,apple,"    ",FL,"  ",2.3

I need to write a script that will take the first & second line and "merge" them together based on their comma position:

,Product Name," ""  ",Citty Location,"  ""  ",Price Per Unit
,banana,"   ",CA,"  ",5.7
,apple,"    ",FL,"  ",2.3

I've looked at other questions on here and stack overflow, but the answers don't seem to pertain to this weird column-by-column situation for just the first 2 lines of the file.


As an additional unrelated task, I'd also like to get rid of the empty columns in the csv and fix the spelling error so that it looks like this:

Product Name,City Location,Price Per Unit
banana,CA,5.7
apple,FL,2.3

(The csv currently has a tab surrounded by quotes between every actual column of data except for the first column, which is just empty followed by a comma).

I will be receiving the csv with the spelling error multiple times, so I would like to programmatically fix the error in the script. Please also note that the columns may not always be in the order shown above, so I need to dynamically check each column name for the error during the script.

  • CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying. – Wildcard Aug 6 '18 at 8:33
  • @Wildcard Haha quite true, but another business group gives me the file so "spelling errors" become a low-priority fix on their end – takanuva15 Aug 9 '18 at 15:09
5

Try this

$ awk -F, 'NR<2{split(gensub(/Citty/,"City","g",$0),a,FS)}NR==2{for(b=2;b<=NF;b+=2){c=c a[b]" "$b","}print gensub(/,$/,"",1,c)}NR>2{print gensub(/(^,|" *",)/,"","g",$0)}' inp
Product Name,City Location,Price Per Unit
banana,CA,5.7
apple,FL,2.3
$

Same code is more readable if split across a few lines :

$ awk -F, '
> NR<2{split(gensub(/Citty/,"City","g",$0),a,FS)}
> NR==2{for(b=2;b<=NF;b+=2){c=c a[b]" "$b","}print gensub(/,$/,"",1,c)}
> NR>2{print gensub(/(^,|" *",)/,"","g",$0)}' inp
Product Name,City Location,Price Per Unit
banana,CA,5.7
apple,FL,2.3
$

If 1st line, split the line into array elements within a. Fix the Citty->City typo.

If 2nd line, starting with the 2nd column, print the corresponding column from 1st line together with this column. Repeat for each column, going in 2 column increments. Strip the trailing ,.

After 2nd line, replace any leading , or any "<spaces>", with an empty string and then print the result.

Tested ok on GNU Awk 4.0.2

Try it online!

  • 1
    Great, thanks for that! However, I have 100+ columns in the csv. Is there a way to generalize that 3rd line for NR>2 so that I don't need to specify 50 $? Also, the "Citty" typo could be on any odd-numbered column of the csv depending on how the file is formatted when I produce it. How would I generalize the typo replacement to check for a column match of "Citty" and then fix it for whatever column the typo was in? (Sorry I didn't mention it earlier; I've updated the question with this info) – takanuva15 Aug 6 '18 at 1:44
  • Sure, now revised. – steve Aug 6 '18 at 19:09
4

Using Perl, with Text::CSV and MoreUtils:

perl -MText::CSV -MList::MoreUtils=pairwise -lne '
  BEGIN { $p = Text::CSV->new(); } 
  @f = $p->fields() if $p->parse($_);
  @hdr = map { s/Citty/City/ ; $_ } @f if $. == 1;
  @f = pairwise { $a . " " . $b } @hdr, @f if $. == 2;
  print join ",", grep { /\w/ } @f if $. > 1;
' file.csv
Product Name,City Location,Price Per Unit
banana,CA,5.7
apple,FL,2.3

The grep omits fields that do not contain at least one word character.

With perl >= 5.14.0 you can simplify the replacement to map s/Citty/City/r @f using the Non-destructive substitution modifier.

  • Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries? – takanuva15 Aug 6 '18 at 2:10
  • @takanuva15 what kind of Unix/Linux are you using? – steeldriver Aug 6 '18 at 2:14
  • Currently I'm running Windows and using a (Git) bash prompt to run/test the script on my local machine. I think the script will eventually go on our server, but I don't know the specific OS...I'll get back to you tomorrow on that. – takanuva15 Aug 6 '18 at 2:18
  • @takanuva15 sorry I don't know anything about perl packaging in git bash – steeldriver Aug 6 '18 at 2:47
  • @takanuva15 If you're stuck testing on Windows, I would suggest installing WSL for a more complete environment that should be more or less portable to a real Linux distro. Git Bash has ... odd quirks. – Bob Aug 6 '18 at 5:14
3

Try

    awk -F, '
            {gsub (/,*"[    ]*",*/, ",")
             sub (/^,/, "")
             sub (/Citty/, "City")
            }

    NR == 1 {n = split ($0, T)
             next
            }
    NR == 2 {for (;n; n--) $n = T[n] " " $n
            }

    1
    ' OFS=, file
Product Name,City Location,Price Per Unit
banana,CA,5.7
apple,FL,2.3

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.