SQL Server 2008 Import Data Wizard doesnt recognize Integer data for a column.

Question

Hi,

I have an excel sheet with 10 columns with headers. For a column I can have the data as "FF5" or else 620. The sheet name is IODE.

I am trying to import this data from SSIS Import data wizard into the database of table IODE.

On selecting source and destination in the wizard, when I click on PREVIEW DATA in Select Source Tables and Views window, I see the column with 620 as null. After importing this data, the table will have the NULL Instead of 620.

The data type for this column in table is nvarchar(50), I tried many data types like varchar(100), text/..

Only alpha numeric data is accepting.

I didn't write any code for this.. I am just trying to import data from excel sheet to a table.

Please help me in solving this

Thanks Ramm

Answer 1

Do you mean that you have either FF5 or 620 as the values for that column meaning you have one or the other and nothing else or are there blank fields in that column as well?

Answer 2

Guys,

I tried reading the excel by using the Excel library reference in VB6.0.

As SSIS Import Wizard treats NUMERIC as NULL when the data is exported to the SQL Table.

This procedure works very well for the data insertion and also for the other database operations.

Private Sub AccessExcelData() On Error GoTo errHandler
Dim oXLApp As Excel.Application'Declare the object variable
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.worksheet
Dim strFileName As String
Dim lCount As Long
Dim strSCDName As String
Dim strICDName As String
Dim intSource_Index As Integer
Dim strInput_Port As String
Dim strLabel As String
Dim strSDI_CID As String
Dim intWordBitNO As Integer
Dim strFilter_Type As String
Dim strPgroup_Input As String
Dim strParagraph_Input As String
Dim strSQL As String Dim sConnString As String
Dim cnTest As New ADODB.Connection
Dim rsTempRecordset As New ADODB.Recordset
Dim objDataAccess As New FmmtDataAccess.clsDataAccess
Dim strxmlResult As String
objDataAccess.Intialize ConString

strFileName = App.Path & "\IODE.xls"

sConnString = "Server=uasql\commonsql;Database=accounts;Driver=SQL Server;Trusted_Connection=Yes;DSN=uasql\commonsql"

With cnTest
.ConnectionString = sConnString
.ConnectionTimeout = 4
.CursorLocation = adUseClient
.Open
End With

' Creating part of the excel sheet.
Set oXLApp = CreateObject("Excel.Application")
'Create a new instance of Excel
oXLApp.Visible = False
'Donot Show it to the user
Set oXLBook = oXLApp.Workbooks.Open(strFileName) 'Open an existing workbook
Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet oXLSheet.Activate

With oXLApp
For lCount = 2 To oXLSheet.UsedRange.Rows.Count
strSCDName = .Cells(lCount, 1).Value
strICDName = .Cells(lCount, 2).Value
intSource_Index = .Cells(lCount, 3).Value
strInput_Port = .Cells(lCount, 4).Value
strLabel = .Cells(lCount, 5).Value
strSDI_CID = .Cells(lCount, 6).Value
intWordBitNO = .Cells(lCount, 7).Value
strFilter_Type = .Cells(lCount, 8).Value
strPgroup_Input = .Cells(lCount, 9).Value
strParagraph_Input = .Cells(lCount, 10).Value

'strSQL = "Insert into XYX () values (strSCDName ..... ) Here any DB related queries can be used
rsTempRecordset.Open strSQL, cnTest, adOpenForwardOnly, adLockReadOnly Next
End With
' Closing part of the excel sheet.
oXLApp.Visible = False 'Donot Show it to the user
Set oXLSheet = Nothing 'Disconnect from all Excel objects (let the user take over)
oXLBook.Close SaveChanges:=False 'Save (and disconnect from) the Workbook
Set oXLBook = Nothing
oXLApp.Quit 'Close (and disconnect from) Excel
Set oXLApp = Nothing
Exit SuberrHandler:
MsgBox Err.Description
Screen.MousePointer = vbNormalEnd Sub

With this procedure the excel records can be read from vb applicatoin and can be inserted into existing table in the SQL database. Thanks Ramm