importing from excel into SQL Server 2005 changing values to NULL

I can't figure this one out...

I have an Excel spreadsheet (Excel 2003 format) with a column of values. The column is formated to General (although I've tried it formated to text). The values are alphanumeric. However, there are a few values that are completely numeric.

Example:

_ITEMNUMBER_

1-528

K214-5

184PR

45678

As can be seen, the last value is completely numeric. I am importing into a table with one column that is formated as nvarchar(50).

I use dtswizard.exe and do a simple import into the table and the alphanumeric values import just fine but anytime a value is completely numeric it has a NULL value in the table instead of the value that should be there.

I've tried changing the format of the column to nchar, char, etc, etc. Always comes in NULL.

Any ideas

Phil,

Thanks for the advice and link. I was wondering, how would I implement this using dtswizard.exe That is typically how I do quick imports/exports. Do I need to construct an SSIS package so that I can specifically set the connection string or is there a way to implement this using the wizard

Thanks again for the advice!

Philip,

Since the user interface of the Import and Export Wizard doesn't let you specify the Extended Properties for the Jet Provider anywhere, you cannot use the Wizard alone when you need to specify IMEX=1. However you can save the package, edit the connection string to add the IMEX setting, and then your import should work as expected.

-Doug

Great info all!

Thanks for the help. I'll probably just create an SSIS package and use it specifically for importing excel sheets. I'm no stranger to SSIS, I just like to use the dtswizard when I can because it's quick and easy.

Thanks again, all!