Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

carriage returns within quotation marks causing new lines in csv

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting carriage returns within quotation marks causing new lines in csv
# 1  
Old 09-02-2012
carriage returns within quotation marks causing new lines in csv
I have a csv file with 3 columns. Fields are comma delimited and strings are enclosed with quotation marks "". About 40% of the time, the line of values will start a new line thanks to carriage return characters within a string.

Example:
Code:
"apple","banana","orange"
"pineapple","grape","straw
berry"
"apple","banana","cherry"

Should be:
Code:
"apple","banana","orange"
"pineapple","grape","strawberry"
"apple","banana","cherry"

How to create a bash script that will catch this issue and combine the line and the line below?
Last edited by Franklin52; 09-03-2012 at 03:34 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 09-03-2012
Try:
Code:
awk '/^["]/{if(out != "") print out;out = $0;next}
        {out = out $0}
END     {if(out != "") print out}' input

This User Gave Thanks to Don Cragun For This Post:
koeji
# 3  
Old 09-03-2012
While that worked for the sample provided, the actual sample includes strings:
Code:
"Smith", "Margie", "200 Elm St."
"Doe", "John", "N", "123 Maple St.
Apt. C."
"Cox", "William", "234 Church St."

How would the awk script combine lines 2 & 3 and add the appropriate space?

Code:
"Smith", "Margie", "200 Elm St."
"Doe", "John", "N", "123 Maple St. Apt. C."
"Cox", "William", "234 Church St.

"
Last edited by Franklin52; 09-03-2012 at 03:35 AM.. Reason: Please use code tags for data and code samples
# 4  
Old 09-03-2012
Just add quotes to Don's solution....

Code:
awk '/^["]/{if(out != "") print out;out = $0;next}
        {out = out" "$0}
END     {if(out != "") print out}' input

This User Gave Thanks to pamu For This Post:
koeji
# 5  
Old 09-03-2012
Quote:
Originally Posted by pamu
Just add quotes to Don's solution....

Code:
awk '/^["]/{if(out != "") print out;out = $0;next}
        {out = out" "$0}
END     {if(out != "") print out}' input

Adding quotes would produce:
Code:
"apple","banana","orange"
"pineapple","grape","straw berry"
"apple","banana","cherry"

instead of:
Code:
"apple","banana","orange"
"pineapple","grape","strawberry"
"apple","banana","cherry"

for the sample input that was given in the first post in this thread. You have to choose whether you want"strawberry"and"123 Maple St.Apt. C."OR"straw berry"and"123 Maple St. Apt. C.". If you want"strawberry"and"123 Maple St. Apt. C.", you need to specify the logic that should be used to determine when a space is needed and when it isn't.

Note also that in koeji's original post explicitly stated:"I have a csv file with 3 columns.", but the"actual sample"provided in a later post has:
Code:
"Doe", "John", "N", "123 Maple St.
Apt. C."

which not only has 4 fields (instead of 3), but also has <comma><space> separated fields instead of <comma> separated fields.

With the original problem statement, I had considered pairing three sets of <double-quote> characters instead of just looking for lines that didn't start with a <double-quote>. In the future, please post real examples, instead of simplified examples that don't match the actual input you expect to be processing.
# 6  
Old 09-03-2012
You can give this a try:
Code:
awk -F\" '!(NF%2){s=$0;getline;$0=s OFS $0}1' file

This User Gave Thanks to Franklin52 For This Post:
koeji
# 7  
Old 09-04-2012
Thanks to all. While I could not provide actual examples due to the content, I should have been more clear in the requirements and provided a better example. It has helped my learning tremendously.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Remove carriage returns from awk output

I'm on Linux version 2.6.32-696.3.1.el6.x86_64, using the Ksh shell. I'm working with the input file: John Daggett, 341 King Road, Plymouth MA Alice Ford, 22 East Broadway, Richmond VA Orville Thomas, 11345 Oak Bridge Road, Tulsa OK Terry Kalkas, 402 Lans Road, Beaver Falls PA Eric Adams,... (2 Replies)
Discussion started by: prooney
2 Replies

2. Shell Programming and Scripting

Removing carriage returns from multiple lines in multiple files of different number of columns

Hello Gurus, I have a multiple pipe separated files which have records going over multiple Lines. End of line separator is \n and records going over multiple lines have <CR> as separator. below is example from one file. 1|ABC DEF|100|10 2|PQ RS T|200|20 3| UVWXYZ|300|30 4| GHIJKL|400|40... (7 Replies)
Discussion started by: dJHa
7 Replies

3. Shell Programming and Scripting

Multiple carriage returns within quotation marks causing new lines in csv

There is a closed thread called "carriage returns within quotation marks causing new lines in csv" that I am unable to post to, so I am starting a new thread. The awk solution worked perfectly in most cases. We have some cases where there are multiple carriage returns within a single quoted... (9 Replies)
Discussion started by: Mary Roberts
9 Replies

4. Shell Programming and Scripting

TR not removing carriage returns

I have a CSV with carriage returns in place of newlines. I am trying to use tr to remove them, but it isn't working. Academic year,Term,Course name,Period,Last name,Nickname 2012-2013,First Semester,English 12,4th Period,Arnold,Adam 2012-2013,First Semester,English 12,4th Period,Adams,Jim... (1 Reply)
Discussion started by: nextyoyoma
1 Replies

5. Shell Programming and Scripting

Why double quotation marks doesn't work in ksh function parameters passing?

I'm working on AIX 6, ksh shell. The parameters are some strings quotated by double quotation marks which from a file. They are quotated because there may be spaces in them. Example: "015607" "10" " " "A"I want to pass these parameters to a shell function by writing the following command: ... (4 Replies)
Discussion started by: Shimmey
4 Replies

6. Shell Programming and Scripting

Excel CSV with Double Quotes and Carriage Returns

Hi List, I am sure this issue must have been encountered before. I have saved an excel CSV and sent it to my linux box where I require to parse it as a CSV line by line. The file displays fine in Excel with csv headers and consistent records and fields. However when I look at it in linux I see... (2 Replies)
Discussion started by: landossa
2 Replies

7. Shell Programming and Scripting

Count number of character occurence but not from quotation marks

I have the following string: 31-01-2012, 09:42:37;OK;94727132638;"Mozilla/5.0 (Linux; U; Android 2.2.1)";3G;WAP;I need a script which is counting the occurrence of semicolons ( ; ) but exclude the ones from the quotation marks. In the string given as example there are 8 semicolons but the script... (3 Replies)
Discussion started by: calinlicj
3 Replies

8. Shell Programming and Scripting

enclose a line with quotation marks

i have a file like this aaaa bbbb cccc aaa aaaa aa cccccccccccccccc aaaaaaa aaaa aaaa i want to enclose this lines with double quotation: "aaaa bbbb cccc" "aaa aaaa" "aa cccccccccccccccc" "aaaaaaa aaaa aaaa" any idea? (preferably without using sed) thanks in advance... (3 Replies)
Discussion started by: gfhgfnhhn
3 Replies

9. Shell Programming and Scripting

Removing carriage returns with sed

How do we delete all carriage returns after a particular string using sed inside a K Shell? e.g. I have a text file named file1 below: $ more file1 Group#=1 User=A Role=a1 Group#=2 User=B Role=a1 Role=b1 Group#=3 User=C Role=b1 I want the carriage returns to be delete on the... (12 Replies)
Discussion started by: stevefox
12 Replies

10. Shell Programming and Scripting

spaces and carriage returns in 'here documents'

As the title suggests, i am having some trouble figuring out how to pass spaces and carriage returns to a 'here document' ie #!/bin/bash /usr/local/install_script.sh <<SCRIPT yes no <pass carriage retun here> yes no <pass a space and then a carriage return here> exit SCRIPT any... (0 Replies)
Discussion started by: hcclnoodles
0 Replies
Login or Register to Ask a Question