Using awk to split CSV file by column

Question

I have a CSV file that I need to split by date. I've tried using the AWK code listed below (found elsewhere).

awk -F"," 'NR>1 {print $0 >> ($1 ".csv"); close($1 ".csv")}' file.csv

I've tried running this within terminal in both OS X and Debian. In both cases there's no error message (so the code seems to run properly), but there's also no output. No output files, and no response at the command line.

My input file has ~6k rows of data that looks like this:

date,source,count,cost
2013-01-01,by,36,0
2013-01-01,by,42,1.37
2013-01-02,by,7,0.12
2013-01-03,by,11,4.62

What I'd like is for a new CSV file to be created containing all of the rows for a particular date. What am I overlooking?

It runs for less than a second (the prompt returns). I've watched the folder for a few minutes to see if anything populates, but nothing. I've also searched my system to see if the files are being created elsewhere, but no luck. — Lenwood
– Lenwood, Commented Mar 15, 2013 at 19:39
Resolved. It was my line endings. Following the leadings of this thread, I used the file data.csv command to check the file format. I had Mac style line endings, so I used Text Wrangler to change the formatting and now the code above works as expected. — Lenwood
– Lenwood, Commented Mar 15, 2013 at 19:51
@Lenwood - add that as an answer and accept so that this question is closed. No points for you though :-) — Fredrik Pihl
– Fredrik Pihl, Commented Mar 15, 2013 at 20:00
@FredrikPihl I've added the answer below. Can I mark it as closed now, or do I have to wait 2 days? — Lenwood
– Lenwood, Commented Mar 15, 2013 at 20:11

Community · Accepted Answer · 2017-05-23 10:32:03Z

5

I've resolved this. Following the logic of this thread, I checked my line endings with the file command and learned that the file had the old-style Mac line terminators. I opened my input CSV file with Text Wrangler and saved it again with Unix style line endings. Once I did that, the awk command listed above worked as expected. It took ~5 seconds to create 63 new CSV files broken out by date.

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Mar 15, 2013 at 20:07

Lenwood

1,41419 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ed Morton Over a year ago

The posted command will produce output but it's probably overly long and inefficient. The script is closing the input file after every line and then reopening it on the next matching line. It's probably doing that to have as few output files as possible open simultaneously but with modern awks like gawk that's simply not an issue. You should be able to just do: awk -F, 'NR>1 {print > ($1 ".csv")}' file.csv

alfiogang · Accepted Answer · 2018-06-28 16:13:22Z

0

For retrieve information in a log file with ";" separator I use:

grep "END SESSION" filename.log | cut -d";" -f2

where

  -d, --delimiter=DELIM   use DELIM instead of TAB for field delimiter
  -f, --fields=LIST       select only these fields;  also print any line
                          that contains no delimiter character, unless
                          the -s option is specified

edited Jun 28, 2018 at 16:13

answered Jun 28, 2018 at 15:21

alfiogang

5145 silver badges8 bronze badges

Collectives™ on Stack Overflow

Using awk to split CSV file by column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related