DTS import converting string data to Null

[multiplex77]

Hi,

I'm trying to import data from an Excel 2000 spreadsheet to SQL 2000 table via DTS. But there seems to be one cell (a varchar(20), value: 1400) which always ends up as a NULL in the SQL2K table.

I've tried Format Cell and changing that cell format to Text, but it still remains as NULL on import. But if I add a letter, eg "1400A", to the cell value in the Excel file, it imports fine. Once I delete the "A", it imports as NULL again. SO it seems it can't import numbers, but can import alphanumerics. But all the other rows (which contain numbers) import fine.

What can I do to resolve this? I have to use the Excel file cos I've already given it to my client as an intermediate document.

I tried converting the Excel file to a Tab delimited .txt file, but somehow DTS can't seem to read the Tab delimited .txt properly - the columns don't line up properly.

Thanks for the help.

 

[tb]

How do you do the import within the DTS package?



PS. If you are doing it with ActiveX Script and reading the excel sheet row for row - then try to convert that value to text first cstr() function

The pen is mightier than the sword - and quite frankly . . easier to write with

 

[multiplex77]

Thanks for your reply. Yes, I'm using an ActiveX script. I tried using CStr() but it still ends up as NULL. It's only that record. All the others import fine.

 

[tb]

Let's try this . . .

From MSDN - on Format
'If format is not supplied, a string is returned.
MyStr = Format(23) ' Returns "23".

If this doesn't work try to combine it with the cstr() function.

I know this sounds silly - but it's worth a shot!



The pen is mightier than the sword - and quite frankly . . easier to write with

 

[multiplex77]

Sorry, I don't quite understand. What that Format() do, and why 23?

Thanks.

 

[tb]

If you use . . . Format(now,"YYYY/MM/DD&quot it will do the format to what you specified ie "2003/08/02"

If you use Format(23) or in your case Format(1400) - or according to the MSDN help file it should return a string value of "1400" and not a numeric value 1400.

If this does not work - try the following . . .
Thus try to force a string value . . . any try to least

cstr(format(1400))

As I said before - it's worth a try.

Hope this works!

The pen is mightier than the sword - and quite frankly . . easier to write with

 

[multiplex77]

Hi tb

Thanks for the help. I tried what you suggested, but I get:
Type mismatch 'Format'.

If I have a "Number" cell format in Excel, and I only enter numbers, it imports fine into MSSQL.

If I have a "Text" cell in Excel, and I enter a combo of numbers AND letters, it also imports fine.

I've found out it is a problem only when I have a "Text" field in Excel in which I load only Numbers. However I need to keep it this way cos I don't know what format the data for this field will be in.

Thanks.

 

[Raparla]

multiplex77,

Any luck on this problem. I also end up with the same problem.

ThanX