Import Excel Data to SQL Error

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Import Excel Data to SQL Error

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-24 : 14:02:00

I am attempting to import an excel sheet to SQL and keep coming up with the following error:

Error during transformation ... row number 29
TransformCopy conversion error: Destination does not allow null
Source column 'ProductID' (DBTYPE_R8),
Destination column 'ProductID' (DBTYPE_STR)

When I look at row 29, the ProductID is '1240000A' and not NULL.
All prior ProductID's had no Alpha characters ans appeared as 7 digits eg. '1240000'. I Previewed the data before the transformation and for some reason the ProductID '1240000A' appears as NULL, even though the Excel Sheet has the value '1240000A' entered. Help would be appreciated. Thanks.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-24 : 14:03:27

Bring it in to a staging table, then see what DTS byuild the new table as.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-24 : 14:25:53

It identifies it as 'float', even though I have the EXCEL column set to TEXT.

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-24 : 14:41:36

That's probably because the first n number of rows look like a number.

But that's not your problem.

Anything can get loaded to a char column.

No, your problem is elsewhere.

Script the destination table, with all it's constraints, and unique indexes, and create a new table.

Then do

NSERT INTO newTable (Collist)
SELECT Collist FROM Stage table

And tell us what error you get

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-24 : 16:19:38

I'm relatively new to SQL so I hope I did what you asked.
Step 1. Transformation: EXCEL to CONVFILE$
Step 2. Transformation: Query:
INSERT into tblShipments
SELECT * From CONVFILE$

ERROR: Deferred prepare could not be completed
Line 3: Incorrect syntax near ')'.
Context: Error calling GetColumnInfo. Your provider does not support all the interfaces/methods required by DTS.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-25 : 01:15:53

As an alternative try this also
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

uberman
Posting Yak Master

159 Posts

Posted - 2005-08-25 : 06:20:18

I think the following will help

http://support.microsoft.com/kb/257819
http://support.microsoft.com/kb/189897/EN-US/
http://support.microsoft.com/kb/194124/EN-US/

the data from excel is sampled to determine its type and the system has decide the column is of type "number" -- it sees the A just ignores that cell, hence the null, hence the error

there are all sorts of switches and flags you can try and set but nothing works because of bugs in MDAC2.1+!

the only thing I have found to work is the registry hack detailed in the second link where you enter a value of 0 to make excel sample all cells before deciding on type, but this could have performance implications

hope this helps

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-25 : 10:10:13

Thanks for your post, uberman, that is exactly what is happening.
I'm going to trick the system by prefixing the columns in question with an alpha character. Once it's in SQL, I'll just run an update removing the alpha character.
I'm almost there. I now get the following error on the last Record ...

Violation of Primary Key constraint, cannot insert duplicate key.

I don't have any duplicates, it's on the last record.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-08-25 : 10:17:42

Sorry my mistake. I did not clear the SQL table before doing the transformation rerun. Thanks.

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-09-09 : 12:27:56

Just thought I'd update this post. I have created a Macro in Excel that prefixes a column with ' to define it as a text field. SQL will now pickup the column as a text column. I also use TrimAll to remove trailing blanks from all columns, this should run first. These 2 macros have solved my problems when attempting to import EXCEL files into a SQL database:

Sub SetColumnToText()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
cell.Formula = "'" & cell.Text
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

Sub TRIMALL()
Dim myRange As Range
Dim myCol As Range
Set myRange = Intersect(ActiveSheet.UsedRange, Selection)
If myRange Is Nothing Then Exit Sub
Application.ScreenUpdating = False
myRange.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
For Each myCol In myRange.Columns
If Application.CountA(myCol) > 0 Then
myCol.TextToColumns Destination:=myCol(1), _
DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
End If
Next myCol
Application.ScreenUpdating = True
End Sub

ingineu
Yak Posting Veteran

89 Posts

Posted - 2005-10-17 : 13:04:38

In doing some more conversions, I ran into problems with loading columns with Null values. In some cases, the column would not load the numeric values if the first 20 rows were Null. I then used Access to import excel data into SQL tables. Doesn't seem to be as picky. I use a combobox in the Access form to identify the Table:


    Conn1.Execute "TRUNCATE TABLE " & Me.cbxTable, , adCmdText
    ZFDir = "C:\..locn of Excel files"
    ZFile = DialogFile(OFN_OPEN, "Open file", "", "Microsoft Excel (*.xls)|*.xls", ZFDir, ".xls")
    If Len(ZFile) = 0 Then
        MsgBox "File must be selected", vbInformation, "Error"
        Exit Sub
    End If
    DoCmd.TransferSpreadsheet acImport, 8, cbxTable, ZFile, True
    MsgBox "File Import Completed", vbInformation, "File Import"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources