leading zero when exporting to csv file

[explorer]

Please help! I've searched all around with no success. I am currently exporting dataset information to a csv file. one of the field has a leading zero, which is read correctly in the application. However, when it is exported into the csv file, the leading zero is dropped. Is there any way to keep that leading zero? Can I program it within the application so that the excel spreadsheet accepts it in as a string with the zero.

 

Thanks in advance.

[PlausiblyDamp]

What code are you using to write the CSV?

[explorer]

this is the code that was written.

 

Try

Dim cmd As New SqlCommand()

Dim dr As SqlDataReader

cmd.Connection = CSession.Database.Connection

cmd.CommandType = CommandType.Text

cmd.CommandText = "EXECUTE " & mkAPCInvoiceExportQuery & " '" & sCompany & "', " & sUserID

Dim iColumn As Integer

CSession.Database.OpenConnection()

dr = cmd.ExecuteReader

While dr.Read

For iColumn = 0 To dr.FieldCount - 1

If iColumn > 0 Then _HeaderData = _HeaderData & ","

_HeaderData = _HeaderData & dr.Item(iColumn)

Next

_HeaderData = _HeaderData & Chr(13) & Chr(10)

End While

dr.Close()

cmd = Nothing

 

Dim moExportProcess As New CExportProcess(miProcessId)

Dim sFileToSave As String

 

_HeaderExportFile = moExportProcess.HeaderFileName & "_" & Format$(sUserID, "0000") & "." & moExportProcess.HeaderFileExtension

moExportProcess = Nothing

 

Finally

CSession.Database.CloseConnection()

End Try

[PlausiblyDamp]

Which of the columns contains the leading zero? You may need to alter the loop to force the format for that particular column.

In Excel could you not set the format property of the column to display leading zeros?

[explorer]

column two is the column that contains the vendor number with the leading zero. although i can manually go into excel and format the sheet, we want to avoid the users from making any changes to the spreadsheet, as this could cause other problems.

 

i am pretty new at coding and fully understanding this process. how would i be able to stop the loop and what is the format syntax for the string?

 

thanks!

[PlausiblyDamp]

try something similar to the following - no promises (not near VS.Net at the moment, if it doesn't work reply and I can try later)

 

 

While dr.Read
   
           For iColumn = 0 To dr.FieldCount - 1
               
               Select Case iColumn
                   Case 0
                        _HeaderData = _HeaderData & dr.Item(iColumn)
                   Case 2
                       _HeaderData = _HeaderData & ","
                       Dim tmp As Integer
                       tmp = Integer.Parse(dr.Item(iColumn))
                       Dim str As String
                       str = tmp.ToString("00000")
                       _HeaderData &= str
                   Case Is > 0
                       _HeaderData = _HeaderData & ","
                       _HeaderData = _HeaderData & dr.Item(iColumn)
               End Select

           Next
           _HeaderData = _HeaderData & Chr(13) & Chr(10)
       End While

 

edit forgot about Case 0 ;)