Solved Importing an Excel file with Duel Formats in a Column (1 Viewer)

Weekleyba

Registered User.
Local time
Today, 11:27
Joined
Oct 10, 2013
Messages
455
I'm importing a Excel document into Access with some code.
Everything seemed to work well but, I notice I was missing some data between the Excel file and the table in Access.
The problem is that one of the columns (Budget Activity Program) in the Excel file has data that is both numbers and text.
When Access imports the spreadsheet, it imports the Budge Activity Program column as a number format.
When it does this, it deletes the entries that are a combination of characters that are both numbers and letters. See below.

If I select the Budget column in the Excel file and change the format from "general" to "text", then it imports just fine.
I would rather not have to do that step every time or rely on someone else to have to remember that step.

Is there some code that I could include to do this for me? See my code to import the file below.
Or possible another approach to prevent this entirely?

Below is the Excel file and Access Table after import that illustrates the problem.

1582820771383.png
………...
1582820640514.png


Here is the VBA code I use to import the Excel file.

Code:
Private Sub Command12_Click()

    Dim Msg1, Response1, Msg2, Response2, Style, Style2, Title, fName
    Msg1 = "Are you sure you want to IMPORT the Allowance Status Report?"
    Style1 = vbYesNo + vbCritical + vbDefaultButton2
    Title = "IMPORT"

    Response1 = MsgBox(Msg1, Style1, Title)
    If Response1 = vbNo Then
        Exit Sub
    ElseIf Response1 = vbYes Then
 
    
    On Error GoTo SubError
      
    DoCmd.SetWarnings False
        
    fName = "C:\Users\brweekley\Documents\Databases\Funding_Database\IHS - Allowance Status by Project and Location.xlsx"
  
    DoCmd.OpenQuery "Delete_IHS - Allowance Status by Project and Location"
  
    ' True means the spreadsheet has column names for the first row
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "IHS - Allowance Status by Project and Location", _
            fName, True
    
    Msg2 = "Worksheet imported!"
    Style2 = vbOKOnly + vbInformation
    'Title = "IMPORT"
    Response2 = MsgBox(Msg2, Style2, Title)
  
    DoCmd.OpenQuery "UpdateImportTable_Q"
    DoCmd.RefreshRecord
  
    DoCmd.SetWarnings True
    
  
SubExit:
On Error Resume Next

    Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & " = " & Err.Description, vbCritical + vbOKOnly, _
       "An error occurred."
    GoTo SubExit
    
End If

End Sub
 

Attachments

  • 1582819536928.png
    1582819536928.png
    17.3 KB · Views: 208
  • 1582819611467.png
    1582819611467.png
    20 KB · Views: 209
  • 1582820725960.png
    1582820725960.png
    12.1 KB · Views: 126

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
17,154
Hi. Assuming you are importing the Excel data into an existing table, what is the data type of the Budget field?
 

ebs17

Member
Local time
Today, 18:27
Joined
Feb 7, 2020
Messages
71
The easy way:
Use the method DoCmd.RunSavedImportExport ... instead of DoCmd.TransferSpreadsheet ...

You will receive a saved import if you run the import once via the menu and save it. There you can define in the advanced settings how Excel columns are to be read, i.e. as text.

Eberhard
 

Weekleyba

Registered User.
Local time
Today, 11:27
Joined
Oct 10, 2013
Messages
455
DBguy - The data type of the Budget field is Short Text.
Eberhard - I check out the docmd.runsaveimportexport. Haven't used that yet.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
17,154
DBguy - The data type of the Budget field is Short Text.
Eberhard - I check out the docmd.runsaveimportexport. Haven't used that yet.
Hmm, so if it's Short Text, I guess I don't understand why only numbers would come through. I'll have to do some tests. Are you able to share your db and Excel files?
 

Weekleyba

Registered User.
Local time
Today, 11:27
Joined
Oct 10, 2013
Messages
455
DBguy
Attached is the Database and the Excel file.
Thanks for taking a look.
I noticed that when I first import the file, the Budget field data type is Number.
If I change the Excel column format to Text, then import it, the Access table data type for Budget is now Short Text.

If I change the Budget field from Number to Short Text and then import again, it still deletes the cells with alpha characters.
In the db attached, you'll find the Budget data type to be Number, because that is set when the Excel file is first imported.
Changing it after it is imported does nothing.

I'm stumped.....
 

Attachments

  • DFM Funding Database 11.zip
    359.7 KB · Views: 74
  • IHS - Allowance Status by Project and Location.zip
    984 KB · Views: 209

ebs17

Member
Local time
Today, 18:27
Joined
Feb 7, 2020
Messages
71
The problem:
Excel knows no data types per column, only formats per cells. Jet-SQL, and TransferSpreadsheet uses Jet-SQL internally, needs data types for reading.

Therefore, according to the registry entry TypeGuessRows, the contents of the first lines are interpreted, usually the first 8 lines. The data type is then determined. If numbers come first and later text, the text can no longer be recorded. However, an error table is generated.
This called interpretation occurs before you can intervene yourself.

Ways:
1) Change registry entry
2) Export worksheet as CSV and import this CSV. For text files, you can specify how the columns are to be read using the import specification.
3) Saved imports since Access 2007. In this you can also create import specifications, also for Excel. However, these are stored in the XML depths of the database and are not quite so easily accessible.

Eberhard
 
Last edited:

Weekleyba

Registered User.
Local time
Today, 11:27
Joined
Oct 10, 2013
Messages
455
Eberhard
Thanks for the help. It took me long enough, but I got to work.
Once I figured out that I needed to first save an Import, then it all came together.
This is a great tool in the tool bag to have!

Just used this code in the procedure:
Code:
    ' This runs the saved Import under the External Data tab where I saved the data type for the BAP column to text.
        DoCmd.RunSavedImportExport "Import-IHS - Allowance Status by Project and Location"

Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:27
Joined
Oct 29, 2018
Messages
17,154
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom