Can I import .csv file in Access from Excel VBA (1 Viewer)

SachAccess

Active member
Local time
Tomorrow, 02:07
Joined
Nov 22, 2021
Messages
389
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:37
Joined
May 7, 2009
Messages
19,169
just use Access and import it there and do whatever you like.
export it back.
why make it complicated.
 

stardustvega

Member
Local time
Today, 15:37
Joined
Feb 4, 2022
Messages
36
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:37
Joined
Feb 19, 2002
Messages
42,975
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

Member
Local time
Today, 15:37
Joined
Feb 4, 2022
Messages
36
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

Super Moderator
Staff member
Local time
Today, 16:37
Joined
Feb 19, 2002
Messages
42,975
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

Member
Local time
Today, 15:37
Joined
Feb 4, 2022
Messages
36
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.)
 

isladogs

MVP / VIP
Local time
Today, 20:37
Joined
Jan 14, 2017
Messages
18,186
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

Super Moderator
Staff member
Local time
Today, 20:37
Joined
Feb 19, 2013
Messages
16,553
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

Active member
Local time
Tomorrow, 02:07
Joined
Nov 22, 2021
Messages
389
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. :)
 

Users who are viewing this thread

Top Bottom