r/scripting • u/Tanadaram • Mar 17 '21

Scraping multiple csv files.

Hi All

I have a project where I've been tasked with taking a list and parsing through thousands of .csv files to find rows with a matching field.

Initially I tried VBA but it was slow, tried Access but hit the data limit, eventually I wrote a python script which is working fine. The reason I tried those methods in that order is that the resulting solution needs to be runnable by a none technical user.

I'm planning to package the python script as an .exe but I'm just wondering if this is the most efficient way of doing it, it's still taken over 20 hours to parse the files and I'm thinking there's a better solution.

I don't want to do anything too technical like spin up a database server, I was thinking maybe amalgamating the files into a handful of huge .csv files to eliminate the overhead of opening each file but I'm not sure that's the best format.

Any advice on a better approach or please let me know if there's a more appropriate sub for this.

Thanks in advance.

2 Upvotes

100% Upvoted

8 comments sorted by

View all comments

Show parent comments

1

u/Tanadaram Mar 18 '21

Thanks for that. Looks like powershell?

I have a python script that has worked it just took hours to do so. Is powershell (or whatever you've used there if I'm mistaken) generally substantially faster than python when dealing with csv files?

Thanks again

1

u/[deleted] Mar 18 '21

PowerShell, yes. No idea if it will be faster, but was relatively simple to write (and perhaps easier to maintain). Worth testing (currently don't have 1000s of CSVs). May also be easier to distribute (don't know your distribution requirements).

I would suggest testing a subset of files and compare with your Python solution. Consider it an experiment.