DTS Excel Import- numeric only rows not importing

[Barneye]

Hello!


I have a spreadsheet that imports via DTS. It has a part number column that may contain both numbers and letters. If the part number is numeric only and over 6-8 digits long the field is left blank. I know this has something to do with the way Microsoft "predicts" the data type by what is in the first few rows.


Can I get around this? I may have part numbers up to 15 numeric digits mixed in with alpha numeric part numbers.

 

[AlexCuse]

What is your column's data type? If you change it to varchar(15) rather than DTS' 'guess' that may help.

Hope this helps,

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Barneye]

I have tried many different "Data Types" with no luck. This is crazy! I hate Microsoft sometime! Ugh!

 

[AlexCuse]

varchar(15) should let everything in. What is your destination table's data type currently set to for the offending column?

Alex


Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[Barneye]

I have it set for nvarchar(255).

I found some info that this is designed behavior for SQL. Microsoft suggests saving the file as a text file and then do the import. I guess I will add a step to the DTS to do the conversion to text and then do the import.

 

[Barneye]

Well having SQL output the Excel file as a csv file did not work. This is very frustrating. I guess I will have to convert the file to CSV by hand from excel and try again. If anybody has a better idea, please speak up!

 

[rac2]

...a spreadsheet that imports via DTS...

Forgive me if I misinterpret or have overlooked something, but Excel does not import via DTS.

Possibly you are exporting from SQL Server via DTS?

I have created the following table on SQL Server

wz with columns
Name VARCHAR(255)
ID INT
NameID VARCHAR(255)

containing
Name ID NameID
z 17 17
w 10 w10


I used the SQL Server DTS Wizard and exported this table to an Excel spreadsheet. Both values in the NameID field were stored as strings '17 and 'w10; the ID values were numbers and the Name values were strings.

I repeated this, exporting to a Text file specifying quoted and comma delimited. The file name, wz.csv. When I opened that file in Excel, it automatically converted the data differently, the NameID values were 17 and 'w17. This seems to be what you are describing.

If, instead, I start with a blank spreadsheet, select Data->Import External Data->Import Data then choose the .csv file, I get a dialog for the Excel Text Import Wizard. This allows me to specify that the third field is a Text field instead of a General field. The result is I have strings for both of the NameID values.

 

[Barneye]

Sorry if I confused anyone, sometimes I do that!

I have an Excel spresdsheet being imported into SQL using DTS. One column has part numbers. These part numbers are alpha numeric, but some are numeric only 6- 10 digits. During the import the numeric only part numbers are not imported and left as a Null value.

My research shows that Microsoft designed the Excel driver for SQL to work this way. Needless to say it is a pain in my behind. The only way to make it work is to export to a CSV file from Excel and then import to SQL.

I need this import to be automated and run daily.

Hope that clears up the confusion.

 

[Barneye]

Just a note....

I have actually used DTS to import from Excel to Excel. I needed to automatically combine 2 spreadsheets on a daily basis. It worked great!

 

[rac2]

What a good idea using SQL Server Agent to automate spreadsheet tasks.

What about adding a column to the spreadsheet that converts the numbers to text. TEXT(c4, 0) will convert the number in C4 to text. If C4 already has text, the function will return text. Import the column with the formula instead of the one with the mixed datatypes.

Or use DTS to import in two steps. In one step imort the spreadsheet column into a VARCHAR column and also import it into an INT colum. This should give NULLs in the VARCHAR PartNumber column where there are numbers in the INT column. In the second step use CONVERT(VARCHAR(50), col_with_integers) and UPDATE the rows with NULLs in the VARCHAR column to the converted value from the INT column.

 

[JinjaNinjaUK]

I was struggling for a while with the very same problem, but I finally managed to identify a workable solution.

As part of the DTS, add an ActiveX Script Task, and enter the following code.

=============
Function Main()

dim pkg
dim conXL
dim prp

set pkg = DTSGlobalVariables.Parent
set conXL = pkg.Connections("XLSConnection")
conXL.ConnectionProperties("Extended Properties").Value = "Excel 8.0;HDR=NO;IMEX=1"
Main = DTSTaskExecResult_Success

End Function
=================

Be sure to change the pkg.Connections("XLSConnection") to the name of the XLS Connection in your DTS. You may also want to change the HDR=NO, depending on whether you are looking to import Headers or not, and alter the version of Excel to match your requirements. It's the use of the IMEX=1 code that actually helps to import the data more accurately. (It's not exposed as part of the GUI, hence why we have to use an ActiveX script)

Make sure this ActiveX script task is workflowed so that it gets processed prior to the XLS Connection, and you should hopefully see a more successful import of the data. It certainly worked for me.

 

[JinjaNinjaUK]

Just a quick alternative solution if you want to use the sp_addlinkedserver procedure to open a connection to your XLS file. The IMEX=1 should solve the mixed format import issues.

EXEC    sp_addlinkedserver    EXCEL,
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'C:\DataTest.xls',
    NULL,
    'Excel 5.0;HDR=No;IMEX=1'

EXEC    sp_tables_ex    EXCEL

SELECT    *
FROM    Excel..Sheet1$

EXEC    sp_dropserver    EXCEL