| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2005-07-04 : 08:42:36
|
| Hi,I have a DTS package which uses an excel file to load data into a table.In the Data Transformation when trying to map the source fields to the destination fields, it shows that the source field is a double. When I check the excel file, the field is a text field and so do not see why the source field is recognised as a double.And because of this the data which is not transfered.The destination is varchar(50)Any thoughts please?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-04 : 10:18:47
|
| Is the formatting of the spreadsheet influencing DTS perhaps?Kristen |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-04 : 11:02:10
|
| yes, exactly. But no matter if I change the format of the excel field, the DTS package still says that the source field is a double.This is the problem and therefore does not import that value because it is a text and not a number.Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-04 : 13:37:51
|
| Format the column as TEXT in Excel?That's always been my solution. Possibly added a dummy ROW 2 with an alpha value in the offending column, and then deleting that row [in SQL] after import might help (it needs to be near the top of the spreadsheet as DTS only looks at the first few rows to decide what the format of each column is - you can use this trick to also pursuade DTS that a particular column needs to be treated as TEXT and not VARCHAR(8000) :-)Kristen |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-04 : 16:13:50
|
| I tried changing the format of the column to text but still during the DTS transformation it shows the field source as a double. And this is why the text value in one of the cells in the column will not get exported to table from excel.??Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-05 : 04:34:42
|
| Did you try some non-numeric values in a dummy ROW 2?Kristen |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-05 : 04:36:52
|
| Hi,Please explain what you mean?Thanks |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-05 : 04:43:38
|
quote:
Originally posted by Kristen
Did you try some non-numeric values in a dummy ROW 2?Kristen
I think I know what you mean now.In row 2 of the excel sheet I entered the character t in all the cells.Still no difference because one of the columns (ManagerID) only shows the numbers and not the text values when being previewed in DTS.Thanks |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-05 : 04:43:45
|
quote:
Originally posted by Kristen
Did you try some non-numeric values in a dummy ROW 2?Kristen
I think I know what you mean now.In row 2 of the excel sheet I entered the character t in all the cells.Still no difference because one of the columns (ManagerID) only shows the numbers and not the text values when being previewed in DTS.Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-05 : 05:50:56
|
| "In row 2 of the excel sheet I entered the character t in all the cells"Yup, that's what I meant. I had hoped that would cause DTS to consider it to be a text column.Save the Excel XLS as a CSV and go from there? Have you got cells that contain TABs or LINE BREAKS? If NOT then that might work OK. What a PITA though ...I've mentioned before that I think DTS is a real nuisance for import from XLS - yet even with that we keep using it as a "convenient way to get data from a client" :-(Kristen |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2005-07-05 : 07:32:53
|
| Hi all,I managed to work this one out.It was a combination of registry and DTS settings.You ned to use IMEX=1 in the DTS settingThank you all for your time. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-07-05 : 07:56:58
|
| Can't remember where I got it from, but I remember reading a set of best practice guidelines from Microsoft where they recommended always converting Excel to text and using BCP as opposed to a datapump. This is the approach we adopt and it does save a lot of grief!Mark |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-05 : 08:50:39
|
| Beware that the registry setting needs to be set on the appropriate machine(s) - kinda accident waiting to happen when it is run from a not-usual machine :-(Kristen |
|
|
|
|
incorrect source field datatype