June 7, 2003 at 6:03 am
Hi, I want to IMPORT an excel file into a SQL SERVER table via DTS.
The sql table has an identity field
DESTINATION:
tblSql
IdField
NameField
'etc
SOURCE:
tblExcel
idField
nameField
'etc
Should I include the “idField” in the excel file, and leave it blank?
I tried this but it didn’t work.
I also tried ommitting the “idField” from the excel file, but this did not work either.This is getting really frustrating.At the moment, I am trying to keep everything as simple as poss, and my excel file only has one row.I can preview it and everything seems to be fine.
I wonder wht I am doing wrong?
BTW, if I have only ONE sheet in my excel workbook, named “tblExcel”, why do TWO sources appear in the “DTS Import /Export”
Dialog box, ie
1) “tblExcel”,
2) “tblExcel$”
Any help much appreciated,
Cheers, yogi.
June 8, 2003 at 10:03 pm
Leave the Identity field out of the Excel file... it's not needed.
In the DTS Import/Export wizard:
1. define your spreadsheet as source
2. define your SQL database as destination.
3. select "copy tables and views..."
4. select your source sheet (eg. tblExcel$)
5. within transformations dialog, choose "Create destination table" and click on "Edit SQL"
6. In the CREATE TABLE statement insert the first column as "[IDField] int NOT NULL, " then OK.
7. DON'T select "Enable Identity Insert"
That should work for a new SQL table.
Cheers,
- Mark
June 9, 2003 at 4:47 am
Hi bud, thanks for the reply.
The destination table already has data in it, so I really just want to append the imported data from the source to the destination.I did the following:
1) Left the Identity field out of the Excel source file
2) Defined the spreadsheet as source
3) defined the SQL database as destination.
4) selected "copy tables and views..."
5) selected my source sheet (eg. tblExcel)
I only have 1 row in this source excel file.When I select “Preview”, this single row appears ok.I only want to import this row, to get me started.
I then click on “transform” > “Append rows to destination Table”
I also click on “Enable Identity Insert”
I get the following error:
TransformCopy ‘DirectCopyXForm’ conversion error.
Destination does not allow NULL on column pair 3 (source column ‘field3’ (DBTYPE_R8), destination column ‘field3’ (DBTYPE_14)
I get this error, regardless of whether I click on “Enable Identity Insert” , or not.
In the single row that I am pathetically trying to import, the ‘field3’ column value is not null, it is 1.
SCHEMA
Excel SOURCE file
field2
field3
SQL Server DESTINATION table
IdField (field1)
field2
field3
This is doing my head in.Does anyone know why it is trying to insert a NULL value, when I can plainly see from the preview that the value of ‘field3’ is 1?
Any help greatly appreciated.
Cheers, yogiberr
June 9, 2003 at 4:06 pm
Just a shot in the dark, going back to your original question (BTW, if I have only ONE sheet in my excel workbook, named “tblExcel”, why do TWO sources appear in the “DTS Import /Export”)... Maybe you have defined a NAME in your tblExcel worksheet called tblExcel?
And, maybe that NAMEd area tblExcel extends past the actual end of data? Check this in Excel with Edit/Goto and it will highlight the named area.
This would explain why you have 2 sources. tblExcel refers to the named area. tblExcel$ refers to the worksheet.
If that's the case, delete the name and just use tblExcel$. It should then work.
Cheers,
- Mark
June 9, 2003 at 4:43 pm
hi Mccork,
aye, you were dead right.
My excel source sheet originally contained 30 records.I had deleted 29 of them, but, as you said, the "tblExcel" AREA was still trying to import 30 rows, 29 of which were null.It would have taken me forever to have worked that one out.
Thanks for taking the time, I really appreciate it yogiberr
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply