SQL Server Performance Forum – Threads Archive

DTS and Excel

OK, my first post here….
SQL server 2000 developer
MS Excel 2002 I have an excel sheet with some header information in the first 8 rows.
I want to proces the data in the rows 9 and further using a DTS package.
In the package I defined two connection, one for the excel sheet and one for the database to put the data in.
Most of the processing is going well, but I run into a strange problem. Several of the columns contain numeric data (i.e. only numbers). The first 8 rows contain header info etc.
I use Transform Data Tasks to fill up the SQL server db from the excel sheet.
If I take a look in the excel sheet using the preview button in the Source tab of properties of a Transform Data Task, some of the columns in the excelsheet appear to be empty, others are correctly filled in the preview.
This problem seems to be cel related, because some of the cels in other columns in the preview are also empty…
In the actual excel sheet all columns are filled! I did check about a million times that the connection to the excel sheet pointed to the same sheet as I looked at using Excel…. Does anybody know how this is possible? Bart

Is the excel sheet is populated correctly after the package execution? (just deleted other duplicate post)
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for deleting second post, sorry for that, I was trying to delete it myself. The Excel sheet is the source, the sql-server datbase the destination.
The Excel sheet is correctly populated.
I do some extra validation in the Transform Data Tasks to check the integrity of the data provided in the excel sheet.
The problem is that I do not see all the data in the excel sheet in the Transform Data Tasks although that data is present in the sheet itself.

That may be some issue with the GUI tools provided and as long as the destination connection data is populated in a right way then ignore other simple issues. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Now you hit the problem! The destination db is not populated correctly.
Example:
In the Excelsheet is the following data: 1 aa 110
2 bb 123
3 ss 222
4 fd 232 In the Transform Data Task preview it shows like this: 1 aa 110
2 bb
3 ss 222
4 fd That seems to be the only data from the Excel sheet available in SQL Server, the missing numbers are not available and are presented as NULL values to the destination db!

Check status for those NULL populated columns default, if specified as NULL then if no data is available then it inserts as NULL.
~ What is the level of service pack on SQL & MS Office? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

BTW what do you define in Advanced transformation properties in the DTS package? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I am playing around with it.
I defined a new package.
Source is the Excel sheet, Destination a flat file.
Transform data Task is just dumping the excel sheet in the flat file. The Excel sheet looks like this: SubformulierAEB-A1111111
Rapportagedatum30-9-20031111111 Assets: investments in equity issued by non-residents (without ISIN)
Type of securitySector issuerCountry of residence of the issuerAsset
Position at the beginning of the month (market value)Changes during the monthPosition at the end of the month (market value)
TransactionsRevaluationOther changes (market value)
Purchases (market value)Sales (market value)Exchange rate changesPrice changes
EquityNFIAT3.829.98519639,74001.052.98204.902.607
EquityNFIBE13.123.507133616500833.807015.293.479
EquityNFICH5.123.902010.729-1121.349.24206.462.304
EquityNFIDE26.042.3721192868,750-29.468-892.778026.312.995
EquityNFIDK38.737.99200-25-18.494038.719.474 The flat file looks like this: "Rapportagedatum"|""|"30-9-2003"|1|""|1|1|1|1|1|1|||||||""|||
""|""|""||""|||||||||||||""|||
"Assets: investments in equity issued by non-residents (without ISIN)"|""|""||""|||||||||||||""|||
"Type of security"|"Sector issuer"|"Country of residence of the issuer"||""|||||||||||||""|||
""|""|""||"Changes during the month"|||||||||||||""|||
""|""|""||"Transactions"|||||||||||||""|||
""|""|""||"Purchases (market value)"|||||||||||||""|||
"Equity"|"NFI"|"AT"|3829984.8300000001|""|0|0|1052982.3500000001|0|4902606.9199999999|||||||0|""||0|
"Equity"|"NFI"|"BE"|13123507.039999999|""|0|0|833806.87|0|15293478.91|||||||0|""||0|
"Equity"|"NFI"|"CH"|5123902.0700000003|""|10728.5|-112.02|1349242.4299999999|0|6462303.9800000004|||||||0|""||0|
"Equity"|"NFI"|"DE"|26042372.390000001|""|0|-29468.049999999999|-892778.06999999995|0|26312995.019999996|||||||0|""||0|
"Equity"|"NFI"|"DK"|38737992.329999998|""|0|-24.84|-18493.869999999999|0|38719473.619999997|||||||0|""||0| The fifth column (purchases) shows a double double quote (empty) in the flat file, in the excel sheet it is filled correctly!
That is the problem I have!

I solved the problem by deleting lines 2 to 8 from the excel sheet.
I still do not have an explanation why this behavior occurs…..

Glad it resolved and apologies as I was away to update the post.
And as I specified this could be a flaky mode of Enterprise Manager which returns spooky results sometimes but whereas the original values are propogated without any issues. What level of service pack and MDAC version used? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Excel uses the first 5 rows in a column to determine the data type of the column. So if the first 5 rows were 1
2
3
4
5 the datatype is then an integer. If the rest of the rows had string data they would be treated as NULL by the excel datasource. it’s a pain in the backside. Blank rows get assumed to be strings too.

The Excel sheet takes the number with a quote before. For eg., "5" is treated as text and "’5" as Number 5. Try updating the Numeric value in the Text box provided in the Top instead of DIRECTLY UPDATING in the CELL Itself…
Seenu

]]>