SQL Server Performance Forum – Threads Archive

Append Excel data to existing table

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

---

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
Poland

---

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
Poland

---

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
Poland

---

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.

---

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

---

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:
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

---

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

---

I do not know any padding function in VB or SQL, so I am afraid you will need to create your own one.
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

---

Thanks Chopeen,
I was able to work something out… Thanks for all your help. The DTS is a very good tool!!

---

]]>