Importing data from Excel not working right

[nsanto17]

I am running SQL 7.0 and i am using DTS to import data into a table inside excel.

All data seems to be getting imported except 1 column.

In Excel the one column can contain the following values (field is formated as text in excel) SQL Datatype is Varchar (6)

3091
1591
3091 S
1591 S
0301
1291
<Null>

When i import the data it appears that everything goes ok except that 3091 & 1591 does not get imported.

Does anyone have any ideas.

Thanks in advance.

Nick

 

[SkipVought]

Hi,

More than likely, the import manager scans the first 6-8 rows and decides that you are importing NUMERIC data. COnsequently, the cells containing TEXT (the two you are missing) are never 'seen.'

You must CHANGE the data in Excel by making all the numeric values text. Merely changing the column numeric FORMAT dpes absolutely NOTHING to the underlying data.

I'd prefix with an APOSTROPHY character...

Sub Num2Txt()
  dim r as range
'select in the column you want to FIX
  with selection
    for each r in range(.end(xlup), .end(xldown))
       with r
         if IsNumeric(.value) then .value = "'" & .value
       end with
    next
  end with
end sub

Save and reimport.

Skip,

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

 

[whateveragain]

Strange. Worked once I rebooted my machine. Thanks for your help.