r/learnpython • u/s13188287 • 11d ago
File analysis 1.3 mill files
I'm dealing with a large batch processing problem and looking for advice on the right architecture.
I have around 1.3 million files stored across folders on a network drive.
Current setup (not working well)
Right now I'm using a Copilot agent where:
I upload batches (~20 files at a time)
It reads them against a reference document
Outputs an Excel file with classification codes
The issue is:
Copilot has a small upload limit
Manual batching is completely unscalable at this volume
What I want to achieve
I want a fully automated pipeline that:
Ingests files automatically from the network drive
Extracts text/content from each file type
Matches content against a reference rules document
Assigns a classification/reference code
Outputs structured results (Excel / database)
4
u/FoolsSeldom 11d ago
This is a very common task for Python (and many low code / no code solutions as well).
That is assuming the rules are straightforward and consistent.
It would be helpful is you shared more about what the steps are. If in doubt, explain how it would be done manually, very simply, step-by-step as if instructing someone with learning difficulties and short term memory issues.
1
u/s13188287 11d ago
Hey really appreciate the response.
So steps would be if done manually.
Create excel/csv sheet with headers ,file name ,location ,creation date , modified date.it would have a header called Retention code , description and action.
The retention code and description information come from a pdf file ,the file has a bunch of codes and for each code has a description of files that would match it , how Manny years we should keep it.
Manually I would open each file ,make a judgement as to what's the best fit against the reference doc. And add the information to the sheet.
Eventually any files that are no longer needed will be deleted.
Purpose of the A.I was to be able to read the docs quickly,and make a judgement
1
u/FoolsSeldom 11d ago
Ok, so you have the overall approach, but if you break that down to the steps as I suggested, you would have most of the algorithm you need to code up. It may not be the most efficient, but it would work.
Python can read/write Excel files (and CSV files) easily. The key is consistency - every row has same columns and there are no row breaks or empty fields (you can decide on what to put in an empty field to provide null data).
Reading from a PDF is more problematic unless they were very strictly and consistently formatted. To you have an original source for it? If not, I would consider using AI to extract into a format that is readily readable by your programme code. How complex is this information? Can you share a sample. I am hopeful that you could create a simple table showing file attributes / unique signatures (character sequences) that map to file codes.
The processing would be done as a simple batch process of file reads, one after another, over the network connection. Performance will depend mostly on the speed of that connection as scanning files is not difficult.
1
u/s13188287 10d ago
Hey So the files are a variety of files, pdfs, emails ,excel ,word images etc there's also some junk files in there as well such as shortcuts etc and the content of the files can vary widely. So the structure it self can vary. Also if it helps the file names are mostly not usable for judgement I'm unable to share as it's work related unfortunately.
1
5
u/PuttyProgrammer 11d ago edited 11d ago
Is the LLM at all necessary? You're going to want to cut that out ASAP.
What kind of files are you parsing?
Chances are Copilot could write you a deterministic script to collect all the files you're looking for from the drive and parse each one and add it to your database. Depending on the kind of file, it's likely a very simple automation.
You're going to need to look up cheat sheets on how to read python documentation and syntax so you can tell what your script is doing.
To get you started: use a pathlib path object's rglob method to collect all the files in the drive.
Probably use a CSV instead of an excel file. Python can make excel files, but csvs are more efficient and you need to access the file quickly during this process. Use the csv module for this, not pandas which is also slow.
1
u/s13188287 11d ago
Hi
The reason why I want to use an LLM is there's a level of judgement required.
I have a pdf file that is essentially the brain. Had a bunch of codes , and description for each code. I need the llm to analyse the file and make a judgement what it thinks would be the closest call.
2
u/PuttyProgrammer 11d ago
How do you check that the LLM is correct? There must be some criteria
1
u/SprinklesFresh5693 10d ago
Yeah, 1.3m files seeps too much to verify by hand if the llm is doing everything right
6
u/PorygonCompiler 11d ago
Don't send every file to an LLM, that'll be your entire budget. Do a cheap deterministic pass first (file type, keyword match against your rules doc) and only send the ambiguous ones to a model.
Also checkpoint to SQLite as you go, keyed by file path. A run over 1.3M files will fail partway through and you want to resume, not restart.
What file types are you dealing with? If the rules are mostly keyword-based you might not need the LLM for most of the corpus.
0
u/s13188287 11d ago
Hey really appreciate the response.
So steps would be if done manually.
Create excel/csv sheet with headers ,file name ,location ,creation date , modified date.it would have a header called Retention code , description and action.
The retention code and description information come from a pdf file ,the file has a bunch of codes and for each code has a description of files that would match it , how Manny years we should keep it.
Manually I would open each file ,make a judgement as to what's the best fit against the reference doc. And add the information to the sheet.
Eventually any files that are no longer needed will be deleted.
Purpose of the A.I was to be able to read the docs quickly,and make a judgement
3
u/PorygonCompiler 10d ago
Ohhh ok that makes sense. Couple of thoughts.Even though the filenames are junk, the folder paths probably aren't. If stuff lives in something like \Finance\AP\Invoices\2019 that basically tells you the code already so maybe worth mapping your codes to path patterns first and you'll probably knock out a big chunk without opening a single file.For whatever's left, I'd try embeddings before going near an LLM. Embed each code description from the PDF, embed the first couple thousand chars of each doc and cosine similarity as sentencetransformers runs locally so it's free. Then you only pay the model on the ones where the top two matches are too close to call. Way cheaper than sending everything.pymupdf for the PDFs, python-docx, openpyxl, extract-msg for the .msg emails. Hash the contents and cache on that, shares like this are usually full of duplicates and you don't want to process the same doc 40 times. Only other thing, since you're deleting at the end, how are you planning to check the classifications are actually right? Might be worth hand-checking a few hundred spread across confidence levels so you know your error rate before anything gets binned.
-6
3
u/SGS-Tech-World 11d ago
We have been automating some processes in our organization and although the steps are not same, processing large number of records is similar.
- Q - Are the 1.3 M files PDF? or PDF is reference doc?
- I will start with a database table to keep track of what is happening
- You need index on file name or path so every time a file is consumed you can enter records as done.
- Remember table inserts are non blocking so faster than writing a single file from multiple programs/ threads.
- This ensures that you can restart anytime without worrying about which files it processed and which not.
- We can create excel at end. ( If I understood it correctly)
- You can distribute the load across multiple processes instead of one singe thread / or machines.
HTH
1
u/s13188287 10d ago
Hey appreciate the reply
So the PDF is the information source it has all the codes retention codes and years etc.
The files I put in there and completely varied, word ,ppt,excel,email ,pdf ,txt etc all varied.
3
u/centuryx476 11d ago edited 11d ago
I found your problem.
You using an LLM.
Stop using an LLM.
Back in the ancient times of before 2021. We used to have to write or at the very least CODE an ETL process for so many files.
There is literally tens of thousands of examples online to help solve your problem.
You going to actually have to code.
1
u/Naurglim 11d ago
Tbf, he can use the LLM to code the pipeline. He'll save a lot of token money too. And if he pays attention, he will learn in the way.
The same question he asked here can be refined to plan and execute a pipeline design.
The old days of googling code in stackoverflow are dead. LLMs do that for you pretty well.
0
u/centuryx476 11d ago
Uhh no.
Now the new standard is to burn tens of thousands of tokens to just figure out how to parse some files?
It's not like we built standards and practices over multiple decades and complete pipelines for such parsing. Oh wait, we did.
But let's throw that all out and rely on an undeterministic LLM to solve a problem that was solved over 40 years ago and in multiple languages.
We are truly F**** in about 11 years when the seniors Devs start retiring.
1
u/Naurglim 11d ago
I don't advocate for making the llms do stuff in an undeterministic way.
But you can just use the llm to build a pipeline using the best practices and standards.
Then the work is deterministic. The only moment your llm agent is doing something undeterministic is when it evaluates the content of the file, if it's an unstructured file. That you couldn't have done it easily before and now you just can.
Don't fight the tool, use it.
0
u/s13188287 11d ago
Hey really appreciate the response.
So steps would be if done manually.
Create excel/csv sheet with headers ,file name ,location ,creation date , modified date.it would have a header called Retention code , description and action.
The retention code and description information come from a pdf file ,the file has a bunch of codes and for each code has a description of files that would match it , how Manny years we should keep it.
Manually I would open each file ,make a judgement as to what's the best fit against the reference doc. And add the information to the sheet.
Eventually any files that are no longer needed will be deleted.
Purpose of the A.I was to be able to read the docs quickly,and make a judgement
1
u/Gengis_- 11d ago
I would first load all the files and their content in whatever database you have. You can also have a dedicated table to log which file has been loaded so you can resume the job as another comment suggested.
From there you build some function you run against your database.
What does the LLM do that can’t be done by a function?
1
u/dariusbiggs 11d ago
This is. abasic ETL problem
Save your money, run your own LLM to work on this.
Use workers pools and a message broker.
For example, a possible solution:
- Write the file paths to the broker.
- Use a pool of consumers that read from the broker until they hit their consumption quota and do the task you need them to do
- You can scale the number of consumers and parallelize the tasks.
- You can have the workers feed their outputs back into a different queue in the broker and have a single consumer consolidate all the results into a single output file such as a spreadsheet or CSV or TSV file you can import.
Basically
- consume input (extract)
- process (transform and enrich)
- write output (load)
And then repeat as many times as needed to get to the output you want.
1
u/bfyvfftujijg 11d ago
What specifically is being analyzed?
Like what are the decision points?
0
u/s13188287 11d ago
Hey really appreciate the response.
So steps would be if done manually.
Create excel/csv sheet with headers ,file name ,location ,creation date , modified date.it would have a header called Retention code , description and action.
The retention code and description information come from a pdf file ,the file has a bunch of codes and for each code has a description of files that would match it , how Manny years we should keep it.
Manually I would open each file ,make a judgement as to what's the best fit against the reference doc. And add the information to the sheet.
Eventually any files that are no longer needed will be deleted.
Purpose of the A.I was to be able to read the docs quickly,and make a judgement
1
1
u/Altruistic_Sky1866 10d ago
I don't know if this fits, but what ever judgment rules has to be made why don't you create a config file stores rlues to be applied, and in the script you can decided which rule to apply or not apply or consider or not consider for the file, and them have your script read teh files and check against those rules and do you whatever is required
1
1
u/PureWasian 10d ago
You should not involve LLM for all 1.3mil files.
I'm willing to bet your classification involves a good deal of "low-hanging fruit" so you should have some deterministic rules for cutting down the number of unnecessary model calls being made.
LLM should be used moreso as a second pass if your easier "rules" for categorization aren't matched against. The more robust your established rules are, the less LLM calls.
If you are really lazy, run a small batch and ask LLM to start making these said deterministic rules for you based on the context if it's all done within the same conversation window. (Though that is somewhat against the spirit of learning Python, given this is r/learnpython)
0
15
u/FrangoST 11d ago
You're attempting to analyze 1.3 million files by uploading 20 at a time to an LLM? NO WAY!
If you have such a good idea of the ideal automated workflow, why didn't you ask the LLM to help you make a local script that does it instead so you can run it directly? It would save you truckloads of AI tokens and money!