How to get Millions of files from folder and bulk insert in database
-
I have some folders having files, its count like 40Lac, 70Lac, 1Cr, 1.5cr. And filetype like .png, .xlsx, .txt, .msg, .ico, .jpg, .bmp etc...
Now i want to insert this filename into my database table with its size, but when i implement it take too much time to scanning the folder, and then after it throws memory exception.
can anyone please help me out here, how can i implement this scenario in a better manner.
how can insert faster in table ?I am using C#.net with PostgreSQL database.
Ankur B. Patel
-
I have some folders having files, its count like 40Lac, 70Lac, 1Cr, 1.5cr. And filetype like .png, .xlsx, .txt, .msg, .ico, .jpg, .bmp etc...
Now i want to insert this filename into my database table with its size, but when i implement it take too much time to scanning the folder, and then after it throws memory exception.
can anyone please help me out here, how can i implement this scenario in a better manner.
how can insert faster in table ?I am using C#.net with PostgreSQL database.
Ankur B. Patel
First of all, what is a lac and cr? This is never going to be "fast", more important is that you do it correct. I assume your putting all your shit in a blob. Don't, txt should be archived as memo, so you can later use search form DB. If you just want to archive names and their sizes, read the entire folders' contents and spawn some threads to save chuncks of that.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
First of all, what is a lac and cr? This is never going to be "fast", more important is that you do it correct. I assume your putting all your shit in a blob. Don't, txt should be archived as memo, so you can later use search form DB. If you just want to archive names and their sizes, read the entire folders' contents and spawn some threads to save chuncks of that.
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Eddy Vluggen wrote:
First of all, what is a lac and cr?
Yah. So them like Americans with their pounds. In international communication we use the SI system. If you can't, better learn :)
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Yah. So them like Americans with their pounds. In international communication we use the SI system. If you can't, better learn :)
Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Thanks but, I just correct here that forget about lac and cr (it's Lakh and Crore), keep in mind that you have millions of files in a folder and I just want to insert the files name and its size in bytes into the database.
how do I bulk insert? and also keep in mind that system memory doesn't go high.
please suggest to me, how I can do it quickly and securely.
-
I have some folders having files, its count like 40Lac, 70Lac, 1Cr, 1.5cr. And filetype like .png, .xlsx, .txt, .msg, .ico, .jpg, .bmp etc...
Now i want to insert this filename into my database table with its size, but when i implement it take too much time to scanning the folder, and then after it throws memory exception.
can anyone please help me out here, how can i implement this scenario in a better manner.
how can insert faster in table ?I am using C#.net with PostgreSQL database.
Ankur B. Patel
If this is your idea of a backup mechanism, don't bother: it'll never be quick, it'll never be efficient, and it will always risk running out of memory. Instead, think about using a "proper" backup system which archives the disk as sectors instead of files - it's a lot quicker, a lot more efficient, and much, much safer. Remember, backups should be air-gapped for safety: DB's and suchlike are just files and as such are just as much at risk from ransomware and any other data (more so, in some cases as they are a "Prime target" for ransomware to exploit). I use AOMEI Backupper - it has a free Standard version and it allows you to mount the backup images as a disk and retrieve individual files if necessary.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
Thanks but, I just correct here that forget about lac and cr (it's Lakh and Crore), keep in mind that you have millions of files in a folder and I just want to insert the files name and its size in bytes into the database.
how do I bulk insert? and also keep in mind that system memory doesn't go high.
please suggest to me, how I can do it quickly and securely.
Pipe a DIR listing to a text file. That'll give you the list you need; which you can then "substring" and load into a table of files names, etc. Displays a list of files and subdirectories in a directory. DIR [drive:][path][filename] [/A[[:]attributes]] [/B] [/C] [/D] [/L] [/N] [/O[[:]sortorder]] [/P] [/Q] [/R] [/S] [/T[[:]timefield]] [/W] [/X] [/4] [drive:][path][filename] Specifies drive, directory, and/or files to list. /A Displays files with specified attributes. attributes D Directories R Read-only files H Hidden files A Files ready for archiving S System files I Not content indexed files L Reparse Points O Offline files - Prefix meaning not /B Uses bare format (no heading information or summary). /C Display the thousand separator in file sizes. This is the default. Use /-C to disable display of separator. /D Same as wide but files are list sorted by column. /L Uses lowercase. /N New long list format where filenames are on the far right. /O List by files in sorted order. sortorder N By name (alphabetic) S By size (smallest first) E By extension (alphabetic) D By date/time (oldest first) G Group directories first - Prefix to reverse order /P Pauses after each screenful of information. /Q Display the owner of the file. /R Display alternate data streams of the file. /S Displays files in specified directory and all subdirectories.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it. ― Confucian Analects: Rules of Confucius about his food
-
I have some folders having files, its count like 40Lac, 70Lac, 1Cr, 1.5cr. And filetype like .png, .xlsx, .txt, .msg, .ico, .jpg, .bmp etc...
Now i want to insert this filename into my database table with its size, but when i implement it take too much time to scanning the folder, and then after it throws memory exception.
can anyone please help me out here, how can i implement this scenario in a better manner.
how can insert faster in table ?I am using C#.net with PostgreSQL database.
Ankur B. Patel
Hi Ankur, I'm going to assume your files are in a bunch of directories, not all in one, or mostly in a few, but leafy. I'd use the System.IO classes Directory and Path and FileInfo. You can use Directory.GetDirectories to get then directories, then loop over those directory paths calling Directory.GetFiles to get file paths to the files, then for each file path call Path.GetFilename to get the filename, and (new FileInfo(filePath)).Length to get the file length. I'd use a DB transaction per directory, and create an INSERT statement per file and execute it inside the transaction, and commit when you're done with the files in that directory. Things not to do: Don't get all files in all directories. Don't build one SQL statement for all the files. Hope this helps, -Michael
-
I have some folders having files, its count like 40Lac, 70Lac, 1Cr, 1.5cr. And filetype like .png, .xlsx, .txt, .msg, .ico, .jpg, .bmp etc...
Now i want to insert this filename into my database table with its size, but when i implement it take too much time to scanning the folder, and then after it throws memory exception.
can anyone please help me out here, how can i implement this scenario in a better manner.
how can insert faster in table ?I am using C#.net with PostgreSQL database.
Ankur B. Patel
Ankur B. Patel wrote:
can anyone please help me out here, how can i implement this scenario in a better manner.
Then easiest and best answer do not do that in the first place. File systems, not databases, have existed for a long time and exist solely to manage files. What you have are files. If you want to reference the files in the database then do just that. Keep the files on the file system and provide a reference (absolute or relative) to the location of the file. You can keep meta data on the file in the database, such as name, size, type, etc. Might also want to insure uniqueness also. You do that by finding out what makes one file always different from other. Usually some combination of name and location. And insure that is maintained. Finally of course how will these files be used. For example if you expect a png to show up in a web page that has 10,000 unique visits a day, you certainly do not want to be pulling it out of a database.
