Importing table from CSV (1 Viewer)

[jaryszek]

Hi Guys,

I have seperate table not connected to any relationships which i have to just import to Access from csv and update what we currently have.
And now what is better?

1. Clear all table record and just replace all (without deleting table).
2. Delete whole table and replace with new
3. Use ID as primary key (i can do this) and just update, delete, append not necessary records.

What method will cause less database bloating and performance issues if tables will have millions of records?

Best,
Jacek

 

[CJ_London]

method 3 will cause less database bloating and, providing the destination table is properly indexed most likely be the best performing. I probably wouldn't delete records as part of the the import process, just flag them as to be deleted - then delete as part of a general maintenance routine

An alternative to create a temporary db, import to that and link to the temp db. Simply replace the temp db on the next reload. If multi user, the temp db probably needs to be in the same folder as the backend.

 

[Auntiejack56]

Hi Jacek,
AFAIK Access will not reclaim space, so both 1 and 2 are going to cause bloat if you are importing millions of records.
3 is a better option.
CJ's suggestion is a good one, which I have used to good effect, and is pretty straightforward and easy to implement.
But there is another solution, which I would be tempted to try if speed is a high priority. Read the CSV as a flat file, and only update the records that you need to change using the primary key that you mentioned in solution 3. If the csv file is in some sort of order, you can make this run very fast by doing a single pass of the csv and the table at the same time, updating as you go.
Jack

 

[jaryszek]

thank you CJ_London,

"Simply replace the temp db on the next reload. "

How to achevie this? Use VBA in order to create temp database and relink tables?
Jacek

 

[jaryszek]

Auntiejackie56 thanks,

this seems to be very nice method.
I will have composite keys - how to make them to be updated?

Just joined them in query or add additional field with concatenaed key? How you would do this?

Jacek

 

[CJ_London]

Use VBA in order to create temp database and relink tables?

providing your tempdb and table both have the same name, no need to relink, just use vba code to delete the temp db then recreate it

 

[Pat Hartman]

I create a template db with the table and keys defined but with no data. To begin, copy the template to the shared folder and link to the table there. Then save the linked, empty database as the master. In the app, copy the template master when you are ready to import the new file and overlay the old copy. No linking. The template is always empty and compacted at the start of the process so you never have to worry about bloating.

 

[jaryszek]

hmm i am not sure if i understood Pat Hartman.

1. First create empty database with keys and relationships - master
2. Copy it - child
3. Create link between master and child.
4. Put data to the child?

Hmm?

Jacek

 

[Gasman]

My interpretation.

Create empty DB with table(s) and keys required. That is your Master.
Anytime you wish to import, you copy the master (lets call the copied version 'slave') and you import to that slave db.
Your DB is linked to the slave DB, and you process from the slave DB.

So, every time you need to import, you start off with a clean small DB that is the master and is copied, however you only ever work with the copied version, the slave?

HTH

 

[Pat Hartman]

Thanks for translating Gasman

 

[Pat Hartman]

It's only a couple of lines of code but I'm in the middle of a bridge game and can't break away to get it for you. If you still need it in a couple of hours, I'll dig it out.

 

[Gasman]

I wish I had known about bloat when I worked at Lloyds Bank, as I had to import a a several brand new excel worksheets every day, that contained a lot we did not need and filter it out for what we did need.
Was continually compacting the DB.

 

[jaryszek]

" It's only a couple of lines of code but I'm in the middle of a bridge game and can't break away to get it for you. If you still need it in a couple of hours, I'll dig it out. "

Please.

"
My interpretation.

Create empty DB with table(s) and keys required. That is your Master.
Anytime you wish to import, you copy the master (lets call the copied version 'slave') and you import to that slave db.
Your DB is linked to the slave DB, and you process from the slave DB.

So, every time you need to import, you start off with a clean small DB that is the master and is copied, however you only ever work with the copied version, the slave? "

Thanks!

So when you copy master and import to this copy you have data in Slave and file is called "Copy of Master".
So now you have already Slave.accdb created where you have already data inside. You are changing "Copy of Master" name to "Slave" and replacing yes?

And thats it?

Jacek

 

[The_Doc_Man]

First put the structured but empty copy of the Temp DB file in the place where you want it to be linked when you will actually use it.
Link it in whatever way is required and save it (linked but with tables still empty) and close it.
Copy THAT file to the location you have designated to hold the master Temp DB copy.
When you copy the master Temp DB to its work area, it will still be empty but it is already linked correctly because links in Access files go by file specifications, not by other methods of file identification.
That means that while it takes an extra step or two to set up, you can use a simple Kill command in VBA to get rid of any previous copy, then a FileCopy command in VBA to make the new copy. Two commands at run time and you are good to go.

 

[Gasman]

Well I was not even thinking of deleting the old file, just copy over it.?

 

[The_Doc_Man]

I think you get a dialog or an error from FileCopy if the destination already exists. But you could do the Kill OR the FileCopy within the scope of an "On Error Resume Next". If so, I would do the Kill with errors blocked like that, but the FileCopy with errors enabled (in case there is some OTHER error besides a file being in the way.)

 

[Gasman]

Doc,
FileCopy overwrites with no dialog or error. Just tried it.

 

[The_Doc_Man]

Darn, I could have sworn that at one time I did that. OK, then it is easier than I thought.

 

[Pat Hartman]

Sorry for the delay. I had started a sample db for this function a while back so I finished it and added instructions. It is a little more complicated than it actually needs to be but it is also more flexible and includes some other features you might find useful. See how it works for you.

 

[Isaac]

One question. Is the external file source, exactly the same as you have your internal table? Columns, datatypes, etc?