Import Problem with Excel File 1

[Bonediggler1]

Hello -

I have a table with field "Invoice" set to data type text. The corresponding field "Invoice" in the Excel file to be imported has data that follows the following pattern:

Either
1) 1234567
Or
2) 1234H567-0

Now, if the data in the Excel file is sorted such that the numeric values in the Invoice field are at the top i.e. type 1) above, there are errors while importing as I assume Access determines it is importing a numeric field, and it then kicks out type 2) invoices.

But this shouldn't happen as the Access Invoice field is set to TEXT (???)

When the Excel file is sorted such that invoices of data type 2) are at the top everything is fine. I am wondering why Access is not recognizing that I set Invoice to data type Text and it should behave accordingly when importing...


Thank you!!

 

[SkipVought]

hi,

BIG problem!

You have BOTH TEXT and NUMERIC values in the same column in Excel. NOT A VERY SMART THING!

You must CONVERT your numbers in Excel to TEXT. Formatting the column will do ABSOLUTELY NOTHING.

Run a procedure like this one that uses any cell selection in the column of interest to convert the entire column of data to TEXT...

sub Num2Txt()
'select any cell in the column of data you want to convert
  dim r as range
  with selection
    for each r in intersect(.parent.usedrange, .entirecolumn)
       if isnumeric(r.value) then
          r.value = "'" & r.value
       end if
    next
  end with  
end sub

Running this procedure will convert any numeric value to text.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bonediggler1]

Thanks SkipVought

Unfortunately I don't have any control over the source file.

While I appreciate the solution, I still don't understand why importing a mixed-data-type Excel column into an Access field of type text doesn't work...

 

[SkipVought]

http://support.microsoft.com/kb/141284

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}