Importing 340 columns into Access 2010. "How?"

[Dave Smith]

I receive a "Data Dump" that comes to me in a csv file.

Once I open and save it in excel there are about 18000records.

The header row has 340 columns.

This will be a weekly process, I'd like to automate
Needing to import the excel table into access.

Thoughts:

Does anyone have code that will reach out from access to my excel file and import just certain columns maybe with a TransferSpreadsheet? (Not sure how to do this)

Is there a way I can save my csv or excel file as a ODBC, then do a pass-through query to retrieve the columns I need. (Not sure how to do this)

You're thoughts please.

Thanks again everyoneI also this time attached a like to a blank database and the csv file I'm trying to load. Thanks again for your help

[patjones]

The import option of TransferSpreadsheet will work fine. You can specify a range, but I believe that it must be a continuous range. In other words, you wouldn't be able to specify A1:D18000 and R1:V18000 simultaneously. So you could probably just do separate calls to TransferSpreadsheet.

The way that I would approach this problem, in general terms, is to create a proper relational table setup in Access, and always import the Excel file all at one time. Once the import is complete, you can use appropriately designed SQL queries to split the data off into the normalized table structure and delete the imported table.

I do understand the situation that you're in. I recently built a crude ETL (Extract, Transform, Load) database whereby the user would import an Excel file, the database performs some aggregate calculations on the data, and the results of those calculations get loaded into the permanent tables in my schema (with the temporary import table getting deleted).

[TheSmileyCoder]

First off, you can usually read the CSV file directly, and I would suggest that, instead of involving excel in the matter.

Second off, I believe access tables have a maximum of 255 fields, which would mean you either have to limit the amount of fields you wish to import, or do the import into 2 tables, by specifying a subset of the columns to import.

You can manually setup a import specification, and save it, and then reuse it in your code.

As patjones has allready specified in the end , you (may) also need to work on normalising it. That however depends on your end requirements. A quick look at the csv file tells me that it is not normalised in its current form.

[Mihail]

Or, if this is only time you need to perform that task, manage it in Excel by removing unnecessary columns. Then import into Access.

[sierra7]

Hi
I was doing something similar recently so here is some of my code (in the attached file)

Your source data must be placed in a folder c:\access

As it is CSV I opened it in Excel and saved it as a worksheet. I'm using Office 2010 so it called it an .xlsx file and the Access code checks for this. Just modify the code to .xls if that suits.

The Smiley Coder was right about the 255 fields limit so the code has two goes, reading first up to column GZ, then to LB (check I have not missed any!)

I took the liberty of including code to add an Autonumber ID field to each of the tables, so they can be joined.

I guess you will then have to run queries against this data to put it into your Normalized structure.
Have fun!
S7

[Dave Smith]

Thanks everyone for the great advice. This gives me a couple of options.

[Dave Smith]

I have a file located in my Documents titled: Testfile.csv
When I run the Module in the database it opens the file but then gave me this message "Run-time error 3265 - Item not found in this collection"

any Idea's

Public Sub ImportCSVUsingExcel()
 
    Dim app As Object 'Excel.Application
    Set app = CreateObject("Excel.Application") 'New Excel.Application
    app.Visible = True  'Make the Excel app visible (you really don't have to do this)
 
    'Open your text file and point the workbook that Excel will create to hold the contents of the file.
    'Note that there are multiple arguments for .OpenText that can define the 'shape' of your text file,
    'for example you can indicate of your text has dbl-quotes around it or not.
    'I would advise looking at the Excel help for all the argument definitions.
    app.Workbooks.OpenText "D:\Documents and Settings\clvlasa\My Documents\Testfile.csv"
    Dim wb As Object 'Excel.Workbook
    Set wb = app.Workbooks(app.Workbooks.Count)
 
    'Point to the sheet (tab) that is created when you open the CSV
    Dim ws As Object 'Excel.Worksheet
    Set ws = wb.Sheets(1)
 
    'Assume a header row exists and loop through each row and save the columns you want into
    'existing table. Note that this code assumes the first column of the data will never be
    'a blank value.  When the first column is blank, the end of data is assumed.
    Dim rst As DAO.Recordset
    Dim lngRow As Long
    Dim lngColumn As Long
 
    'Intialize
    lngRow = 2 'Assume column headers
    CurrentDb.Execute "DELETE FROM someTable", dbFailOnError 'Assume you want to delete existing rows
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM someTable WHERE 1=0") 'Open the table for data entry
 
    'Loop the worksheet
    With ws
        Do Until .Cells(lngRow, 1) & "" = ""
 
            'Add the record using the appropriate columns of the spreadsheet/csv
            'note that you may need to coerce the datatype {CLng(), CStr(), CDate()} from the cell values
            'in order to write correctly into the recordset.
            rst.AddNew
            rst.Fields("Field1") = .Cells(lngRow, 1) 'Get the value from column 1 into Field1
            '... etc ... etc ... etc ...
            rst.Fields("Field2") = .Cells(lngRow, 300) 'Get the value from column 300 into Field2
            rst.Update
 
            'Point to the next row
            lngRow = lngRow + 1
 
        Loop
    End With
 
    'Clean up
    Set ws = Nothing
    wb.Close
    Set wb = Nothing
    app.Quit
    Set app = Nothing
    rst.Close
 
End Sub
 
 
 

[patjones]

You may want to start a new thread on this problem, as it is not entirely contiguous with the previous discussion. I'll let the moderators make that call. Anyway...

Do you know what line it's stopping on? Also, is "someTable" the name of a table in your database, and are "Field1" and "Field2" actually the names of columns in that table?

Pat

[Dave Smith]

When I hit debug, this row was highlighted

rst.Fields("Field1") = .Cells(lngRow, 1) 'Get the value from column 1 into Field1

No I dont have a column with those names, do I need to change then to one that I have?

[patjones]

When you use recordsets in this manner, yes. You need to make sure that you are binding the recordset to a table that exists in your database, and that you are referencing column names in that table. VBA is complaining that it cannot find "Field1" in your table.

This looks like third-party code to me. Be aware that such code usually requires modification to fit the parameters of your database. It also may not adhere to best practices.

Pat

[Dave Smith]

Thanks Pat, I'll try changed the field1 name

[sierra7]

Dave
Do you understand the code you are using?

Each statement (e.g. that for Field1) represents copying a single cell from a row (called lngRow) into your table. As you are going to have 340 columns you will need 340 such statements! (hence the 'etc., etc, at Line 40) Instead of "Field2" at Line 41 it should be "Field300" to be consistant.

We have already confirmed that you cannot have more than 255 columns in an Access table, so unless you are selectively importing certain columns, this code will not do your job.

It will also be incredibly slow. Unless you only want to import something like every alternate column, instead of blocks of columns, the I would recomend the 'Block' approach used in the sample database I sent last time.
S7

[patjones]

Yes, what sierra is saying is 100% true, a point that escaped me when I saw the "etc. etc." comment line.

I have to say that either utilizing TransferSpreadsheet( ), as I mentioned earlier in the thread, or setting up an import specification as Smiley noted, would be the way for you to go. The third-party code that you have here is clunky and will not perform well.

[Dave Smith]

I'v never done a TransferSpreadsheet( ) before, Do you know of a sight that would give me detailed instructions

[patjones]

It's a fairly straightforward function call. For instance, to import a spreadsheet called "test.xlsx" located in your My Documents folder into a table "tblTest" you'd do:

DoCmd.TransferSpreadsheet(acImport, , "tblTest", "C:\My Documents\test.xlsx", True)

You can look at the Access help page on it for more information, and look at exactly what all the options for the arguments are.

[sierra7]

Dave
I SPENT SOME TIME WRITING YOU A DEMO SYSTEM WHICH I ATTACHED A WEEK AGO, which extracted your own data.

If you has bothered to down load it all would be easy to follow.

S7

[NeoPa]

Registering to keep an eye on matters here.

@Dave
You need to consider carefully how you deal with people. People put in a lot of effort to help you and, surprisingly enough, expect a certain minimum of effort on your part in return. That would include responding to each post and at least checking everything that is posted for your assistance. I make no judgements at this stage, but I felt it important you should at least understand the situation.