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.

2
  • CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
    – Wildcard
    Aug 6, 2018 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, 2018 at 15:09

3 Answers 3

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!

2
  • 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, 2018 at 1:44
  • Sure, now revised.
    – steve
    Aug 6, 2018 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.

6
  • Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
    – takanuva15
    Aug 6, 2018 at 2:10
  • @takanuva15 what kind of Unix/Linux are you using? Aug 6, 2018 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, 2018 at 2:18
  • @takanuva15 sorry I don't know anything about perl packaging in git bash Aug 6, 2018 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, 2018 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

You must log in to answer this question.

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