Format Excel Types | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Format Excel Types

Hello, I am exporting some data via a DTS into excel. I have already created the excel file and it perfectly imports the data into that file. The problem is that it does not format the data properly. It thinks that everything is a varchar, although I formated the columns in my excel file as "number". The weird thing is that once the data is exported, and I go into one of those cells, and go to "format cells", then I can still see that the column is formatted properly, but the data is displayed as a varchar. I would appreciate if someone could help me out? Thanks in advance.
Under the data transformation option why don’t you set the excel columns according to the table values? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Where can I do that?
I am creating the xls file a template and an active x script. Function Main() ‘Fill in the blanks here
Dim sTemplatePath, sTargetPath, sSourceFileName, sTargetFileName
sSourceFileName = "temp.xlt"
sTargetFileName = "Canada_Retail_Inventory.xls"
sTemplatePath = "C:"
sTargetPath = "C:" sTargetFileName = sTargetPath & year(now) & "-" & month(now) & "-" & day(now) – 1 & " " & sTargetFileName
DTSGlobalVariables("sFileName").Value = sTargetFileName
sSourceFileName = sTemplatePath & sSourceFileName Dim oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFileName, sTargetFileName
Set oFSO = Nothing set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections("Connection 2")
oConn.DataSource = DTSGlobalVariables("sFileName").Value
set oPackage = nothing
set oConn = nothing Main = DTSTaskExecResult_Success End Function
I dont see any option in the Transformations tab to set the data type. When I double-click on the arrow from the source to destination listbox, then I get another popup (Transformation Options), which doesnt show any datatype options either. I would appreciate if anyone could help me that. I have been working the complete day on that. Thanks
On the "Select Source Tables and Views" screen, you have three columns for each table: Source, Destination and Transform. On the Transform column for selected tables, there’s a button showing. Click that button and make the necessary changes here.
]]>