Importing from Excel into SQL using the Import Wizard
-
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. If the column in SQL doesn't allow NULLs, then in excel you must have a value in every single row for that column.
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.
-
Set each cell in the SQL table a "Default Value" that is either an empty space or "N/A" or "<UnKnown>" or etc...?
-
Yeah I tried doing something like that where I set the default value in the column properties to a blank string ('') and it still doesn't work. The data type on the column is nvarchar.
The value in the cell has to be blank so putting N/A or Unknown isn't an option.
Any other ideas?
I tried turning off all constraints on the table to see if that would let me do it but no luck:
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
-
Try changing the column to a varchar() and assign a default value. If I recall correctly, nvarchar() requires the data to be padded. So if you have an nvarchar(10), you will need 10 blanks.
-
I have to say that I don't understand the requirement to not allow NULLS in the column but insist that you insert a row without specifying a value for this NOT NULLABLE column. You can't have it both ways.
FROM BOL:
For each column in your table, you can specify a default value that will be entered in the column if the user leaves it blank. If you do not assign a default value and the user leaves the column blank, then:
If you set the option to allow null values, NULL will be inserted into the column.
If you do not set the option to allow null values, the column will remain blank, but the user will not be able to save the row until they supply a value for the column.
For text strings, enclose the value in single quotation marks ('); do not use double quotation marks (") because they are reserved for quoted identifiers. For example, type: 98036 or 'Paris, France'.
If your entry in the Default Value field replaces a bound default (which is shown without parentheses), you will be prompted to unbind the default and replace it with your new default.
To enter a numeric default, you enter the number.
To enter a object/function you enter the name of the object/function with no single quotes around it.
To enter an alphanumeric default you enter the value with single quotes around it.
To specify a default value for a column
In Object Explorer, right-click the table with columns for which you want to change the scale and click Design.
The table opens in Table Designer.
Select the column for which you want to specify a default value.
In the Column Properties tab, enter the new default value in the Default Value or Binding property or select a default binding from the drop-down list.
Note:
To enter a numeric default value, enter the number. For an object or function enter its name. For an alphanumeric default enter the value inside single quotes.
The new default value is assigned to the column in Table Designer after you click outside the grid cell or use the TAB key to move to another grid cell. It takes effect in the database when you save your changes in Table Designer.
-
No luck. I tried setting the default value to as many blank spaces as the column length as well as changing the data type to varchar.
Any other ideas?
-
andersg98 (5/11/2011)
I have to say that I don't understand the requirement to not allow NULLS in the column but insist that you insert a row without specifying a value for this NOT NULLABLE column. You can't have it both ways.FROM BOL:
For each column in your table, you can specify a default value that will be entered in the column if the user leaves it blank. If you do not assign a default value and the user leaves the column blank, then:
If you set the option to allow null values, NULL will be inserted into the column.
If you do not set the option to allow null values, the column will remain blank, but the user will not be able to save the row until they supply a value for the column.
For text strings, enclose the value in single quotation marks ('); do not use double quotation marks (") because they are reserved for quoted identifiers. For example, type: 98036 or 'Paris, France'.
If your entry in the Default Value field replaces a bound default (which is shown without parentheses), you will be prompted to unbind the default and replace it with your new default.
To enter a numeric default, you enter the number.
To enter a object/function you enter the name of the object/function with no single quotes around it.
To enter an alphanumeric default you enter the value with single quotes around it.
To specify a default value for a column
In Object Explorer, right-click the table with columns for which you want to change the scale and click Design.
The table opens in Table Designer.
Select the column for which you want to specify a default value.
In the Column Properties tab, enter the new default value in the Default Value or Binding property or select a default binding from the drop-down list.
Note:
To enter a numeric default value, enter the number. For an object or function enter its name. For an alphanumeric default enter the value inside single quotes.
The new default value is assigned to the column in Table Designer after you click outside the grid cell or use the TAB key to move to another grid cell. It takes effect in the database when you save your changes in Table Designer.
Yeah I understand what you mean. The program was written in VB.net with an SQL backend. Whenever they programmed the software, they decided not to program for NULL values except in columns with a datetime data type. I have no clue why. I've been told that the software will error out if there are NULL values in any field other than one with a datetime data type.
I've tried setting the default value in the column properties and it still gives me the error message saying:
"The value violated the integrity constraints for the column"
I know it sounds weird but I need some way to insert a blank value into a column that doesn't allow NULLs when importing from Excel into SQL.
Basically, we send these import templates to new clients and they put the data in. At that point, they send those templates back and we import them into SQL for them. FYI - It's accounting software
-
Are you using an SSIS package? If you are, you need to put logic in there to replace NULL with CHAR(32) before you insert it into your SQL table.
I think what's going on is your insert statement is passing the NULL value directly into the table.
For example, if you run the following this will create a test table...
CREATE TABLE [dbo].[TESTDefValue](
[MangerName] [varchar](50) NULL,
[MangerNickName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[TESTDefValue] ADD CONSTRAINT [DF_TESTDefValue_MangerNickName] DEFAULT ('DUMMY') FOR [MangerNickName]
GO
Now run the below lines of TSQL...
Insert Into TESTDefValue (MangerName,MangerNickName) VALUES ('John Doe','Toilet')
Insert Into TESTDefValue (MangerName,MangerNickName) VALUES ('Jack Sparrow','')
Select * From TESTDefValue
The inserts run fine and the non nullable field gets populated. Now run this one....
Insert Into TESTDefValue (MangerName) VALUES ('Jack Sparrow')
Select * From TESTDefValue
This runs fine also but notice the default value is inserted. It's inserted because that field was omitted in the insert statement.
Now run this statement...
Insert Into TESTDefValue (MangerName,MangerNickName) VALUES ('Will Turner',NULL)
It fails because you are trying to insert the NULL. That is what your package is doing, inserting the NULL value.
You need to intercept that NULL value BEFORE it get's into the INSERT statement. You'll have to do this for every column that is not nullable.
-
That makes sense now. I am using an SSIS package. What I'm trying to do is make this easy enough for our training analysts to be able to run. It seemed like creating the SSIS package would be the way to go but I do understand what you mean about not having it both ways.
I understand what you mean about catching the NULL value before. For any columns that aren't in my import template but exist in the database, I can use the default values. By using a single quote in the empty cells in excel, it treats them as empty strings and imports successfully. It also uses the default value for any columns not in the excel template.
I'm definitely on a better path now. I just need to figure out how to write some automatic macro in excel that will replace any empty cells with a single quote.
-
You can do metadata transformations from Excel to MS-SQL, but it requires the building the Package the MS Visual Studio BIDS environment.
Personally I would not recommend doing it this way, loading to a Staging table and working on the data in SQL would just be easier, but if your hands are tied this will work.
Start a new BIDS project.
Add a Data Flow Task and open it.
Add an Excel Source and set it up
Add a Script Component.
Connect them with the green arrow.
Set up the Script Component, (if you haven't done this before prepare for a headache, it's not very intuitive.)
In the Script Transformation Editor set the ScriptLanguage to Visual Basic 2008.
In the Input Columns tab select the source columns, (probably all of them).
In the Inputs and Outputs tab, click open the Output 0 and then click open the Output Columns tree. Add the columns that are needed for the destination. For each column added change the DataType to string[DT_STR]. (You many need to change the length here).
Now go back to the Script tab and select the Edit Script button.
Warning - the ssisscript editor will automatically build the script depending on what you specified in the Script Component. So if you forgot something you would have to start over.
Go to the subroutine Input0_ProcessInputRow and add the code below, (modified to your database names obviously).
Basically we are looking at the input string before it's writen to the table and testing for NULL values. If one if found it's being changed to some default value.
Good luck.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strID As String
Dim strName As String
Dim strAddress As String
Me.Output0Buffer.AddRow()
'
If Row.F1_IsNull Then
strID = "No ID"
Else
strID = Row.F1
End If
If Row.F2_IsNull Then
strName = "No Name"
Else
strName = Row.F2
End If
If Row.F3_IsNull Then
'strAddress = Nothing
strAddress = "No Address"
Else
strAddress = Row.F3
End If
'
'MsgBox(strID + " " + strName + " " + strAddress, 1, "row" + strAddress)
Me.Output0Buffer.ID = strID
Me.Output0Buffer.Name = strName
Me.Output0Buffer.Address = strAddress
End Sub
A nod is as good as a wink to a blind bat.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply