By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Importing 340 columns into Access 2010. "How?"

P: 72
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
Attached Files
File Type: zip Testfile.zip (1.64 MB, 257 views)
Dec 6 '11 #1
Share this Question
Share on Google+
16 Replies


patjones
Expert 100+
P: 931
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).
Dec 6 '11 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Dec 6 '11 #3

100+
P: 759
Or, if this is only time you need to perform that task, manage it in Excel by removing unnecessary columns. Then import into Access.
Dec 7 '11 #4

Expert 100+
P: 446
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
Attached Files
File Type: zip TestDB2.zip (29.4 KB, 559 views)
Dec 7 '11 #5

P: 72
Thanks everyone for the great advice. This gives me a couple of options.
Dec 7 '11 #6

P: 72
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

Expand|Select|Wrap|Line Numbers
  1. Public Sub ImportCSVUsingExcel()
  2.  
  3.     Dim app As Object 'Excel.Application
  4.     Set app = CreateObject("Excel.Application") 'New Excel.Application
  5.     app.Visible = True  'Make the Excel app visible (you really don't have to do this)
  6.  
  7.     'Open your text file and point the workbook that Excel will create to hold the contents of the file.
  8.     'Note that there are multiple arguments for .OpenText that can define the 'shape' of your text file,
  9.     'for example you can indicate of your text has dbl-quotes around it or not.
  10.     'I would advise looking at the Excel help for all the argument definitions.
  11.     app.Workbooks.OpenText "D:\Documents and Settings\clvlasa\My Documents\Testfile.csv"
  12.     Dim wb As Object 'Excel.Workbook
  13.     Set wb = app.Workbooks(app.Workbooks.Count)
  14.  
  15.     'Point to the sheet (tab) that is created when you open the CSV
  16.     Dim ws As Object 'Excel.Worksheet
  17.     Set ws = wb.Sheets(1)
  18.  
  19.     'Assume a header row exists and loop through each row and save the columns you want into
  20.     'existing table. Note that this code assumes the first column of the data will never be
  21.     'a blank value.  When the first column is blank, the end of data is assumed.
  22.     Dim rst As DAO.Recordset
  23.     Dim lngRow As Long
  24.     Dim lngColumn As Long
  25.  
  26.     'Intialize
  27.     lngRow = 2 'Assume column headers
  28.     CurrentDb.Execute "DELETE FROM someTable", dbFailOnError 'Assume you want to delete existing rows
  29.     Set rst = CurrentDb.OpenRecordset("SELECT * FROM someTable WHERE 1=0") 'Open the table for data entry
  30.  
  31.     'Loop the worksheet
  32.     With ws
  33.         Do Until .Cells(lngRow, 1) & "" = ""
  34.  
  35.             'Add the record using the appropriate columns of the spreadsheet/csv
  36.             'note that you may need to coerce the datatype {CLng(), CStr(), CDate()} from the cell values
  37.             'in order to write correctly into the recordset.
  38.             rst.AddNew
  39.             rst.Fields("Field1") = .Cells(lngRow, 1) 'Get the value from column 1 into Field1
  40.             '... etc ... etc ... etc ...
  41.             rst.Fields("Field2") = .Cells(lngRow, 300) 'Get the value from column 300 into Field2
  42.             rst.Update
  43.  
  44.             'Point to the next row
  45.             lngRow = lngRow + 1
  46.  
  47.         Loop
  48.     End With
  49.  
  50.     'Clean up
  51.     Set ws = Nothing
  52.     wb.Close
  53.     Set wb = Nothing
  54.     app.Quit
  55.     Set app = Nothing
  56.     rst.Close
  57.  
  58. End Sub
  59.  
  60.  
  61.  
Dec 13 '11 #7

patjones
Expert 100+
P: 931
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
Dec 13 '11 #8

P: 72
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?
Dec 13 '11 #9

patjones
Expert 100+
P: 931
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
Dec 13 '11 #10

P: 72
Thanks Pat, I'll try changed the field1 name
Dec 13 '11 #11

Expert 100+
P: 446
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
Dec 13 '11 #12

patjones
Expert 100+
P: 931
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.
Dec 13 '11 #13

P: 72
I'v never done a TransferSpreadsheet( ) before, Do you know of a sight that would give me detailed instructions
Dec 13 '11 #14

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Dec 13 '11 #15

Expert 100+
P: 446
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
Dec 14 '11 #16

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 16 '11 #17

Post your reply

Sign in to post your reply or Sign up for a free account.