SQL Server Performance Forum – Threads Archive
Append Excel data to existing tableHi 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
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
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
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
Do you have something like this http://www.wizny.ps.pl/dts1.png)? — Marek ‘chopeen’ Grzenkowicz, MCP
Yes. How do I select the fields from the Excel sheet to append to the SQL table? Regards
http://www.wizny.ps.pl/dts2.png — Marek ‘chopeen’ Grzenkowicz, MCP
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.
If DTSSource("column1") <> "" Then
DTSDestination("age") = DTSSource("column1")
DTSDestination("age") = Now
End If Main = DTSTransformStat_OK
End Function — Marek ‘chopeen’ Grzenkowicz, MCP
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
Not to worry Chopeen – I worked out how to achieve this. Now to inspect the results!!
Thanks for your help. Kind Regards
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:
DTSDestination("name") = "hello" Main = DTSTransformStat_OK
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
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
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
I was able to work something out… Thanks for all your help. The DTS is a very good tool!!