looking to automate a process of manipulating with very large .csv files

user_18563133 0
Viewed 82

here is my dilemma. I need to process information from large .csv files. typical size of the file is about 200mb which results in approximately 3-4 million lines. files contain human readable information. 5 fields. my process originally was to take the file and sort it based on the last field

"sort -k4 -n -t, "

then split

"split -l 1000000"

however now i need to add a decode from base64 of the second field.

i used this script for testing

awk 'BEGIN{FS=OFS=","} (cmd="echo "$2" | base64 --decode"; cmd | getline v;$2=v} 1' originalfilename.csv > newfilename.csv

-- while running this command on a sample file a I am getting an error message.

"sh: fork: Resource temporarily unavailable"

and the process halts.

my final output are several .csv files I use to import into Excel tabs for processing.

here are my questions:

  1. why am I getting the error while running awk? is there a different command that will do what i need faster and cleaner

  2. can anyone suggest a better way to process my files? im trying to stay away from java but python script may work.

i am not a professional programmer, hence my knowledge ot tools is not up to date.

any advice/assistance would be greatly appreciated.

Thanks

base64 csv shell unix python
1 Answers

If you can install a program, I recommend GoCSV; it's a purpose-built multi-tool for CSV, has many functions for manipulating data, including decoding base64, and it's pre-built for a number of platforms.

I mocked up a sample CSV:

Col1,Col2,Col3,Col4,Col5
banana,U3RhY2tPdmVyZmxvdw==,apple,0,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,1,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,2,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,3,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,4,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,5,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,6,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,7,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,8,turnip
banana,U3RhY2tPdmVyZmxvdw==,apple,9,turnip

We can build up to a pipeline for:

  • base64-decoding the 2nd column (which actually has to add a whole new column)
  • selecting that new column and putting it back in its place (in essence, overriding the original base64-encoded column)
  • renaming the new column to the old column
  • sorting on the 4th column
  • splitting every 1_000_000 rows

I'll start with the base64 decoding, because to do that we'll actually need to add a new column:

gocsv add -name New_col -t '{{ b64dec .Col2 }}' small.csv

Col1,Col2,Col3,Col4,Col5,New_col
banana,U3RhY2tPdmVyZmxvdw==,apple,0,turnip,StackOverflow
banana,U3RhY2tPdmVyZmxvdw==,apple,1,turnip,StackOverflow
...

We can then use the select command to effecitvely replace the original column with the new column:

...
| gocsv select -c 1,New_col,3-5

Col1,New_col,Col3,Col4,Col5
banana,StackOverflow,apple,0,turnip
banana,StackOverflow,apple,1,turnip

and then rename the new column back to the old name:

...
| gocsv rename -c New_col -names Col2

Col1,Col2,Col3,Col4,Col5
banana,StackOverflow,apple,0,turnip
banana,StackOverflow,apple,1,turnip

From there, add in sorting, based on column 4, and split into CSVs, each no bigger than 1_000_000 rows:

...
| gocsv sort -c 4 -reverse \
| gocsv split -max-rows 1_000_000

(My sort is reversed because column 4 was already in ascending order.)

Here's the whole pipeline:

gocsv add -name New_col -t '{{ b64dec .Col2 }}' big.csv \
| gocsv select -c 1,New_col,3-5 \
| gocsv rename -c New_col -names Col2 \
| gocsv sort -c 4 -reverse \
| gocsv split -max-rows 1_000_000

That small 10-row mock-up is from a big mock-up of your data I made:

% gocsv dims big.csv 
Dimensions:
  Rows: 4000000
  Columns: 5

When I run the whole pipeline on the big CSV:

% time ./main.sh
./main.sh  21.10s user 13.55s system 165% cpu 20.951 total

% ls out*.csv
out-1.csv       out-2.csv       out-3.csv       out-4.csv

% gocsv dims out-1.csv 
Dimensions:
  Rows: 1000000
  Columns: 5

% gocsv head -n 2 out-1.csv
Col1,Col2,Col3,Col4,Col5
banana,StackOverflow,apple,3999999,turnip
banana,StackOverflow,apple,3999998,turnip

% gocsv tail -n 2 out-4.csv
Col1,Col2,Col3,Col4,Col5
banana,StackOverflow,apple,1,turnip
banana,StackOverflow,apple,0,turnip
user_2468010
Related