We are trying to import CSV file into Access Database via Powershell. My input file size is 1GB and it is getting difficult to iterate through each row and use insert command. Any quick suggestions here are highly appreciated
Thanks!!
CodePudding user response：
as expresssed by @AlbertD.Kallal - what is the reason to use powershell at all? ... I simply made an assumption that you sought something that would run automatically, daily, unattended - - - as that is a typical reason.
if that is the case then it really breaks down to 2 parts:
- make the import work manually in Access - - and then set up that import to fire automatically upon start/open of the Access file (auto exec).
- just use powershell to start/open the Access file daily (or whenever...).
Access is not designed to be open full time and run unattended. So this is the typical approach to use it in that mode.
CodePudding user response：
Ok, now having stated no need for power-shell, there are cases in which the IT folks and people are using power-shell to automate processes. So it not "bad" to consider power-shell - especially if it is being used. I only wanted to point out that PowerShell will not help performance wise - and probably will be slower.
If you have (had) to say schedule a import to occur every 15 min or whatever?
Then I suggest setting up a VBA routine in a standard code module in Access to do the import. You then in the power shell, or windows script launch access, and then call that import routine. So, first step is to setup that routine in Access - even if using some kind of batch system for scheduling that import routine to run.
So, you use the windows scheduler.
It would: launch access, run the VBA sub, shutdown Access.
And using the windows scheduler is quite robust. So, we don't need (or want) to keep access running, but only launch it, run the import, and then shutdown access.
Next up, if the import process is "huge" or rather large, then on startup, a temp accDB file can be created, and then we import into that. We then can take the import table and send it into the production data table. (often column names are different etc. It also of course much safer to import into that temp table, and better yet, we can delete that temp file after - and thus we never suffer bloating or file size problems (no need to compact repair).
So, the first thing to do is manually import the csv file using the Access UI. This ALSO allows you to create setup a import spec. That import spec can thus remember the data types (currency, or often date time columns).
Once we have the import working and the import spec created?
Then we now can write code to do the above same steps, and THEN take the imported table and put that data into the production data table.
It it not clear if you "stage" the imported csv into that temp table, and then process that table into the real production data table, but I do suggest doing this.
(too dangerous to try and import directly into the production data).
you also don't share right now what kind of pre-processing, or what additonal code is required after you do the import of that csv (but, still, we assume now that such imports will be into a new temp table).
So, I would assume the steps are:
we import the csv file using built in import abiity of Acces
we then send this data table to the production table, perhaps with some code processing of each row before we send that temp table to production table.
Once we done the import, then we dump delete the temp accDB file we used for the import, and thus we eliminate the huge data bloat issue.
Thus for the next time, then we create that temp file for a fresh import, and thus each time we start out with a nice empty database file.
So the first question, and you can create a blank new database for this test. Do you or can you import the csv file using Access. You want to do this, since such imports are VERY fast and VERY high speed. Even if the imported format is not 100% as you want, you do need to confirm and try if using the access UI you can import the csv file. if you can, then we can adopt VBA commands to do the same thing, but no use writing code if a simple csv import via Access UI can't be used.