SQL Server Performance

Append Excel data to existing table

Discussion in 'SQL Server DTS-Related Questions' started by picses, Aug 23, 2004.

  1. picses New Member

    Hi Group,
    I have an existing Excel spreadsheet and I need to APPEND this information to a table in SQL.
    I would like to do some formatting around some of the date columns before I append the data.
    What is the best way to do this?

    Kind Regards
  2. picses New Member

    Sorry, I forgot to add that the column names in the Excel Spreadsheet are different to the column names in the SQL table. Also, they are not in the same order.

    Kind Regards
  3. chopeen Member

    Seems like you need a simple DTS package - a single Data Transformation Task will be able to do all you need.

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  4. picses New Member

    I've started to create a DTS Package. What task will I need to choose to achieve what I am after. I will need to do an insert into the SQL table. Will I need to place this table in the 'Package' window?

    Kind Regards
  5. chopeen Member

  6. picses New Member

    Yes. How do I select the fields from the Excel sheet to append to the SQL table?

    Regards
  7. chopeen Member

    http://www.wizny.ps.pl/dts2.png

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  8. picses New Member

    Thanks chopeen!! I seem to be getting somewhere. However, there are some validations which I need to carry out e.g if one of the date columns is blank in the source, I want to use a default date. How would I code this? I presume using the ActiveX Script option. What would my code look like?

    Kind Regards.
  9. chopeen Member

    Try this:
    Function Main()
    If DTSSource("column1") <> "" Then
    DTSDestination("age") = DTSSource("column1")
    Else
    DTSDestination("age") = Now
    End If

    Main = DTSTransformStat_OK
    End Function

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  10. picses New Member

    Thanks Chopeen!! That worked!!
    Just one more thing and I think the import will work - thanks to you!!

    I've got columns in the destination table (SQL) which needs to be populated with a default value - for all records. These columns are not present in the source table. How can I achieve this?

    One field will be populated with the current date, the other with 1's.

    Kind Regards
  11. picses New Member

    Not to worry Chopeen - I worked out how to achieve this. Now to inspect the results!!
    Thanks for your help.

    Kind Regards
  12. chopeen Member

    I've already wrote it, so I'll post it anyway.

    1. ActiveX Script task can have only destination column and no source column, so you can use a script like this:
    Function Main()
    DTSDestination("name") = "hello"

    Main = DTSTransformStat_OK
    End Function
    2. You can also add a DEFAULT constraint to the table definition:

    ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT (default_value)

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  13. picses New Member

    Thanks Chopeen.

    I've now posted the data. There are a couple of things which I will need to amend:

    1. I've got a field where the data should be 4 characters in length. For some records, the data is only 2 characters - never less than 2 characters. I want to pad these fields with 2 zero's at the start so if the data is 98, I want to post 0098.

    2. Some of the currency field values have a minus in front. I want to get rid of the minus sign.

    Kind Regards
  14. chopeen Member

    quote:1. I've got a field where the data should be 4 characters in length. For some records, the data is only 2 characters - never less than 2 characters. I want to pad these fields with 2 zero's at the start so if the data is 98, I want to post 0098.
    I do not know any padding function in VB or SQL, so I am afraid you will need to create your own one.


    quote:2. Some of the currency field values have a minus in front. I want to get rid of the minus sign.
    I think that it's gonna be easiest to use absolute value - there is an appropriate function both in VB and SQL.

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  15. picses New Member

    Thanks Chopeen,
    I was able to work something out...

    Thanks for all your help. The DTS is a very good tool!!

Share This Page