import large CSV file(more then 80k records) to MS Access table

[rahul26]

Hi All,
I have got a requirement that I need to import a large CSV file with more then 80k records with some 14 columns to a table directly in MSAccess.
I am a newbie (basically a business analyst so not much coding experience)in MSAccess, so have got no idea whether its possible to implement and how the performance would be.

earlier we were importing from business object report and it was failry fast, but now have apphrehensions how the performnace would be.

Please give me some sample code/macro where we can import CSV file to MSAccess table .

Regards,
Rahul26

 

[DJkarl]

Access should be able to handle that file size easily.

It has no limit on the number of records stored in a table, just the overall size of the database is limited to under 2GB. 80K records with 14 columns should be WELL under that limit.

Look at Docmd.TransferText in Access help or on this forum, this should show you what you need to do to automate the file import.

FYI you should import it once manually and setup an import spec.

 

[rahul26]

thanks for the reply.
Actually what i wanted to is write a macro is in excel which on click of a button is supposed to import the CSV file to a table.
Queries I had are:-
1)Is it advisable to import such huge volums of data through Docmd.TransferText method?
As of now I used to get the data in BO reports.But to cut costs now they want to send files in CSV format.Would it lead to erroring out of many records?

2)what is the best approach to transfer data from CSV to a table directly using a macro in excel?

 

[DJkarl]

1)Is it advisable to import such huge volums of data through Docmd.TransferText method?

80K records is not huge in the world of Access, I've imported CSV files with over a million records. TransferText should work just fine for your needs.

As of now I used to get the data in BO reports.But to cut costs now they want to send files in CSV format.Would it lead to erroring out of many records?

I'm not familiar with with BO reports, but CSV files should be easy enough to import without errors.

2)what is the best approach to transfer data from CSV to a table directly using a macro in excel?

Well this is the opposite of what I normally do (I usually import things from Excel into Access from Access itself). If you want to write a macro in Excel that imports something into an Access database you will need to create an Access Application object and a database object and work with those to import your data into a table.

 

[namliam]

2)what is the best approach to transfer data from CSV to a table directly using a macro in excel?

I agree with a lot that Karl said... but want to add... WHY create a macro in excel if you can do it directly in Access??

 

[rahul26]

I agree with a lot that Karl said... but want to add... WHY create a macro in excel if you can do it directly in Access??

The requirement is such that we have to have a button in excel , clicking on which should do the import of data from CSV to MSAccess....

Does it overwrite the data currently existing in table.
Plz give some sample code for the same.

 

[namliam]

The requirement is such that we have to have a button in excel , clicking on which should do the import of data from CSV to MSAccess....

Does it overwrite the data currently existing in table.
Plz give some sample code for the same.

*ugh*
1) What PUTS makes this requirement?? B.S. You want to do something in Access, do it in Access not in another way.
2) Lookup the TransferText in the access help to find how things work (in access!)
3) Doing it in Excel is making it to complicated!

 

[rahul26]

Hmm...got your point.
Could you please let me know how to do it in access.
have got some experience in Excel macro, but have got no idea about access.

By the way the requirement is as follows:-
We have got a data extract screen in excel where user extracts various forms of data.
Most of the data that is extracted is from excel files places in LAN which is updated daily.
But for a particular data, data was being imported from BO reports which will now be chnaged to CSV files.That is why we have to implement it in excel only ...making my life difficult...
Thanks for ur replies...........

 

[namliam]

Hmm...got your point.
Could you please let me know how to do it in access.

Type DoCmd.transfertext in a module and Hit F1... Read the help