insert xls/csv file into access 2007 table using vba

Question

I have several excel files carrying 90 columns and about 500 rows, each file has a header of 4 rows, and I need to insert them into one access table.

I am able to do the import by using the for...loop to loop all the excel files and all of the rows inside the file, but this is too slow.

I have used mysql in R and it can be done easily using "LOAD DATA LOCAL INFILE". I can convert the xls file into csv file without problem, but I can't find anything similar in Access as "LOAD DATA LOCAL INFILE".

Any suggestions for optimizing the import process? Thanks!

Answer 1

I guess you are looking for TransferSpreadsheet. You can import data or link it.

Answer 2

http://msdn.microsoft.com/en-us/library/aa141565%28office.10%29.aspx

As above, this is what you're looking for.

If the 4 lines of header is a problem, you can run create the table with correct headers, run a delete query, delete everything in the table, then import without headers into the already existing table. The below code is what I use to get rid of the first line, you can simply edit it to remove 4 lines. This is used in Excel VBA. In your Access module, simply set Excel as an object and run the below.

Header code:

Application.DisplayAlerts = True

    Workbooks.Open Filename:= _
        "Excel File here"
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.Save
    ActiveWorkbook.Close

RE: your transferspreadsheet not working, why is your table already created? I am using the code as follows and it is working correctly:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TABLENAME", "IMPORT PATH", False

Can you let me know any errors/anything you dont understand, I will try to help further.The false at the end of the above code is to make sure you are NOT using headers. If you would like to import a header row, change that to True.