Import over 255 to Access 2010

[TJVFree]

I’ve been on the internet now for a couple hours with no success.

I’m using MS Office 2010

I have a excel spread sheet with around 18,000 rows, and 16,475 columns
The 16,475 columns always are the same. So what I’m trying to find is if there is a way I can import this spread sheet into access. Understanding that access has a 255 limitation.

I’m wondering if there is a way I can have access go out to the spread sheet and grab column” A, B , C, D, Z, AE, AZ, etc.,”<-as a example

Then go back and grab more columns until I have all the columns in different access table from columns that were in excel

I appreciate any help

 

[TJVFree]

Skip and Majp,

You both have helped me out in the past a lot and I take your advice as that’s the way to do it.

MajP,
Can you help me get started on how I would write code in excel to normalize my data.

Skip,
What I would do once I have it in access is delete some columns and sum some rows.

I get this message when using the code below:

Error message
This operation will fail because the text file you are about to import contains more than 255 columns. We recommend that you first make a backup copy of your source find, reduce the number of columns to 255 or less, and try again

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

 

[PHV]

delete some columns
Do it in excel before importing in access.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

Corey,
I think using skips idea and the macro recorder you probably can get a lot of this done.

The trick is to bite this off into parts. You need to start with the end in mind. That is figure out what your tables are going to look like. Then figure out how to format the data so it can be imported into Excel.

I do not understand all the fields and the relations. But I see a couple of tables. In my mind the code would create several sheets (normalized tables) ready for direct input into access.

tblLoanData
loan Primary key
specific data about a loan (this looks like the first 4 fields)

tblCredit
creditId
creditDescription
creditAmount
loanID_FK (relates back to a loan)

example
LENDER TOLERANCE ADJUSTMENT 0.01 88383383
USBHM HELOC 30000 88383383

etc.

tblChargeType
looks like you have certain groups of charges ("Document fee") and need to know if that type of charge is required. These types then have specific charges "DocPrepFeeBuyerChhrge"
. chargeTypeID
ChargeTypeRequired
SomethingCalledFamily
loanID_fk

tblCharges
chargeID
chargeType_FK
chargeAmount
other charge fields


This table would replace columns S-AC with 4 columns, and reduce the rows from 18,000 + to maybe a couple hundred.

There are a whole bunch of other types of charges. Some types might require their own table, but maybe a lot can go into a charges table.

If you can propose the tables (we can help solidify) then getting into a normalized format will not be simple, but not overly hard.
There definitely appears a structure that can be easily normalized.

 

[TJVFree]

Thanks MajP,

That’s exactly what I was looking for. Great advice again.

Let me get back with you once I go through the steps and find out what my end result will be.

Thanks again
Corey

 

[MajP]

As I said I do not know enough of the buisness model to say exactly how to normalize the table. I can see that my first assumption was wrong. I assumed each row contained information about a loan and related charges. However, I can see that there are multiple rows per loan. So there is some other entity that appears uniquely identified by compababilityHookNumber.

 

[MichaelRed]

Do it by "{importing" the original csv file as a simple text object (Look here for a routine "GrabFile"?) there should be an example whivh then shows a simplistic routine to parse the file to individual lines and (CSV) delimited fields. Since you appear to already know the structure of the file, decide which fields go into each data set, so looping through a record you could simply assign the contents to an array to be imported via your query.



MichaelRed