Can I import .csv file in Access from Excel VBA

[SachAccess]

Hi,
I do not know if this can be done.
I have a .CSV file. I am writing a Excel macro to process this CSV file.
Files size is approx. 500,000 rows of data.
But the required data for processing is not more than 10,000.
So out of 500,000 data only 10,000 data is required for further processing in Excel.
Is it possible that I will import CSV file in Access from Excel VBA.
Filter on required data in Access and export to Excel.
I will be able to filter required data in Access but not sure how to import CSV file in Access using Excel.
Can anyone please help me in this.

 

[arnelgp]

just use Access and import it there and do whatever you like.
export it back.
why make it complicated.

 

[stardustvega]

Hello, it sounds like you want to use Access to filter down the data, right? Since doing a macro in Excel will take so long to run on such a big dataset.

You can easily import to Access by going to External Data > New Data Source > From File > Text File.

Filter the data using a query and save the query.

Then export the results of the query by going to External Data (Under the Exports section of the ribbon) > Excel OR External Data (Under the Exports section of the ribbon) > Excel > Text File and changing the file extension to CSV.

 

[stardustvega]

This video walks you through how to import records: How to Import a CSV. This one shows you how to export records: How to Export a CSV.

 

[Pat Hartman]

One of my consulting assignments was to work with a group of analysts who were working with Excel and teach them how to use Access either for the entire analyses or at least for preprocessing. They were working with million row tables and so everything took a long time. Just replacing their vLookups saved each of them TWO HOURS every day.

 

[stardustvega]

One of my consulting assignments was to work with a group of analysts who were working with Excel and teach them how to use Access either for the entire analyses or at least for preprocessing. They were working with million row tables and so everything took a long time. Just replacing their vLookups saved each of them TWO HOURS every day.

You'd think by the time they had like...20,000 rows someone would have been like "There has to be a better way than this." But the power of inertia is crazy!

 

[Pat Hartman]

They used to think of it as breakfast time. They'd come in, download the new file and start up the macro. Then go have breakfast

 

[stardustvega]

Ah, yeah I can understand that...I'm stuck with this frustrating dinosaur of a computer at work so I've started doing neck stretches whenever I'm sitting and waiting for a really big PDF to be sized down. (Can't do anything else while waiting or it hangs up the PC.)

 

[SachAccess]

T

just use Access and import it there and do whatever you like.
export it back.
why make it complicated.

Thanks. I agree with you.

 

[SachAccess]

This video walks you through how to import records: How to Import a CSV. This one shows you how to export records: How to Export a CSV.

Thanks for the help.

 

[isladogs]

Another approach.
1. LINK the CSV file rather than import it to avoid unnecessary bloating of your Access file
2. Process and export as above

 

[CJ_London]

and another, use a sql query instead of a linked table - using DAO in excel, you should be able to just import the records you want


SELECT * FROM [TEXT;DATABASE=C:\pathtofile;HDR=Yes].filename.csv WHERE somefield=somevalue

 

[SachAccess]

Another approach.
1. LINK the CSV file rather than import it to avoid unnecessary bloating of your Access file
2. Process and export as above

Thanks a lot.

 

[SachAccess]

and another, use a sql query instead of a linked table - using DAO in excel, you should be able to just import the records you want


SELECT * FROM [TEXT;DATABASE=C:\pathtofile;HDR=Yes].filename.csv WHERE somefield=somevalue

Thanks a lot.