Mixing
How to merge first two lines of a csv column-by-column?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
4
down vote
favorite
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.
text-processing csv
asked yesterday
takanuva15
1214
add a comment |Â
up vote
4
down vote
favorite
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.
text-processing csv
asked yesterday
takanuva15
1214
CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
– Wildcard
yesterday
add a comment |Â
up vote
4
down vote
favorite
up vote
4
down vote
favorite
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.
text-processing csv
asked yesterday
takanuva15
1214
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.
text-processing csv
asked yesterday
takanuva15
1214
edited yesterday
asked yesterday
takanuva15
1214
asked yesterday
takanuva15
1214
asked yesterday
takanuva15
1214
1214
CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
– Wildcard
yesterday
add a comment |Â
CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
– Wildcard
yesterday
CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
– Wildcard
yesterday
CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
– Wildcard
yesterday
add a comment |Â
3 Answers
3
active
oldest
votes
up vote
5
down vote
Try this
$ awk -F, 'NR<2split(gensub(/Citty/,"City","g",$0),a,FS)NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)NR>2" *",)/,"","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<2split(gensub(/Citty/,"City","g",$0),a,FS)
> NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)
> NR>2" *",)/,"","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!
answered yesterday
steve
11.9k22047
1
Great, thanks for that! However, I have 100+ columns in the csv. Is there a way to generalize that 3rd line forNR>2so 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
yesterday
Sure, now revised.
– steve
15 hours ago
add a comment |Â
up vote
4
down vote
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.
answered yesterday
steeldriver
30.8k34877
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
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
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@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
yesterday
add a comment |Â
up vote
3
down vote
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
answered yesterday
RudiC
762
add a comment |Â
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
5
down vote
Try this
$ awk -F, 'NR<2split(gensub(/Citty/,"City","g",$0),a,FS)NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)NR>2" *",)/,"","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<2split(gensub(/Citty/,"City","g",$0),a,FS)
> NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)
> NR>2" *",)/,"","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!
answered yesterday
steve
11.9k22047
1
Great, thanks for that! However, I have 100+ columns in the csv. Is there a way to generalize that 3rd line forNR>2so 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
yesterday
Sure, now revised.
– steve
15 hours ago
add a comment |Â
up vote
5
down vote
Try this
$ awk -F, 'NR<2split(gensub(/Citty/,"City","g",$0),a,FS)NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)NR>2" *",)/,"","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<2split(gensub(/Citty/,"City","g",$0),a,FS)
> NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)
> NR>2" *",)/,"","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!
answered yesterday
steve
11.9k22047
1
Great, thanks for that! However, I have 100+ columns in the csv. Is there a way to generalize that 3rd line forNR>2so 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
yesterday
Sure, now revised.
– steve
15 hours ago
add a comment |Â
up vote
5
down vote
up vote
5
down vote
Try this
$ awk -F, 'NR<2split(gensub(/Citty/,"City","g",$0),a,FS)NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)NR>2" *",)/,"","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<2split(gensub(/Citty/,"City","g",$0),a,FS)
> NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)
> NR>2" *",)/,"","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!
answered yesterday
steve
11.9k22047
Try this
$ awk -F, 'NR<2split(gensub(/Citty/,"City","g",$0),a,FS)NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)NR>2" *",)/,"","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<2split(gensub(/Citty/,"City","g",$0),a,FS)
> NR==2for(b=2;b<=NF;b+=2)c=c a[b]" "$b","print gensub(/,$/,"",1,c)
> NR>2" *",)/,"","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!
answered yesterday
steve
11.9k22047
edited 15 hours ago
answered yesterday
steve
11.9k22047
answered yesterday
steve
11.9k22047
answered yesterday
steve
11.9k22047
11.9k22047
1
Great, thanks for that! However, I have 100+ columns in the csv. Is there a way to generalize that 3rd line forNR>2so 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
yesterday
Sure, now revised.
– steve
15 hours ago
add a comment |Â
1
Great, thanks for that! However, I have 100+ columns in the csv. Is there a way to generalize that 3rd line forNR>2so 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
yesterday
Sure, now revised.
– steve
15 hours ago
1
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
yesterday
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
yesterday
Sure, now revised.
– steve
15 hours ago
Sure, now revised.
– steve
15 hours ago
add a comment |Â
up vote
4
down vote
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.
answered yesterday
steeldriver
30.8k34877
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
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
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@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
yesterday
add a comment |Â
up vote
4
down vote
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.
answered yesterday
steeldriver
30.8k34877
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
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
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@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
yesterday
add a comment |Â
up vote
4
down vote
up vote
4
down vote
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.
answered yesterday
steeldriver
30.8k34877
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.
answered yesterday
steeldriver
30.8k34877
edited yesterday
answered yesterday
steeldriver
30.8k34877
answered yesterday
steeldriver
30.8k34877
answered yesterday
steeldriver
30.8k34877
30.8k34877
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
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
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@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
yesterday
add a comment |Â
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
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
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@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
yesterday
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
Sorry, I'm a bit nooby with bash scripting. How would I install those two modules/packages/libraries?
– takanuva15
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
@takanuva15 what kind of Unix/Linux are you using?
– steeldriver
yesterday
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
yesterday
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
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@takanuva15 sorry I don't know anything about perl packaging in git bash
– steeldriver
yesterday
@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
yesterday
@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
yesterday
add a comment |Â
up vote
3
down vote
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
answered yesterday
RudiC
762
add a comment |Â
up vote
3
down vote
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
answered yesterday
RudiC
762
add a comment |Â
up vote
3
down vote
up vote
3
down vote
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
answered yesterday
RudiC
762
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
answered yesterday
RudiC
762
answered yesterday
RudiC
762
answered yesterday
RudiC
762
answered yesterday
RudiC
762
762
add a comment |Â
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f460718%2fhow-to-merge-first-two-lines-of-a-csv-column-by-column%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
CSV repair is fine, but you might consider getting the XLS source fixed also/instead. Just saying.
– Wildcard
yesterday