Multiple carriage returns within quotation marks causing new lines in csv


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Multiple carriage returns within quotation marks causing new lines in csv
# 1  
Old 01-13-2015
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 field. Is there a way to modify this awk script to have it look for multiple occurrences of CR within a single quoted field?

The example given was:

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

My example would be:

Code:
"apple","banana","orange"
"pineapple","grape","straw

berry"
"apple","banana","cherry"

Thanks for any help.

Last edited by vgersh99; 01-16-2015 at 12:47 PM.. Reason: code tags, please!
# 2  
Old 01-13-2015
The solution posted by Don Cragan in that thread seem to work fine for your example:

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

# 3  
Old 01-16-2015
Thanks for your reply. It turns out that if the CR is just before the final end quote, it does not work. Any other ideas?

*******************************************************

Looks like it only works for cases like this:
Code:
"apple berry company
banner
test"


But not for cases like:
Code:
"apple berry company
banner
"

If the closing quote is the beginning of a new line then it does not work. If the line begins with something else and then a closing quote then it works.

Last edited by vgersh99; 01-16-2015 at 12:46 PM.. Reason: code tags, please!
# 4  
Old 01-16-2015
Try this:-
Code:
awk '/"$/{ORS=RS}!/"$/{ORS=FS}1' file

# 5  
Old 01-16-2015
remove <CR>s first:
Code:
awk '{gsub(/\r/,""); /"$/{ORS=RS} !/"$/{ORS=FS}1' file

# 6  
Old 01-16-2015
Code:
Syntax error

awk: cmd. line:1: {gsub(/\r/,""); /"$/{ORS=RS} !/"$/{ORS=FS}1
awk: cmd. line:1:                     ^ syntax error
awk: cmd. line:1: {gsub(/\r/,""); /"$/{ORS=RS} !/"$/{ORS=FS}1
awk: cmd. line:1:                                   ^ syntax error
awk: cmd. line:2: (END OF FILE)
awk: cmd. line:2: syntax error


Last edited by Scrutinizer; 01-17-2015 at 06:27 AM.. Reason: code tags
# 7  
Old 01-16-2015
Quote from Don Cragun:
Quote:
If you are using a Solaris/SunOS system, use /usr/xpg4/bin/awk or nawk instead of awk .
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. Shell Programming and Scripting

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: "apple","banana","orange" "pineapple","grape","straw... (6 Replies)
Discussion started by: koeji
6 Replies

4. Shell Programming and Scripting

Removing carriage return/line feeds on multiple lines

I would like to remove carriage returns/line feeds in a text file, but in a specific cadence: Read first line (Header Line 1), remove cr/lf at the end (replace it with a space ideally); Read the next line (Line of Text 2), leave the cr/lf intact; Read the next line, remove the cr/lf; Read... (14 Replies)
Discussion started by: tomr2012
14 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

CSV to SQL insert: Awk for strings with multiple lines in csv

Hi Fellows, I have been struggling to fix an issue in csv records to compose sql statements and have been really losing sleep over it. Here is the problem: I have csv files in the following pipe-delimited format: Column1|Column2|Column3|Column4|NEWLINE Address Type|some descriptive... (4 Replies)
Discussion started by: khayal
4 Replies

9. 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

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