Importing data from Excel

[enak]

I am having a problem importing data from Excel. Here is the error message:

"Conversion invalid for datatypes on column pair 6 (source column 'FirmID' (DBTYPE_WSTR), destination column 'FirmID' (DBTYPE_I4))."

The column FirmID is int(4).

The spreadsheet was created by exporting from this exact table. I cleaned up some of the rows and now I am trying to import the data back into the table. This worked yesterday and there have been no changes.

Any ideas?

Thanks,
enak

 

[dbomrrsm]

save the excel sheet as a text file/csv then import that - problem with excel drivers from SQL Server - will import fine from text file.

[blue]DBomrrsm[/blue]

 

[enak]

Thanks, but I got the same error message.

 

[Pattycake245]

I am assuming your are using a DTS or import wizard to do this. SQL server is recognizing the excel column to be a string field whereas your SQL column is an int. Is there perhaps characters or dashes in some of the values in that column? That, to me, would be the problem.

Tim

 

[enak]

Some of the values are negative. This table was upsized from Access 2000.

 

[jhall01]

If you exported to CSV/TXT then it sounds like you have a non numeric value in your column on one of the records. I would check your spreadsheet for character values.

Easy way to do this is add a autofilter to that column and click the down arrow which will list all your unique value and scroll through that.

I would also check blank spaces at the end of the file since excel sometimes adds blank rows to the end when you delete out data.

 

[ProdAir]

This might be a little longer process wise but I'd import the data into a staging table first where all the columns are varchars. Then you can import it into your table (and perform any checks using T-SQL). That way, it doesn't just blow up in your face and you don't lose any data. Also, like dbomr said, save it as a text file first. Excel formats the data according to what it thinks is inside the cell so that might not be what SQL Server expects and that could cause you a lot of grief.

 

[aolb]

I always have issues with importing spreadsheet because the first row determines the data type. what I do is on the first row of the spreadsheet if the value is null, add '01/01/1900' for a datetime column, 'string' for a string value, 1 for an int value etc so that the spreadsheet rows have been defined correctly. After the import I manually remove the values I've added to define the spreadsheet columns.

Bit of a work around.

 

[enak]

Sorry about not getting back to this. i was able to import the data by not importing the problem column.

Thanks for all of the responses that were given.

 

[metahari]

This post helped me too. I had to not import my first column (which was an an empty id column) so I could use the auto-increment.

--Metahari