| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-04-15 : 12:01:26
|
| Hi, I created a DTS job to import data from Excel into a SQL Server tbl. One of the columns is TrainerId which is actually an EMPLYOEE_ID. The import was successful but ids which started with 0 (zero) were substituted with NULL (The col is not a key and can accept NULLs). The key of this table is Student Id which is also EMPLOYEE_ID but nothing happened in this column for values starting with Zero. I can understand this because it is key and not null. Both the cols are text fields in the Excel file.Why is the data becoming NULL even when there is a value in the Excel file with TrainerId column?Thanks,Sarat. |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 12:16:43
|
| can you script the table ddl and post it? Do you drop and recreate the table everytime? You're much better off have the table in existance first, defined as you need it to be if that's the case.Brett8-) |
 |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-04-15 : 12:58:32
|
| Hi, I created the table before hand. Here is the sql I used:--CREATE TABLE PS_DTC_ADMIN_TRN (EMPLID char(11) NOT NULL, COURSE char(6) NOT NULL, COURSE_START_DT PSDATE NOT NULL, COURSE_TITLE char(30) NULL, COURSE_END_DT PSDATE NULL, DEPTID char(10) NULL, DTC_TRAINER char(11) NULL, INTERNAL_EXTERNAL char(1) NULL, ATTENDANCE char(1) NULL, TRAINING_REASON char(2) NULL, PREREQ_MET char(1) NULL); --Thanks,Sarat |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-15 : 13:20:59
|
| Which column is having the problem (there is no TrainerID column in your DDL)?Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-04-15 : 13:35:05
|
| Sorry it is called DTC_TRAINER. I also tried making it a NOT NULL column. Then the error says, I am trying to import NULL which is unacceptable but all the rows do have values for this column in the file. I just realized that I am also missing the very first row in the file didn't get loaded!Sarat. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-15 : 13:47:05
|
| Well I believe that we are going to need to see a couple of sample rows from the Excel file and also the same rows after they are imported into SQL. Once we have that information, we can see if we can duplicate your problem and/or provide a fix.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 13:48:24
|
| Have you looked at the transform data task_Transformation tab?Do you see all of the columns, and are they mapped correctly.How did you build the package, through a wizard or manually?Brett8-) |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-04-15 : 16:18:49
|
| 1. I created the package via wizard.2. I am pretty sure the mapping is correct because all the data looks perfect except for these 2 glitches.Here is how it looks in Excel, there is no heading line:059495 AD1000 10-16-02 10-16-02 AZP003 025769059495 CE1014 10-16-02 10-16-02 AZP003 012490059495 CE1015 11-20-02 11-20-02 AZP003 212490--do--When I do a query from table, I get this result:Emplid Course_Start_Dt Course Course_End_Dt Deptid DTC_Trainer******First row desn't appear**********059495 2001-07-12 CE1014 2002-10-16 AZP003 NULL 059495 2002-09-16 CE1015 2002-02-20 AZP003 212490 Entire file got loaded except one row which is the first one! + Ids in Trainer column starting with ZERO comes in the table as NULL. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-15 : 16:50:10
|
quote:
2. I am pretty sure the mapping is correct because all the data looks perfect except for these 2 glitches.
Only one way to know for sure. You need to look. Because there's obvously a problem. Also don't you get any error messages when you run the thing?Do you know how to look at the trsnformations tab?Here's another idea. Why not just import the data and let SQL create a table for you. Then compare your structures. I'm still betting on a mismapping in the transformation.Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-15 : 17:04:14
|
| Instead of using the wizard, you should create a DTS package from scratch. Then mess with the transformation tab as X002548 suggests.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-04-15 : 18:27:54
|
| Hi,I added a coulmn for heading and I looked at the mapping, it is correct. I am able to get all rows now but when I open excel in preview in DTS package properties, it vanishes the ids starting from Zero for the last column and not the first.ex:059495 CE1014 10-16-02 10-16-02 AZP003 212490059495 AD1000 10-16-02 10-16-02 AZP003 See the last value in second row, there is actually a value=025769.WHY? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-15 : 19:17:12
|
| Hey it works now, why ask why? j/k Not sure why it wouldn't show the data in the preview pane for that column, but it probably is a bug.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-04-15 : 20:00:55
|
| BTW Excel STINKS!The reason I am getting blank value is because Excel messes up the numbers fields by truncating preceding Zeros. It is extremely annoying! Even after I convert the column to TEXT, i have issues. I created a new file and typed in the trainer ids and then changed the format to Text and then it worked but if I modify the same spreadsheet, it behaves stupidly!!!Most of my day has been wasted!!Sarat. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-15 : 21:49:25
|
| There are some subtle problems when importing from Excel spreadsheets, especially ones that get edited often. It seems to be some kind of range definition that doesn't get updated properly when the workbook is edited. Somehow DTS keeps hitting the original range even though rows were added, deleted, or altered.I'd recommend, if you really need this data in SQL Server NOW, just exporting the Excel sheet to a tab-delimited text file and importing that instead. You may lose the leading zeros as before, but you shouldn't have problems with missing data. |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-04-16 : 14:25:49
|
| thank you rob. i like your idea. it feels good to know the exact cause! usually we request our vendors a flat file which can be opened in text pad and then we write programs to parse the data depending upon the spec provided by them. but this is a first time intenal customer + the data is pretty bad so we let them give the data in the way they are comfortable with which happens to be Excel!thanks again.sarat |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-16 : 16:19:31
|
| Can they feel comfortable with MS Access? Your's is a common problem around here, so what I do is give them an access app, on a file server, with a beautifule front end (not too fancy) and have the enter data into a table as predefined by me (or actually the table the data is going in to).Then it's a breeze to get the data.This was esp important when I was trying to get data to DB2 on os/390....Now you want to talk about nightmare. DB2 is bullet proof, but flexible?..steel is more flexible. So the Access solution worked there too...used fixed width format. Works beautifully (even had a null indicator column that db2 like to tell it if the field is null not space...kind of a problem with numbers and dayes).Well Good luck. I hope you can implement the access solution.PS You can't do this in DB2:declare @x datetime, @y intSelect @x=' ', @y = ' 'Select @x, @yWHY you can do it here (SQL Server) is beyond me!!Brett8-)Edited by - x002548 on 04/16/2003 16:20:30 |
|
|
|
|
DTS import data type issue