| Author |
Topic |
|
mjwillyone
Starting Member
7 Posts |
 Posted - 2008-06-20 : 10:14:54
|
| Hello,I have an Excel file (complete with headers) that I am wanting to import (append) to an existing SQL tables When I use the file import wizard with my version 8 Microsoft SQL server software,I get the following error: "Destination does not allow NULL on column pair 1 (source column 'CheckRef (DBTYPE_R8), destination column 'CheckRef (DBTYPE_I4)). Obviously, the import fails.What date type (in Excel) produces the I4 data type needed by the SQL table? I looked at the SQL table properties and that particular field calls for the data type "int" and the size is 4. I assume that is an integer. I have already made the data type in the Excel file cells for that data a number with NO decimal or zeros. Can someone help me make this work?Thank you very much. Your help is greatly appreciated.Mike |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-20 : 10:19:28
|
| I dont think problem here is data type. I guess seeing the error message its trying to insert NULL values to a not null column. What you need to do is to check if your excel file contains blank data for this column and if thats interpreted by sql table as NULL. If yes, you could either fix data in source or redirect only those rows to error table where you can correct the wrong data. If you're using SSIS there's already a conditional task available to do this. |
 |
|
|
mjwillyone
Starting Member
7 Posts |
Posted - 2008-06-20 : 11:54:56
|
| Dear visakh16,Thanks for your help. The field in question has data in it. In fact, there are about 6 rows of data and all of the rows contain data for that column. I will need to look at the option you mentioned and see if I can get an errors listing.I have read on this forum of a few (very few) who have had similar types of problems, but not quite like this one. Typically, they have had empty fields. I don't. I wonder if anyone else has any suggestions?Thanks again,Mike |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-20 : 12:37:18
|
quote:
Originally posted by mjwillyone
Dear visakh16,Thanks for your help. The field in question has data in it. In fact, there are about 6 rows of data and all of the rows contain data for that column. I will need to look at the option you mentioned and see if I can get an errors listing.I have read on this forum of a few (very few) who have had similar types of problems, but not quite like this one. Typically, they have had empty fields. I don't. I wonder if anyone else has any suggestions?Thanks again,Mike
looking into it more closely i think you're right. The datatypes also vary. Can you try casting the column to type DBTYPE_I4 by using a Derived column task before dumping to destination? |
|
|
|
mjwillyone
Starting Member
7 Posts |
Posted - 2008-06-20 : 13:41:29
|
| Since I am new to SQL, I am not sure what you are asking me to do. However, if you will let me know how to do it (in Excel or SQL) I think I can get it done!Thanks,Mike |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-20 : 13:50:39
|
quote:
Originally posted by mjwillyone
Since I am new to SQL, I am not sure what you are asking me to do. However, if you will let me know how to do it (in Excel or SQL) I think I can get it done!Thanks,Mike
I guess you were using Export Import wizard. Do you have business intelligence studio installed. Then you can create an Integration services package with Excel source and OLEDb Destination with a Derived column task in b/w. Using that task you can cast the columns data type to int(I4).something like(DT_R8)Column |
|
|
|
mjwillyone
Starting Member
7 Posts |
Posted - 2008-06-20 : 13:57:13
|
| Yes, I was using the wizard (DTS). Busines intelligence studio? Nope ... don't have it installed. How do I go about getting it installed?Thanks! |
|
|
|
mjwillyone
Starting Member
7 Posts |
Posted - 2008-06-20 : 14:00:37
|
| At least I don't THINK I have it installed! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-20 : 14:01:39
|
quote:
Originally posted by mjwillyone
Yes, I was using the wizard (DTS). Busines intelligence studio? Nope ... don't have it installed. How do I go about getting it installed?Thanks!
While installing SQL Server it will be listed along with components. You just need to select the checkbox for integration serviceshttp://forums.asp.net/p/1062316/2029687.aspx |
|
|
|
|
Excel Import to SQL - Data Type Problem