Null values imported from Excel 1

[nuVBer]

I'm trying to import an Excel spreadsheet into a table using DTS. The data I'm importing is a mix of text characters and numbers in Excel that I've formatted into text. When I import it to a SQL table I get values for the text, but NULLS for the numeric data. I am creating the field as nvarchar. Is there something else I can do to avoid the NULLS and get it to import the values as characters?

 

[dbomrrsm]

save the excel spreadsheet as a csv or txt file then import it - that will work - excel is awful to use with DTS.

[blue]DBomrrsm[/blue]

 

[nuVBer]

Thanks DBomrrsm,
That works fine.

 

[dbomrrsm]

Great - I avoid Excel where possible.

[blue]DBomrrsm[/blue]

 

[bmpsu]

If you don't mind me asking...
Why do you avoid excel with DTS? I tried both excel and csv and my data imported nicely.

I do have a question that fits this post. Any field that is blank (empty) in my excel or csv file is imported as '<NULL>'. Am i just being too picky and this really doesn't matter, or is there a way to replace '<NULL>' with '' while importing.

Any thoughts or suggestions?

 

[DrSql]

The EXCEL is so bad for formatting. Sometime converting integers,chars etc would have issues. Use CSV, txt, are the safe way to go. Good luck

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.

 

[dr00bie]

I had the same issue, but unfortunatly I couldn't get away from using xls files... so instead of using Table/View as the source for the transform, I use a query,

SELECT Fields
FROM [ExcelWorksheet$]
WHERE (PONum IS NOT NULL)

My Excel file must have a PONum in order to be imported, this fixes the problem that I was having where the DTS package imported empty records because I had formatted them.

HTH,
Drew

 

[bmpsu]

Excel might work for me because I have been exporting a table form SQL Server to Excel. Do all my formatting and then import the Excel Worksheet back into SQL Server. I haven't had any problems yet, other than the Buffer size. I figured out how to get past that.

I can run an update statements on each column to replace the value <NULL>. When you have hundreds of columns, this proves to be a lengthy process. I'll run this statement...
--------------
UPDATE Table
SET ColumnName = ''
FROM Table
WHERE ISNULL(ColumnName, '') = ''
--------------

...or I tried using this in the DTS transformation...

--------------
If IsNull(DTSSource("ColumnName").value then
DTSDestination("ColumnName") = ""
Else
DTSDestination("ColumnName") = DTSSource("ColumnName")
End if
--------------

You still have to do this for each column you would like to get rid of <NULL>. I was just curious if there was a way to go through an entire table and replace <NULL> with "". My other question is; Am I being to picky about my database values?