2

I got this script my a related question of mine - How do I insert the filename and header to the beginning of a csv

find . -name '*.csv' -printf "%f\n" |
sed 's/.csv$//' |
xargs -I{} sed -i '1s/^/customer|/ '$'\n'' 1!s/^/{}|/' {}.csv;

Currently for large files it takes quite some time. I scaled it to 50,000 files and got this result.

real    1m41.251s
user    0m59.326s
sys     0m38.681s

For 100,000 files I got this.

real    3m18.466s
user    1m58.451s
sys     1m16.550s

du -sh for 100,000 files gives 485M. I would like to scale this data up to 10-20 GB.

I would like to know if there is any way to speed up the above script. I'm open to using any tools to speed things up.

If it helps, I'm using Ubuntu 18.04.02 LTS, 16 GB RAM.


Using Ed Morton's answer to my question

time awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}' *.csv

real    0m20.253s
user    0m3.336s
sys     0m14.854s

It's blazingly faster than the inital sed :o. I don't understand why though. If anyone can explain it, it would be really helpful.


The above script when I scaled it to a million files, says Argument list too long.

I tried the following, but it is slow,

find . -name \*.csv -exec awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}' {} \;

Even if I do it batch wise, it seems slow for 100,000 files.

time find . -name "10*.csv" -exec awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}' {} \;

real    9m29.474s
user    2m3.336s
sys     6m37.822s

I tried the usual for loop using Ed's answer but it seems to work at the same rate as the original files being generated, around 40 minutes for 1 million records.

for file in *.csv; do
    echo "$file"
    awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}' "$file"
done

I tried to batch it using ls and xargs for every 100,000 files, and this seems reasonable as the initial solution Ed has given.

time ls 11*.csv | xargs awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}'

real    0m23.619s
user    0m3.537s
sys     0m15.272s

time ls 12*.csv | xargs awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}'

real    0m25.044s
user    0m3.892s
sys     0m16.261s

time ls 13*.csv | xargs awk -i inplace -v OFS='|' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}'

real    0m24.997s
user    0m4.035s
sys     0m16.757s

What I'm planning now is to use the above solution, use a for loop to batch through. Given an avg of 25 secs for each batch, it'd be over by 25*10 -> 4 mins appx. Which I feel is fast for the million records.

If anyone has a better solution please let me know. If any of the code written above is wrong/bad please let me know. I'm still a beginner and might have copied or understood things improperly.

  • Are all these files in a single directory or are there any sub directories within that directory? Also it may help to explain exactly what your sed commands are doing, The whole xargs command is not obvious to me. – Jesse_b Aug 2 at 21:07
  • Reading and writing thousands of files takes time, there's not really any way around that. – Barmar Aug 2 at 21:44
  • @Barmar: Saving even a fraction of a second per iteration will make a huge difference in the overall runtime especially when there are more files. – Jesse_b Aug 2 at 21:54
  • 2
    wrt It's blazingly faster than the inital sed - you don't have an initial sed, you have an initial shell script that calls find + sed + xargs + many-calls-to-sed. For the shell, spinning up all those processes takes time vs creating just 1 process for the awk script. – Ed Morton Aug 3 at 4:22
  • 1
    @EdMorton Please add your answer here also if you don't mind? I can accept it as correct. – scientific_explorer Aug 3 at 4:46
2
$ awk -v OFS=',' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}' 10000000.csv
customer,first_name,middle_name,last_name,gender,email,phone_number,address,city,state,country,date_order_start,date_order_complete,invoice_number,invoice_date,item,item_price,quantity,cost,job_name,job_price,total_cost
10000000,Chae,Jesusa,Cummings,Female,deifier2040@example.com,555-555-8750,911 Hauser Pike,Moline,Georgia,Cameroon,2016-06-29,2016-07-16,36298,2016-07-17,Acer,493.86,14,354.77,Broken,123.68,898.13

so with any awk you could do:

for file in *.csv; do
    awk 'script' "$file" > tmp && mv tmp "$file"
done

or with GNU awk for "inplace" editing:

$ tail -n +1 10000000.csv 10000001.csv
==> 10000000.csv <==
first_name,middle_name,last_name,gender,email,phone_number,address,city,state,country,date_order_start,date_order_complete,invoice_number,invoice_date,item,item_price,quantity,cost,job_name,job_price,total_cost
Chae,Jesusa,Cummings,Female,deifier2040@example.com,555-555-8750,911 Hauser Pike,Moline,Georgia,Cameroon,2016-06-29,2016-07-16,36298,2016-07-17,Acer,493.86,14,354.77,Broken,123.68,898.13

==> 10000001.csv <==
first_name,middle_name,last_name,gender,email,phone_number,address,city,state,country,date_order_start,date_order_complete,invoice_number,invoice_date,item,item_price,quantity,cost,job_name,job_price,total_cost
Fleta,Rosette,Hurley,Other,tobacconist1857@example.com,1-555-555-1210,35 Freelon Arcade,Beaverton,Rhode Island,Cayman Islands,2009-06-08,2009-06-29,39684,2009-07-01,NVIDIA GeForce GTX 980,474.31,16,395.79,Broken,157.53,1088.04
Bennett,Dennis,George,Male,dona1910@example.com,(555) 555-4131,505 Robert C Levy Arcade,Wellington,Louisiana,Mexico,2019-05-09,2019-05-19,37938,2019-05-21,8GB,187.67,16,205.77,Service,170.21,1007.85
Tommye,Pamula,Diaz,Other,dovelet1967@example.com,555.555.4445,1001 Canby Boulevard,Edinburg,Massachusetts,Gambia,2004-05-02,2004-05-24,31364,2004-05-26,Lenovo,137.21,13,193.63,Replacement,246.43,934.31
Albert,Jerrold,Cohen,Other,bolio2036@example.com,+1-(555)-555-8491,1181 Baden Avenue,Menomonee Falls,Texas,Tajikistan,2019-08-03,2019-08-12,37768,2019-08-15,Intel® Iris™ Graphics 6100,396.46,17,223.02,Service,118.53,960.27
Louetta,Collene,Best,Fluid,dinner1922@example.com,1-555-555-7050,923 Barry Viaduct,Laurel,Illinois,St. Barthélemy,2009-03-02,2009-03-06,39557,2009-03-07,AMD Radeon R9 M395X,133.9,11,198.49,Fix,178.54,1055.32
Kandace,Wesley,Diaz,Female,closterium1820@example.com,+1-(555)-555-5414,341 Garlington Run,Santa Maria,New Jersey,Mexico,2005-10-09,2005-10-10,30543,2005-10-14,Samsung,590.29,5,354.85,Service,292.56,1032.22

.

$ awk -i inplace -v OFS=',' 'FNR==1{cust=FILENAME; sub(/\.csv$/,"",cust)} {print (FNR>1 ? cust : "customer"), $0}' 10000000.csv 10000001.csv

.

$ tail -n +1 10000000.csv 10000001.csv
==> 10000000.csv <==
customer,first_name,middle_name,last_name,gender,email,phone_number,address,city,state,country,date_order_start,date_order_complete,invoice_number,invoice_date,item,item_price,quantity,cost,job_name,job_price,total_cost
10000000,Chae,Jesusa,Cummings,Female,deifier2040@example.com,555-555-8750,911 Hauser Pike,Moline,Georgia,Cameroon,2016-06-29,2016-07-16,36298,2016-07-17,Acer,493.86,14,354.77,Broken,123.68,898.13

==> 10000001.csv <==
customer,first_name,middle_name,last_name,gender,email,phone_number,address,city,state,country,date_order_start,date_order_complete,invoice_number,invoice_date,item,item_price,quantity,cost,job_name,job_price,total_cost
10000001,Fleta,Rosette,Hurley,Other,tobacconist1857@example.com,1-555-555-1210,35 Freelon Arcade,Beaverton,Rhode Island,Cayman Islands,2009-06-08,2009-06-29,39684,2009-07-01,NVIDIA GeForce GTX 980,474.31,16,395.79,Broken,157.53,1088.04
10000001,Bennett,Dennis,George,Male,dona1910@example.com,(555) 555-4131,505 Robert C Levy Arcade,Wellington,Louisiana,Mexico,2019-05-09,2019-05-19,37938,2019-05-21,8GB,187.67,16,205.77,Service,170.21,1007.85
10000001,Tommye,Pamula,Diaz,Other,dovelet1967@example.com,555.555.4445,1001 Canby Boulevard,Edinburg,Massachusetts,Gambia,2004-05-02,2004-05-24,31364,2004-05-26,Lenovo,137.21,13,193.63,Replacement,246.43,934.31
10000001,Albert,Jerrold,Cohen,Other,bolio2036@example.com,+1-(555)-555-8491,1181 Baden Avenue,Menomonee Falls,Texas,Tajikistan,2019-08-03,2019-08-12,37768,2019-08-15,Intel® Iris™ Graphics 6100,396.46,17,223.02,Service,118.53,960.27
10000001,Louetta,Collene,Best,Fluid,dinner1922@example.com,1-555-555-7050,923 Barry Viaduct,Laurel,Illinois,St. Barthélemy,2009-03-02,2009-03-06,39557,2009-03-07,AMD Radeon R9 M395X,133.9,11,198.49,Fix,178.54,1055.32
10000001,Kandace,Wesley,Diaz,Female,closterium1820@example.com,+1-(555)-555-5414,341 Garlington Run,Santa Maria,New Jersey,Mexico,2005-10-09,2005-10-10,30543,2005-10-14,Samsung,590.29,5,354.85,Service,292.56,1032.22

If you have too many files to pass on the command line and running it via xargs is too slow then here's another option:

awk -i inplace ... '
    BEGIN {
        while ( (getline line < ARGV[1]) > 0 ) {
            if ( line ~ /\.csv$/ ) {
                ARGV[ARGC] = line
                ARGC++
            }
        }
        ARGV[1] = ""
    }
    { the "real" script }
' <(ls)

The above reads the output of ls as an input file rather than as arguments, populates the array of arguments with those file names that end in .csv, and then operates on the files as if they had been passed as arguments on the command line.

  • Thank you for your answer. Please see the new edits/test results that I have shown/updated in my question. I hope you'd be able to help me out. – scientific_explorer Aug 3 at 12:25
  • Did you time just ls *.csv | xargs awk ... instead of doing it in batches? There's no benefit to passing subsets of files at a time to xargs AFAIK. The find and for loop approaches are calling awk one file at a time so those will be too slow. – Ed Morton Aug 3 at 12:35
  • ls *.csv again gives the same Argument list too long error. Same for your awk solution. I am trying to figure out how to get past that. Additionally I am trying to address how to write a for loop in for 11*.csv, 12*.csv and so on to avoid the error above. Still googling on that part. – scientific_explorer Aug 3 at 12:39
  • Sorry, you can't use *.csv as an arg or it'll expand to your long list of file names, I should have said just ls | xargs awk... in a directory of just your .csvs. Or you could do find . -name '*.csv' | xargs awk.... I updated my question to show an alternative to using xargs as well. All the usual caveats of parsing the output of ls apply of course (see mywiki.wooledge.org/ParsingLs). A loop would just be for i in {11..20}; do ls "$i"*.csv; done. – Ed Morton Aug 3 at 12:44
  • If you don't mind can you please fill the "the real script" part also in your answer? I keep getting syntax errors. :( – scientific_explorer Aug 3 at 13:29
0

You can try the following two approaches:

$ find . -name \*.csv -type f ! -empty -exec \
  perl -spe 's/^/,/;
   $F //= $ARGV =~ s/\.csv$//r;
   s/^/$. == 1 ? "\n$C" : $F/e;
   undef $F, close ARGV if eof;
' -- -C="Customer" {} +

The second one makes use of Gnu sed features, in particular the the F command to get the filename and -s option to treat multiple files not as a single stream but rather individually:

$ find . -name \*.csv -type f ! -empty -exec \
  sed -se 'F;1s/^/CUSTOMER,/' {} + |
  sed -E \
    -e 'N;s/.*\.csv(\nCUSTOMER,)/\1/;t' \
    -e 's/\.csv\n/,/;s/..//' \
;

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.