SQL Server Performance

DTS and Excel

Discussion in 'SQL Server DTS-Related Questions' started by Bartuls, Jan 9, 2004.

  1. Bartuls New Member

    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
  2. satya Moderator

    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.
  3. Bartuls New Member

    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.
  4. satya Moderator

    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.
  5. Bartuls New Member

    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!

  6. satya Moderator

    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.
  7. satya Moderator

    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.
  8. Bartuls New Member

    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!
  9. Bartuls New Member

    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.....
  10. satya Moderator

    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.
  11. Dave Wells New Member

    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.
  12. cnu77 New Member

    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

Share This Page