(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

Seems to me that the data being imported is being imported as an int and hence the values which are not integer (row 62 and beyond) do not get imported into the table. However, this is just my guess. You might get better answer from Integration Services forum and I am moving this thread to the forum for you to get better assistance.

HTH,

|||

Yes that is correct. SSIS will look at the first few rows of Excel data to determine what the data type of the column is. As far as I know there is no easy way around that. Your best option is to ensure that one of the "R" values is in the first few rows.

Otherwise, you could do an import from .csv isntead of from Excel. That would give you more control.

Dylan.

|||

I struggle with the strange behaviour of excel imports for days now..

what helped a lot was this articel:

http://blog.lab49.com/?p=196

I hope it helps you too...

Mybe someone has a solution to my urgent excel import problem too... please look here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2129460&SiteID=1

if you have a minute left...

Thanks

Jens

|||

Thank you every one for your input... I solved the problem by changing the cell format as text and then copied all the information back.. to the excel spread sheet as text and everything works fine...

Regards

Karen