CODEWITHSUNDEEP
shellcsvawk
Jon Weinraub
Jon Weinraub

Reputation: 394

Convert a file into CSV with newline as the delimiter

I've got a lot of data that is new line delimited, copied raw from a server. However, I cannot convert it into a CSV by simply replacing all \n into a comma since I need to have the fields. There are 9 fields in this occasion. How can I convert this data?
Keeping in mind that the last element doesn't need a comma since that will need to retain the newline character.

I am horrible at regular expression foo, but since I have the loop being needed, I assume I will need to make this into a shell script?

Any help is greatly appreciated.

Example data:

Name
Logon User
IP Address
Version
Last Login
Restart Required
Foo1
Foo2
Foo3
Jon Weinraub
jweinraub
10.18.66.10
3.1.1.1
2013-08-19 14:33:11
No
Bar1
Bar2
Bar3
Homer Simpson
....

So essentially it should be

Name,Logon User, IP,...Foo3
Jon Weinraub,jweinraub,10.18.66.10,...Bar3
Homer Simpson,....

Upvotes: 0

Views: 3238

Answers (4)

William Pursell
William Pursell

Reputation: 212248

A nice awk variant:

awk 'ORS = NR%9 ? "," : "\n"'

ORS is the output record separator. It is set to "," for all lines but the 9th, and since it is always either "," or "\n" the expression always evaluates to true and the default action to print the record (the line followed by ORS) is executed on each line of input.

Upvotes: 1

Floris
Floris

Reputation: 46375

A slightly elaborate way to do it (but it's easy to understand and modify) using awk:

Create a file makeCSV.awk with the following script:

BEGIN {
  count = 0; 
}

{
  count++;
  if (count == 9)
  { 
    count = 0;
    printf "%s\n", $0;
  }
  else
  {
    printf "%s, ", $0;
  }
}

Then you can execute this from the command line with

awk -f makeCSV.awk myInputFile > myOutputFile.csv

Upvotes: 1

Kent
Kent

Reputation: 195049

does this line work for your requirement?

awk 'NR%9{printf "%s,",$0;next}7' file

for example:

kent$  seq 36|awk 'NR%9{printf "%s,",$0;next}7'
1,2,3,4,5,6,7,8,9
10,11,12,13,14,15,16,17,18
19,20,21,22,23,24,25,26,27
28,29,30,31,32,33,34,35,36

Upvotes: 4

Sam Green
Sam Green

Reputation: 25

I've had this issue before as well, and I may be mistaked, but I solved my issue by adding "\r\n" to the end of the line to put the next bit of data onto a new line. So for example you have something like

NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,

You'd need to replace the last comma with an "\r\n". Let me know if this isn't what you're asking for...

Upvotes: 0

Related Questions