Importing data into db problems (

Question

Importing data into db problems (

gcruise

SSC Rookie

Points: 36
More actions
February 6, 2008 at 10:35 am

#186418

I have about 10K records that i need to import from excel into a new table on my db but whenever i try to import from excel using the wizard or code below i get the following error message:"TransformCopy conversion error: Destination does not allow null"
Insert into mpg_fee_schedule Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\codes.xls;HDR=YES',
'SELECT * FROM [mpg_fee_schedule$]')
The column contains a mixture of numbers and letters....for example:46600.INH...i've tried changing the data type to text in excel and using the wizard before importing but no luck.
Thanks in advance..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Try creating an interim table in SQL Server with the correct number of columns but all fields as say varchar. Import to the interim table, then copy data from the interim table to a correctly formated table handling the datatypes in the process. In general the wizards in Microsoft seem to work on the basis that all your records will be the same datatype as the fist few records in the source, which invariably won't be the case.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

tbeadle SSCommitted Points: 1635 More actions · Answer 2

Hello,

I would recommend using DTS if possible.

Regarding your problem, from the error message it would appear that in the column mentioned there are rows that are not populated so the interface is interpreting them as null. In the table you are creating nulls are not acceptable. Check your table definition for this column and make sure it can accept nulls.

Regards,

Terry

gcruise SSC Rookie Points: 36 More actions · Answer 3

😀

Thanks to everyone that replied...

I was able to import the new table without getting the error "Destination does not allow null" by copying the data from excel into a text file.. i then used DTS to import into my testDb. The column in question retained all the data numbers and letters (varchar 8000).

Thanks

G

tbeadle SSCommitted Points: 1635 More actions · Answer 4

Another suggestion. When you are in DTS there is a little dialog icon to modify the field contents so that you don't have to have an 8000 varchar ( if your data is 30 and not 8000 that is. )

Regards,

Terry