convert tab delimited data to , delimited records with double quoted fields “ ” [closed]
Clash Royale CLAN TAG#URR8PPP
1
I want to convert tab delimited csv into " ", add empty space if value is empty
FirstName LastName Address1 Address2 City State ZIP
John1 Mark 149 Lower Stereet California CA 05478
John2 Mark 149 Lower, Stereet California CA 05478
John3 Mark 149 ,Lower Stereet California CA 05478
with the desired result
"FirstName","LastName","Address1","Address2","City","State","ZIP"
"John1","Mark","149 Lower Stereet", ,"California","CA","05478"
"John2","Mark", ,"149 Lower, Stereet","California","CA","05478"
"John3","Mark","149,Lower Stereet", , "California","CA","05478"
I tried with below command
sed 's/t+/,/g;s/^|$/"/g;s/,/"&"/g' Actual.csv > Actual_V6.csv
Output
"FirstName","LastName","Address1","Address2","City","State","ZIP
"
"John1","Mark1","149 Lower Stereet","California","CA","05489
"
"John2","Mark","149 Lower"," Stereet","California","CA","05489","
"
"John3","Mark","149 ","Lower Stereet","California","CA","05489"
Where
- "149 ,Lower Stereet" convert into "149 Lower"," Stereet" it's
supposed to be "149 Lower, Stereet" - adding " in the next line
- Empty values are not recognized
EDIT
Output of hexdump:
text-processing sed csv
edited Dec 11 at 18:29
fra-san
1,180214
asked Dec 11 at 16:10
Sanj
63
closed as unclear what you're asking by RalfFriedl, G-Man, Stephen Harris, jimmij, Romeo Ninov Dec 12 at 7:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
|
show 8 more comments
1
I want to convert tab delimited csv into " ", add empty space if value is empty
FirstName LastName Address1 Address2 City State ZIP
John1 Mark 149 Lower Stereet California CA 05478
John2 Mark 149 Lower, Stereet California CA 05478
John3 Mark 149 ,Lower Stereet California CA 05478
with the desired result
"FirstName","LastName","Address1","Address2","City","State","ZIP"
"John1","Mark","149 Lower Stereet", ,"California","CA","05478"
"John2","Mark", ,"149 Lower, Stereet","California","CA","05478"
"John3","Mark","149,Lower Stereet", , "California","CA","05478"
I tried with below command
sed 's/t+/,/g;s/^|$/"/g;s/,/"&"/g' Actual.csv > Actual_V6.csv
Output
"FirstName","LastName","Address1","Address2","City","State","ZIP
"
"John1","Mark1","149 Lower Stereet","California","CA","05489
"
"John2","Mark","149 Lower"," Stereet","California","CA","05489","
"
"John3","Mark","149 ","Lower Stereet","California","CA","05489"
Where
- "149 ,Lower Stereet" convert into "149 Lower"," Stereet" it's
supposed to be "149 Lower, Stereet" - adding " in the next line
- Empty values are not recognized
EDIT
Output of hexdump:
text-processing sed csv
edited Dec 11 at 18:29
fra-san
1,180214
asked Dec 11 at 16:10
Sanj
63
closed as unclear what you're asking by RalfFriedl, G-Man, Stephen Harris, jimmij, Romeo Ninov Dec 12 at 7:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated?
– Christophe Strobbe
Dec 11 at 16:22
1
Possible duplicate of Convert separator character while escaping separator in fields
– RoVo
Dec 11 at 16:35
1
Misleading title. You want to convert to,delimited records with double quoted fields.
– RudiC
Dec 11 at 16:35
1
Not sure this is the cause for your"on the next line, but your file seems to use WindowsCR-LFend of line sequences (you can spot them as0d 0a). If it is what you want, you can get rid of them by adding as/r//g;(or's/o015//g') to yoursedcommand, as insed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'.
– fra-san
Dec 11 at 18:05
1
@fra-san .... Thank you very much It's working sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'
– Sanj
Dec 11 at 18:10
|
show 8 more comments
1
1
1
I want to convert tab delimited csv into " ", add empty space if value is empty
FirstName LastName Address1 Address2 City State ZIP
John1 Mark 149 Lower Stereet California CA 05478
John2 Mark 149 Lower, Stereet California CA 05478
John3 Mark 149 ,Lower Stereet California CA 05478
with the desired result
"FirstName","LastName","Address1","Address2","City","State","ZIP"
"John1","Mark","149 Lower Stereet", ,"California","CA","05478"
"John2","Mark", ,"149 Lower, Stereet","California","CA","05478"
"John3","Mark","149,Lower Stereet", , "California","CA","05478"
I tried with below command
sed 's/t+/,/g;s/^|$/"/g;s/,/"&"/g' Actual.csv > Actual_V6.csv
Output
"FirstName","LastName","Address1","Address2","City","State","ZIP
"
"John1","Mark1","149 Lower Stereet","California","CA","05489
"
"John2","Mark","149 Lower"," Stereet","California","CA","05489","
"
"John3","Mark","149 ","Lower Stereet","California","CA","05489"
Where
- "149 ,Lower Stereet" convert into "149 Lower"," Stereet" it's
supposed to be "149 Lower, Stereet" - adding " in the next line
- Empty values are not recognized
EDIT
Output of hexdump:
text-processing sed csv
edited Dec 11 at 18:29
fra-san
1,180214
asked Dec 11 at 16:10
Sanj
63
I want to convert tab delimited csv into " ", add empty space if value is empty
FirstName LastName Address1 Address2 City State ZIP
John1 Mark 149 Lower Stereet California CA 05478
John2 Mark 149 Lower, Stereet California CA 05478
John3 Mark 149 ,Lower Stereet California CA 05478
with the desired result
"FirstName","LastName","Address1","Address2","City","State","ZIP"
"John1","Mark","149 Lower Stereet", ,"California","CA","05478"
"John2","Mark", ,"149 Lower, Stereet","California","CA","05478"
"John3","Mark","149,Lower Stereet", , "California","CA","05478"
I tried with below command
sed 's/t+/,/g;s/^|$/"/g;s/,/"&"/g' Actual.csv > Actual_V6.csv
Output
"FirstName","LastName","Address1","Address2","City","State","ZIP
"
"John1","Mark1","149 Lower Stereet","California","CA","05489
"
"John2","Mark","149 Lower"," Stereet","California","CA","05489","
"
"John3","Mark","149 ","Lower Stereet","California","CA","05489"
Where
- "149 ,Lower Stereet" convert into "149 Lower"," Stereet" it's
supposed to be "149 Lower, Stereet" - adding " in the next line
- Empty values are not recognized
EDIT
Output of hexdump:
text-processing sed csv
text-processing sed csv
edited Dec 11 at 18:29
fra-san
1,180214
asked Dec 11 at 16:10
Sanj
63
edited Dec 11 at 18:29
fra-san
1,180214
asked Dec 11 at 16:10
Sanj
63
edited Dec 11 at 18:29
fra-san
1,180214
edited Dec 11 at 18:29
fra-san
1,180214
edited Dec 11 at 18:29
fra-san
1,180214
1,180214
asked Dec 11 at 16:10
Sanj
63
asked Dec 11 at 16:10
Sanj
63
asked Dec 11 at 16:10
Sanj
63
63
closed as unclear what you're asking by RalfFriedl, G-Man, Stephen Harris, jimmij, Romeo Ninov Dec 12 at 7:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as unclear what you're asking by RalfFriedl, G-Man, Stephen Harris, jimmij, Romeo Ninov Dec 12 at 7:32
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated?
– Christophe Strobbe
Dec 11 at 16:22
1
Possible duplicate of Convert separator character while escaping separator in fields
– RoVo
Dec 11 at 16:35
1
Misleading title. You want to convert to,delimited records with double quoted fields.
– RudiC
Dec 11 at 16:35
1
Not sure this is the cause for your"on the next line, but your file seems to use WindowsCR-LFend of line sequences (you can spot them as0d 0a). If it is what you want, you can get rid of them by adding as/r//g;(or's/o015//g') to yoursedcommand, as insed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'.
– fra-san
Dec 11 at 18:05
1
@fra-san .... Thank you very much It's working sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'
– Sanj
Dec 11 at 18:10
|
show 8 more comments
Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated?
– Christophe Strobbe
Dec 11 at 16:22
1
Possible duplicate of Convert separator character while escaping separator in fields
– RoVo
Dec 11 at 16:35
1
Misleading title. You want to convert to,delimited records with double quoted fields.
– RudiC
Dec 11 at 16:35
1
Not sure this is the cause for your"on the next line, but your file seems to use WindowsCR-LFend of line sequences (you can spot them as0d 0a). If it is what you want, you can get rid of them by adding as/r//g;(or's/o015//g') to yoursedcommand, as insed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'.
– fra-san
Dec 11 at 18:05
1
@fra-san .... Thank you very much It's working sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'
– Sanj
Dec 11 at 18:10
Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated?
– Christophe Strobbe
Dec 11 at 16:22
Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated?
– Christophe Strobbe
Dec 11 at 16:22
1
1
Possible duplicate of Convert separator character while escaping separator in fields
– RoVo
Dec 11 at 16:35
Possible duplicate of Convert separator character while escaping separator in fields
– RoVo
Dec 11 at 16:35
1
1
Misleading title. You want to convert to
, delimited records with double quoted fields.– RudiC
Dec 11 at 16:35
Misleading title. You want to convert to
, delimited records with double quoted fields.– RudiC
Dec 11 at 16:35
1
1
Not sure this is the cause for your
" on the next line, but your file seems to use Windows CR-LF end of line sequences (you can spot them as 0d 0a). If it is what you want, you can get rid of them by adding a s/r//g; (or 's/o015//g') to your sed command, as in sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'.– fra-san
Dec 11 at 18:05
Not sure this is the cause for your
" on the next line, but your file seems to use Windows CR-LF end of line sequences (you can spot them as 0d 0a). If it is what you want, you can get rid of them by adding a s/r//g; (or 's/o015//g') to your sed command, as in sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'.– fra-san
Dec 11 at 18:05
1
1
@fra-san .... Thank you very much It's working sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'
– Sanj
Dec 11 at 18:10
@fra-san .... Thank you very much It's working sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'
– Sanj
Dec 11 at 18:10
|
show 8 more comments
2 Answers
2
active
oldest
votes
1
You can use this:
sed 's/t/","/g; s/.*/"&"/; s/""/ /g' file
s/""/ /g is not really necessary to have a proper csv but to achieve your desired output.
If you have csvtool:
csvtool -t TAB -u ',' cat file
This will only quote the fields where necessary.
answered Dec 11 at 16:47
RoVo
2,558215
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
" added to next line
– Sanj
Dec 11 at 17:01
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
|
show 1 more comment
0
Not near a comp so can’t test but how about a simple
Sed -r ‘s/^/“/;s/$/“/;s/t/“,”/g;s/“”/ /g’
?
I think the question is wrong though. Quotation marks inside the original tsv will likely confuse any parser requiring them in the final result
answered Dec 11 at 16:52
Dani_l
3,125929
That works, even though the empty fields get converted into""instead of just a space between the commas. (See the desired result in the question.)
– Christophe Strobbe
Dec 11 at 17:10
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
|
show 1 more comment
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can use this:
sed 's/t/","/g; s/.*/"&"/; s/""/ /g' file
s/""/ /g is not really necessary to have a proper csv but to achieve your desired output.
If you have csvtool:
csvtool -t TAB -u ',' cat file
This will only quote the fields where necessary.
answered Dec 11 at 16:47
RoVo
2,558215
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
" added to next line
– Sanj
Dec 11 at 17:01
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
|
show 1 more comment
1
You can use this:
sed 's/t/","/g; s/.*/"&"/; s/""/ /g' file
s/""/ /g is not really necessary to have a proper csv but to achieve your desired output.
If you have csvtool:
csvtool -t TAB -u ',' cat file
This will only quote the fields where necessary.
answered Dec 11 at 16:47
RoVo
2,558215
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
" added to next line
– Sanj
Dec 11 at 17:01
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
|
show 1 more comment
1
1
1
You can use this:
sed 's/t/","/g; s/.*/"&"/; s/""/ /g' file
s/""/ /g is not really necessary to have a proper csv but to achieve your desired output.
If you have csvtool:
csvtool -t TAB -u ',' cat file
This will only quote the fields where necessary.
answered Dec 11 at 16:47
RoVo
2,558215
You can use this:
sed 's/t/","/g; s/.*/"&"/; s/""/ /g' file
s/""/ /g is not really necessary to have a proper csv but to achieve your desired output.
If you have csvtool:
csvtool -t TAB -u ',' cat file
This will only quote the fields where necessary.
answered Dec 11 at 16:47
RoVo
2,558215
answered Dec 11 at 16:47
RoVo
2,558215
answered Dec 11 at 16:47
RoVo
2,558215
answered Dec 11 at 16:47
RoVo
2,558215
2,558215
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
" added to next line
– Sanj
Dec 11 at 17:01
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
|
show 1 more comment
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
" added to next line
– Sanj
Dec 11 at 17:01
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
Hi Rovo,I tried with above it's working fine except adding " to the next line
– Sanj
Dec 11 at 16:56
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP " "John1","Mark1","149 Lower Stereet", ,"California","CA","05489 " "John2","Mark", ,"149 Lower, Stereet","California","CA","05489 " "John3","Mark","149 ,Lower Stereet", ,"California","CA","05489"
– Sanj
Dec 11 at 16:58
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
"FirstName","LastName","Address1","Address2","City","State","ZIP
– Sanj
Dec 11 at 17:00
" added to next line
– Sanj
Dec 11 at 17:01
" added to next line
– Sanj
Dec 11 at 17:01
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
"John1","Mark1","149 Lower Stereet","California","CA","05489
– Sanj
Dec 11 at 17:05
|
show 1 more comment
0
Not near a comp so can’t test but how about a simple
Sed -r ‘s/^/“/;s/$/“/;s/t/“,”/g;s/“”/ /g’
?
I think the question is wrong though. Quotation marks inside the original tsv will likely confuse any parser requiring them in the final result
answered Dec 11 at 16:52
Dani_l
3,125929
That works, even though the empty fields get converted into""instead of just a space between the commas. (See the desired result in the question.)
– Christophe Strobbe
Dec 11 at 17:10
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
|
show 1 more comment
0
Not near a comp so can’t test but how about a simple
Sed -r ‘s/^/“/;s/$/“/;s/t/“,”/g;s/“”/ /g’
?
I think the question is wrong though. Quotation marks inside the original tsv will likely confuse any parser requiring them in the final result
answered Dec 11 at 16:52
Dani_l
3,125929
That works, even though the empty fields get converted into""instead of just a space between the commas. (See the desired result in the question.)
– Christophe Strobbe
Dec 11 at 17:10
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
|
show 1 more comment
0
0
0
Not near a comp so can’t test but how about a simple
Sed -r ‘s/^/“/;s/$/“/;s/t/“,”/g;s/“”/ /g’
?
I think the question is wrong though. Quotation marks inside the original tsv will likely confuse any parser requiring them in the final result
answered Dec 11 at 16:52
Dani_l
3,125929
Not near a comp so can’t test but how about a simple
Sed -r ‘s/^/“/;s/$/“/;s/t/“,”/g;s/“”/ /g’
?
I think the question is wrong though. Quotation marks inside the original tsv will likely confuse any parser requiring them in the final result
answered Dec 11 at 16:52
Dani_l
3,125929
edited Dec 11 at 18:35
answered Dec 11 at 16:52
Dani_l
3,125929
answered Dec 11 at 16:52
Dani_l
3,125929
answered Dec 11 at 16:52
Dani_l
3,125929
3,125929
That works, even though the empty fields get converted into""instead of just a space between the commas. (See the desired result in the question.)
– Christophe Strobbe
Dec 11 at 17:10
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
|
show 1 more comment
That works, even though the empty fields get converted into""instead of just a space between the commas. (See the desired result in the question.)
– Christophe Strobbe
Dec 11 at 17:10
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
That works, even though the empty fields get converted into
"" instead of just a space between the commas. (See the desired result in the question.)– Christophe Strobbe
Dec 11 at 17:10
That works, even though the empty fields get converted into
"" instead of just a space between the commas. (See the desired result in the question.)– Christophe Strobbe
Dec 11 at 17:10
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
@ChristopheStrobbe added last conversion, hope that’s better, still can’t test
– Dani_l
Dec 11 at 17:43
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
The new version(s) convert empty fields into quoted single spaces. (In the first version, empty fields were converted into empty quotes, i.e. without a space between them.) B.t.w.: typo: simle -> simple.
– Christophe Strobbe
Dec 11 at 17:56
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
Why it's getting " in the next line for each row
– Sanj
Dec 11 at 18:03
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
@Sanj still can’t test but most probably because input has empty lines (or more specifically lines with ^[[:space:]]*n$ )
– Dani_l
Dec 11 at 18:34
|
show 1 more comment
Do you have two tabs (converted to spaces?) in the line with "John2"? And why is the field "City" repeated?
– Christophe Strobbe
Dec 11 at 16:22
1
Possible duplicate of Convert separator character while escaping separator in fields
– RoVo
Dec 11 at 16:35
1
Misleading title. You want to convert to
,delimited records with double quoted fields.– RudiC
Dec 11 at 16:35
1
Not sure this is the cause for your
"on the next line, but your file seems to use WindowsCR-LFend of line sequences (you can spot them as0d 0a). If it is what you want, you can get rid of them by adding as/r//g;(or's/o015//g') to yoursedcommand, as insed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'.– fra-san
Dec 11 at 18:05
1
@fra-san .... Thank you very much It's working sed 's/r//g; s/t/","/g; s/.*/"&"/; s/""/ /g'
– Sanj
Dec 11 at 18:10