2

edit: Now that I got answers, I marked one by @KamilMaciorowski that fits better to the title as an answer, but this answer by @oliv actually were better suited to my actual need to my primary purpose. (To process csv file with breaks consistently on awk.)

So in the case if you were looking for awking in the similar circumstance, I recommend checking that first!


Please help me preparing a few thousands of csv file ready for awk to process! Some of the field has line breaks inside the field and that's causing awk to process them as a multiple records. However those problematic line breaks only happens where ^M is inserted, so I just need to remove ^M and line-break altogether from all of them.

*These ^Ms are indeed line break character, not literal caret & letter M string. This file is generated for .net to parse and process, but I haven't worked on developing apps on neither file producing/reading sides, so I don't really know how it's successfully parsed. It's exclusively used for fields in certain columns with multiple-lined strings (comments).

So how do you make this (csv with 1 header and 2 records. Some field has line breaks in it preceeded by ^M):

"header_1", "header_2", "header_3"
"1-1", "1-2", "1-3"
"2-1", "2-2_a^M
2-2_b^M
2-2_c", "2-3"

like this? (csv with 1 header and 2 records without line breaks within each of them.):

"header_1", "header_2", "header_3"
"1-1", "1-2", "1-3"
"2-1", "2-2_a2-2_b2-2_c", "2-3"

I tried removing them with sed but I heard there's no way to process, and I didn't quite get the reason why.

for file in *.csv; do
    sed -e "s/^M//" $file > sedded/$file;
done

Anyhow, I get this:

"header_1", "header_2", "header_3"
"1-1", "1-2", "1-3"
"2-1", "2-2_a
2-2_b
2-2_c", "2-3"

I tried to go for something like "s/^M\n/", and it doesn't work as I suspected. Should I use completely different tool like vim? As long as it works for thousands of files at once (each containing ~500 lines, and I don't really care the time it takes to process) I'm fine with any sort of resolution. Just thought sed was the way. (I'm ok to use DOS command/powershell if it's easier or more straight forward!)

  • Is ^M a litteral string or a control character (newline)? – oliv Jun 21 '18 at 8:37
  • @oliv Thanks for asking, I'll update: that is newline that inserted by the app that generated csv files. It uses them in fields with multiple-lined comments. It's primarily for .net application to parse and I don't know how they interpret these files, but that's just how it is. (Probably easier to process this on Windows machine? I'm fine with that too if that's the case.) – AlexKlaus Jun 21 '18 at 8:42
  • @KamilMaciorowski Oh dang that's right thanks for noting, I'm fixing that. – AlexKlaus Jun 21 '18 at 12:19
2

If these ^M-s are indeed line break characters, not literal caret & letter M strings, then they are what we denote \r, CR or 0x0d (compare this answer of mine, the beginning of it).

Your command

sed -e "s/^M//"

doesn't remove \r; it doesn't even remove literal ^M. The command means "take a line, search for a letter M that is at the very beginning of the line (^, see this), replace it with nothing.

Note sed understands \r. Still sed -e 's/\r//' is not exactly what you need. It removes \r but you need to remove the following \n as well. You may want to try sed -e 's/\r\n//', this will also fail. The problem is sed is a text tool and it treats \n as a separator. Excerpt from info sed (emphasis mine):

sed operates by performing the following cycle on each lines of input: first, sed reads one line from the input stream, removes any trailing newline, and places it in the pattern space. Then commands are executed; […].

This means normally \n doesn't belong to any string processed with s/… (or another sed command). For this reason concatenating few lines is not easy. Still it can be done. This is the command you need:

sed -e ': start; /\r$/{ s/\r$//; N; s/\n// }; /\r$/b start'

Explanation:

  • : start is a label.
  • If the line contains \r (i.e. ^M, 0x0d character) at the very end ($), execute the {} block which is:
    • replace \r at the very end with nothing,
    • append an additional line from the input (N),
    • replace \n that separates the additional line from the previous data.
  • If the outcome contains \r at the very end (meaning the additional line brought it, so we need to add yet another line), jump to start.
|improve this answer|||||
  • Thank you, this worked perfectly! And thank you for explanation too. I still don't quite grasp them and how your answer works, but I'll dig in. FYI my command does remove ^M successfully when I use <kbd>Control</kbd>+<kbd>V</kbd> (verbatim insert) to denote ^M is not the literal M at the beginning of the line. – AlexKlaus Jun 21 '18 at 12:11
1

Assuming there are 3 fields in each row, and there isn't any double quote inside any value, your could use this GNU awk script:

awk -v FPAT='"[^"]*"' '{while(NF!=3){p=$0;getline;gsub("^",p)}; p=""}1' file

FPAT defines how a field should look like, i.e anything surrounding with double quotes.

The awk statement builds a record by getting lines from the file until there are 3 fields.

|improve this answer|||||
  • This didn't work only because I had to handle some empty fields as well, so I used -F, instead and it worked! This was indeed the first day ever to use awk so I'm relieved to know how powerful this tool is! Thanks a lot!! – AlexKlaus Jun 21 '18 at 12:00
  • This is very hard choice. For me technically this answer is definitely the best as this one line solved the whole problem at once (not using loop, sed anything else at all), however just for the sake of reference matching my question title and answer, I'm afraid that I'm choosing another one.. I'm too short of reputation to upvote and such but I'll note about this on my question. Sorry, and thanks! – AlexKlaus Jun 21 '18 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.