Excel to Access Data Transfer

[derek_watt]

Hi All,

Hopefully someone can help me a bit with this query. I'm currently working on an EXCEL VBA macro which will:

1. import a text file into EXCEL
2. trim all cells (the text file has white spaces around all text strings)
3. reformat column headers
4. remove blank rows (blank rows appear every ~10,000 rows)
5. edit rows where a delimiter was used in a text string - the semi-colon is my delimiter, sometimes this can appear in the middle of a text string such as a customer name, in this case, for that particular row item, instead of having 61 columns of data I have 62 columns of data
6. export the EXCEL file to an access database

I am able to do all of the above, however my issue is that the text file I am working with is huge, ~130,000 lines of data and so the above VBA macro takes 8 minutes or so to complete. The biggest chunk of time is spent on exporting data to ACCESS.

Currently I export to ACCESS row by row and field by field using the ADODB.Connection and ADODB.Recordset method, i.e.
1a. open a connection to the database
2a. open the table
3a. for each row in the EXCEL spreadsheet add 61 data fields to a new record in the database table

My question (finally !!!) is whether or not there is a faster method to transfer data from EXCEL to ACCESS, i.e. using the transfer.spreadsheet ACCESS function? Some of you will probably point out that it would be faster to just import the data into ACCESS directly from the original text file, but then how would I complete steps 2-5 above?

Thanks for your help and time with this folks.

Cheers,
Derek

 

[Ruddles]

Hi and welcome to the board!

Have you considered running the code in Access and reading the text file directly into a table, bypassing the Excel step completely? You can do all the data manipulation as you go in the VBA.