Force Data Type on import - Access 2003

Or perhaps run some code like this in Excel to:
Save the workbook
Copy the active Worksheet
Format all cells in the copied sheet to Text
Save the Workbook again.
Then import this Copied sheet to Access.

    ActiveWorkbook.Save
    ActiveSheet.Copy Before:=Sheets(1)
    Cells.Select
    Selection.NumberFormat = "@"
    ActiveWorkbook.Save

JeffCoachman

Hi,

I would create an import specification to be used whe importing your data. The best way to achiev this is to start to manually import your data, then when you have the Import Text Wizard up select the advanced tab, specify your criteria (eg all txt fields) then save the import spec. Then using your vba import your data maing refernce to your import spec.

The example below imports data from a txt file called "INO28783_AIRN*.txt" usinf an import specification called "ImportSpec_PMKeyS_INO28783_AIRN"

Good luck

Kev

'Import new data set
    strFile = Dir(varPMKeySFilePath & "INO28783_AIRN*.txt", vbNormal)
    Do While strFile <> ""
        If strFile <> "." And strFile <> ".." Then
        DoCmd.TransferText acImportDelim, "ImportSpec_PMKeyS_INO28783_AIRN", "tblPMKeyS_AIRN", varPMKeySFilePath & strFile, False, ""
        End If
        strFile = Dir()
    Loop

                                              

1:
2:
3:
4:
5:
6:
7:
8:

Here's the other catch. There are no preset fields in the spreadsheet. They will vary from file to file. Here's a little more info on what I'm using this for. I built a database to clean addresses for form letters. All files will contain a name, 1 or 2 address fileds, city, state, and zipcode, but may or may not have additional fields, and there's no standardization to naming conventions. Once the user imports the file, there are drop down boxes to match up fields, for instance there's a drop down for zip code, and they can select the corresponding field in their data (zip, zip_code, zipcode, etc.). The problem is that if their data has the first 100 rows of data with just a 5 digit zip code and then after that has a 9 digit, then it will throw those out because access has assigned it to a numeric field, so 55555-1234 errors out.

Also, any coding must be within my database. This database will be used to process data for many different spreadsheets by several users of varying skill levels. Coding in the excel spreadsheet isn't an option.

Nightmare!  Can you not give all users the same spreadsheet, with a uniform set of fields?  Otherwise you are going to have to process each one separately.

That's the problem. The origonal data is coming from other people and varying originating locations, so we have no control as to what format it arrives in.

In which case, in your position, I think I should consider renaming the relevant columns in the incoming spreadsheets to conform with your import table, rather than trying to cover all possible eventualities in Access.  

Having done that, I would first import them into a temporary Access table.  Then append from the temporary table to your main import table, using a query which only picks out the fields you need.

I have done this where we had spreadsheets of audiometry tests coming in from all sorts of places in all sorts of formats, and it seemed to be the most time-effective solution.

budorat,

The asker is bringing in Excel files, which, AFAIK, do not have Import Specs...

;-)

Jeff

<Also, any coding must be within my database. This database will be used to process data for many different spreadsheets by several users of varying skill levels. Coding in the excel spreadsheet isn't an option.>

You can run code from Access to format an Excel sheet

It will basically be my code but called from Access and the specific file will be accessed.

Ok here's my thought:
1. Import the Excel into my table which will create all needed columns
2. Delete the data from the table
3. Change the datatypes of all columns to text
4. Reimport the data into the updated table

It's not pretty, but I think it will work. My only concern is that it will throw a type mismatch error on the second import. Not sure if it will or not till I do it.

Boag - You have an interesting concept of using code within my database to modify the spreadsheet being imported. If I could do that then I could change the datatype before it is imported which would be Ideal. How would I do that? Would I have to know the column names or # of columns ahead of time, or could I handle that on the fly?

Ok, so my thought didn't work. When I changed the field to text and then tried to reimport, it kicked out the ones that were stored as numeric...

Give me a few hours...

In the audiometry db mentioned above at 25570683, I had a procedure whereby the user picks the name of the incoming spreadsheet from a common dialog.  It is then checked to see if it has column headings corresponding to the names in our import table (regardless of whatever other columns it contains).  If so, the relevant fields are imported, using the routine I have outlined.  If not, a message is put up, telling the user the spreadsheet needs to by manually inspected before import.

UPDATE: Not behaving as I thought. Access 2003 is only looking at the first 25 or so records. If all of the 1st 25 are the same datatype, it treats the entire column as a single datatype. If there are mixed datatypes in the first 25 records, then it will treat them all individually. This still doesn't help me unless I can figure out a way to add 2 rows to lines 2 & 3 of any spreadsheet that I'm importing.

A different approach... I found that if I import the spreadsheet without identifying the first row as headers, then it makes that row data, and forces text imports and all my data comes in fine. Is there a way to then programatically change the field names to whatever value is in the first row of data and then delete the row?

Not as long as the Number of fields can vary from table to table, AFAICT.

But let's see what the other experts say...

Ok, so you are not interested in my suggestion from here:
http:#a25566692
Because this works fine for me once I modify it to work from Access.

JeffCoachman

Boag: I'm open to anything that will work. How do I call that from my database? I'm using the docmd.transferspreadsheet to import it. I've never tried to run code for excel before, much less in access to modify an excel doc. how would that work?

Just run this modified code in your Database:

'Create the Variables and Objects
Dim appXL As Excel.Application
Dim wrkbkBook As Workbooks
   
'Set the variables and Objects.
Set appXL = New Excel.Application
Set wrkbkBook = appXL.Workbooks

    ' Open the Excel file.
    wrkbkBook.Open "C:\YourFolder\YourExcelFile.xls"
    ' Show the Excel Application.
    appXL.Visible = False
   
    'Copy the sheet
    ActiveSheet.Copy Before:=Sheets(1)
    'Select all the cells in the copied sheet
    Cells.Select
    'Format all cells to Text
    Selection.NumberFormat = "@"
    'Save the Excel File
    ActiveWorkbook.Save
   
    'Close the file.
    wrkbkBook.Close
    'Quit Excel.
    appXL.Quit

'Close the object references.
Set wrkbkBook = Nothing
Set appXL = Nothing
'-------------------------------------------------

You can adjust: " "C:\YourFolder\YourExcelFile.xls"
... for your specific Excel file and path.

Then simply import this copied sheet to Access.

Obviously you would want to run this on a test database and a test Excel file to test and tweak it.

You can see the code is commented, so you can see what is happening.

;-)

JeffCoachman

I'm getting a "User Defined Data type not defined" error on

Set appXL = New Excel.Application

Oh,

Sorry!

In your Access VBA Editor  you need to add a reference to the:
"Microsoft Access Excel Object Library"

Open the VBA Editor in Access and Click: Tools-->References.
The check ON the box for:
"Microsoft Access Excel Object Library"

;-)

JeffCoachman

I can't find the "Microsoft Access Excel Object Library". It's not in the list. How do I get it?