Single Quote included with results to Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Single Quote included with results to Excel

Author

Topic

dhumphries
Starting Member

18 Posts

Posted - 2009-01-26 : 12:56:27

I Have a VB script I am running to update an Excel file from Visual Studios fro applicaitons. It works great with one exception. Every feild I send out to Excel is proceeded by a single quote so the date 12/01/09 is in the cell as '12/01/09. This is wreaking all kinds of havoc. I am doing this in kind of a strange manner but I want to return a table of values from a sql statement and then update an Excel spreadsheet. I also need to be ablewrite formulas to the spreadsheet as I go. If there is a better way to do this I am all ears. If you know why I get a single quote I would be greatly appreciative. I originally set this up using Excel Object code with a reference to the Interop so I could update cells directly and that works a lot better but I am told that we can not install office on the server so I have to go to plan B becuase this has to be a runnable job from the server.Here is the code.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.Data.SqlClient

Public Class ScriptMain

Public Sub Main()

' This will set the file and get ready for update.
Dim dt As Date = DateTime.Now
Format(dt, "MMddyy")
dt.ToString("MMddyy")
Dim runType As String = CType(Dts.Variables("User::GTN_RUN_NO").Value, String)
Dim Path As String
If runType = "20200" Then
FileCopy("c:\PayrollBalancing\MasterSheets\CWLP Payroll Fund Master.xls", "C:\PayrollBalancing\CWLP_Fund" & Format(dt, "MMddyy") & ".xls")
Path = "C:\PayrollBalancing\CWLP_Fund" & Format(dt, "MMddyy") & ".xls"
Else
FileCopy("c:\PayrollBalancing\MasterSheets\City Payroll Fund Master.xls", "C:\PayrollBalancing\City_Fund" & Format(dt, "MMddyy") & ".xls")
Path = "C:\PayrollBalancing\City_Fund" & Format(dt, "MMddyy") & ".xls"
End If

' This will Fill the array needed to export to Excel
'These are the connection to get the list of parameters from Ded_plan
Dim Conn_getList As SqlConnection
Dim SqlSelect_getList As SqlCommand
Dim getListDS As New DataSet()
Dim getListreader As SqlDataReader

Dim ConnString_getList As String = "Data Source=gthinterpdb1\erptesta;Initial Catalog=ADV3HRMCONV;Integrated Security=True;"

'Create SQL adaptor and populate it
Dim SelectStatement_getList As String = "select fund_cd,Unit_cd,Sum(Exps_amt) as Exps_amt," + _
"Sum(frng_amt) as frng_amt from " + _
"(" + _
"Select " + _
"fund_cd,Unit_cd,cntrct_pay_am as Exps_amt,0 as frng_amt " + _
"from pyrl_exps_jrnl " + _
"UNION ALL " + _
"Select " + _
"fund_cd,Unit_cd,0 as Exps_amt,frng_am as frng_amt " + _
"from pyrl_frng_jrnl " + _
") x" + _
" GROUP BY fund_cd,Unit_cd " + _
"order by fund_cd,Unit_cd "

Dim adaptor As SqlDataAdapter = New SqlDataAdapter(SelectStatement_getList, ConnString_getList)
Dim cmdBuilder As SqlCommandBuilder = New SqlCommandBuilder(adaptor)
Conn_getList = New SqlConnection(ConnString_getList)
Conn_getList.Open()
SqlSelect_getList = New SqlCommand(SelectStatement_getList, Conn_getList)
getListreader = SqlSelect_getList.ExecuteReader
adaptor.Fill(getListDS)
Dim I As Integer
Dim chk As Integer
Chk = getListDS.Tables(0).Rows.Count - 1

Microsoft.Office.Interop.Excel.Worksheet)
Dim x As Integer = 3
Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim myCommand As New System.Data.OleDb.OleDbCommand
Dim sql As String
MyConnection = New System.Data.OleDb.OleDbConnection _
("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + _
Path + ";Extended Properties=Excel 8.0")

MyConnection.Open()

For I = 0 To getListDS.Tables(0).Rows.Count - 1

Dim Fund_cd As String = CType(getListDS.Tables(0).Rows(I).Item(0), String)
Dim Unit_cd As String = CType(getListDS.Tables(0).Rows(I).Item(1), String)
Dim Exps_amt As String = CType(getListDS.Tables(0).Rows(I).Item(2), String)
Dim Frng_amt As String = CType(getListDS.Tables(0).Rows(I).Item(3), String)
Try

myCommand.Connection = MyConnection
sql = "insert into [CWLP$] (Fund, Unit, Expense, Fringe, Total) values('" & Fund_cd & "','" & Unit_cd & "'" + _
", " & Exps_amt & ", " & Frng_amt & "" + _
",'=d" & x & "+e" & x & "')"
myCommand.CommandText = sql
myCommand.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try

x = x + 1
Next
MyConnection.Close()
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

mfemenel
Professor Frink

1421 Posts

Posted - 2009-01-26 : 14:38:35

A single quote in excel indicates that the field should be treated as text. I'm guessing your dt.ToString() line is the culprit.

Mike
"oh, that monkey is going to pay"

dhumphries
Starting Member

18 Posts

Posted - 2009-01-26 : 14:44:55

quote:
Originally posted by mfemenel

A single quote in excel indicates that the field should be treated as text. I'm guessing your dt.ToString() line is the culprit.

Mike
"oh, that monkey is going to pay"

The dt.ToString() line should have nothing to do with the problem. The variables being written to Excel are farther down. It is those values that are being added with a quote. for example you may see in the update I am setting the value of Total to =dx+ex. x is a value that is updated in the for loop. So in this case the cell would look like =d3+e3 but what is written to the cell is '=d3+e3. It does this on every single value written to Excel. I did just notice something though as I have been playing with this that if I create a row in spreadsheet with all the values as I want them the update then uses the correct format and writes every record without the single qute however if I delete that first row I return to the single quote on every value and every cell.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources