Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bgolem
Starting Member
1 Post |
 Posted - 2011-05-10 : 19:00:25
|
| I'm driving myself crazy over this and can't figure out a solution. What I'm trying to do is import from excel into SQL using the Import Wizard. I've done it a thousand times and I only have problems whenever trying to import a blank cell into excel where the column doesn't allow NULLs in SQL.The actual error message is:"The value violated the integrity constraints for the column"Let's say I have an excel sheet with 3 columns, (1) CustomerID, (2) Name and (3) Address. Let's also say that for some customers they have an address and some don't. Well if the cell is blank in excel, you can't import it into a column in SQL unless it allows NULLs. If the column allows NULLs, it will just import a NULL value into SQL where the cell was empty in excel.In the same example, I have those same 3 columns in SQL and none of them allow NULLs.How can I get the data into SQL without creating a ton of work? Keep in mind we are talking about hundreds of tables with thousands of columns. There has to be a way to get the empty cell from excel to import as an empty string in SQL even if the column doesn't allow NULLs.What I have been doing is writing queries to allow NULLs on the columns, running an update query to set all NULL values to an empty string and then setting the columns back to not allow NULLs. I have to come up with a better process and I'm stumped. I can't have NULL values in those columns.Any ideas would be greatly appreciated. Thanks. |
|
|
latch
Yak Posting Veteran
62 Posts |
Posted - 2011-05-11 : 09:22:14
|
| Instead of using Import Wizard,build a package in BIDS using a derived column transformation or with help of a script component you can make the necessary changes there and then store to the destination table.I think it is simply then what you doing.or elsewith the help of execute sql task you can do whatever you right now doing it is also one time fix.Thanks |
 |
|
|
|
|
|
Importing from Excel to SQL2008