Importing From Excel problem

[OC2]

I have an Excel spreadsheet from an external source that causes errors when importing into an Access database.
The problem states that some of the columns have a type conversion Failure.

The problem is the columns that are being affected are identical to some of the other columns being imported ie,
1 column has numbers and text, but wont import the text, and the other column also has number and text yet imports them both.

The reason why I stated it was from an external source is because im not sure whether the person did some copy and pasting using 2 different file formats although appear identical on the Excel spreadsheet.

My question is, how can i convert the column that wont import properly to the same text format as the column that does import. I have tried just about everything, ie same allignment same font same size, same cell format etc, it seems as though the problem is hidden somewhere else.

Can anyone help

thx

 

[B827]

This is probably being caused by Access reading the mixed format columns as numeric and failing when it gets to a text entry. This usually happens when the first few (I don't know how many) rows the field contain purely numeric data. I have never really got a satisfactory solution to this problem ohter than loading the data in with the failures, changing the design of the table then importing to the formatted table having first deleted all the data. If you are importing from this Excel spreadsheet regularly then this is worth doing. If you import from different spreadsheets all the time then making sure the mixed text and numeric fields have at least one text entry in the first few rows seems to solve this problem.

I'd be interested if someone has found a better solution as this causes be problems not infrequently.

 

[OC2]

Thx for highlighting that. With that info I was able to code a solution to my import command button.

All I needed to do to my import template was to add a text line below all the headings. I used the word "Text" as this word is never going to appear for the users required information,

Dim db As Database
Dim rst As Recordset
Dim crnow, strsql

crdel = " like 'Text'"

On Error GoTo On_Err
Me.status.Value = ""

If Me.Color.BackColor = 0 Then

Dim stDocName As String
Dim XLT_Location As String

XLT_Location = Me.importas


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "sea-asia", XLT_Location, True

Me.Color.BackColor = 65280
Me.status.Value = "Import complete"

strsql = "Select * from [sea-asia] Where " _
& "[Country]" & crnow & ""

Set db = CurrentDb()

Set rst = db.OpenRecordset(strsql, dbOpenDynaset)
Forms![Sea-intra-asia-import].RecordSource = strsql
Requery
DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.SetWarnings True
GoTo die
Else
Me.status.Value = "You have recently imported files. Please click OK to reset and import again or Cancel to stop operation"
Me.ok.Visible = True
Me.cancel.Visible = True
Me.focus.SetFocus


End If

On_Err:
If Err.number = 94 Then
Me.status.Value = "Invalid Location! "
End If
If Err.number = 3011 Then
Me.status.Value = "Cannot find file name"
End If
If Err.number = 2522 Then
Me.status.Value = "Pls choose a location"
End If
If Err.number = 2391 Then
Me.status.Value = "The file you are trying to import is not compatible with this template"
End If

die:
Forms![Sea-intra-asia-import].RecordSource = ""


This code imports the template into the system, searches for the row to delete using the cell "Text" as reference and deletes it

Thx again for your help

 

[OC2]

sorry

strsql = "Select * from [sea-asia] Where " _
& "[Country]" & crnow & ""


should read

strsql = "Select * from [sea-asia] Where " _
& "[Country]" & crdel & ""

 

[B827]

I've resorted to similar tatics, you could delete the dummy entries after import with a delte query with criteria of "text" in the appropriate fields.