Format not correct when exporting to a CSV file

Good Afternoon,

I am developing an application in VB.Net where when a button is pressed values from an Access 2010 table are exported to a CSV (Comma delimited) file.

The problem I am experiencing is that the values of the below columns are not being formatted correctly when they are exported to the CSV.

ChqNum - Does not display leading 0's
Amount - Not displaying 2 decimal places when whole number (Example: Displays 456 when should really display as 456.00)

When I look at the values of the dataset using a breakpoint, the values are fine (below):

dataset
But when they are written to the CSV file they are no longer formatted.

CSV
Is anyone able to advise how I can fix this?

Code is below:

Imports System.Data.OleDb
Imports System.IO
Imports System.Data
Imports Microsoft.Win32
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class frmMain

    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click

        'Initialize the objects before use
        Dim dataAdapter As New OleDbDataAdapter
        Dim dataSet As New DataSet
        Dim command As New OleDbCommand
        Dim dtMain As New System.Data.DataTable()
        Dim conn As New OleDb.OleDbConnection

        'Assign connection string to connection object
        conn.ConnectionString = sConnString()
        command.Connection = conn
        command.CommandType = CommandType.Text

        'Run query
        command.CommandText = "SELECT Format(ChqNum, '000000') As ChqNum, Format(ChqDate, 'mm/dd/yyyy') As ChqDate, Format(Amount, 'Standard') As Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit, Format(Voided, 'mm/dd/yyyy') As Voided FROM tblJBRHist"
        dataAdapter.SelectCommand = command

        Try

            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")

            Dim oExcel As Excel.Application
            Dim oBook As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add(Type.Missing)
            oSheet = oBook.Worksheets(1)

            Dim dc As System.Data.DataColumn
            Dim dr As System.Data.DataRow
            Dim colIndex As Integer = 0
            Dim rowIndex As Integer = 0

            'Fill data to datatable
            conn.Open()
            dataAdapter.Fill(dtMain)
            conn.Close()

            'Export the Columns to CSV file
            For Each dc In dtMain.Columns
                colIndex = colIndex + 1
                oSheet.Cells(1, colIndex) = dc.ColumnName
            Next

            'Export the rows to CSV file
            For Each dr In dtMain.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each dc In dtMain.Columns
                    colIndex = colIndex + 1
                    oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                Next
            Next

            'Set final path
            Dim fileName As String = "\ExpJBRHistory" + ".csv"
            Dim finalPath = sExportString() + fileName

            'Save file in final path
            oBook.SaveAs(finalPath, XlFileFormat.xlCSV, Type.Missing, _
            Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, _
            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

            'Release the objects
            ReleaseObject(oSheet)
            oBook.Close(False, Type.Missing, Type.Missing)
            ReleaseObject(oBook)
            oExcel.Quit()
            ReleaseObject(oExcel)
            
            MessageBox.Show("Export done successfully!")

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)

        End Try

    End Sub

    Private Sub ReleaseObject(ByVal o As Object)

        Try

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)

            End While

        Catch

        Finally

            o = Nothing

        End Try
   
   End Sub
   
End Class

Open in new window


Kind Regards,
N
LVL 1
KevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

F IgorDeveloperCommented:
Opening a CSV file in excel leads to a auto-formatting texts that appears as numbers.
(CSV does not define the format only the values)
You can try to enclose all values between "" for each fields you need
0
KevinAuthor Commented:
Thank you for your response fraigor.

However opening the file in notepad after exporting it again is giving the same result.

notepad
What I don't understand is why the dataset would pull the correct format and not the export in to the file.
0
F IgorDeveloperCommented:
Try to change the format of cell before setting the value




Se this example:
http://www.aspose.com/docs/display/cellsnet/Setting+Display+Formats+of+Numbers+and+Dates

From line 61 change to:

Dim style As Style = oSheet.Cells(rowIndex + 1, colIndex).GetStyle()
if dc.ColumnName = "ChqNum" Then
style.Number = 49
End If
 oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
...

Open in new window


When you post to excel it's setting to format "general" instead of "text" (style=49) for the column ChqNum.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

KevinAuthor Commented:
Fraigor,

Am getting the below error when I add the above code.

error
I'm wondering something, do I need to obtain the "Apose.Cells" API to get this to work with the code you provided?

Kindly advise.

Regards,
N
0
F IgorDeveloperCommented:
Try Changing

Dim style As Style = oSheet.Cells(rowIndex + 1, colIndex).GetStyle()
if dc.ColumnName = "ChqNum" Then
style.Number = 49
End If

Open in new window


to

if dc.ColumnName = "ChqNum" Then
  oSheet.Cells(rowIndex + 1, colIndex).CellFormat.FormatString = "#######"
End If

Open in new window



See https://documentation.devexpress.com/#WindowsForms/CustomDocument2141 to select the best format you need
0
KevinAuthor Commented:
Thanks Fraigor, but it looks as though you also have to purchase this one (DevExpress) to be able to use it.

Are you able to maybe provide an alternative method altogether to what I am using for exporting an access 2010 table to a CSV (comma delimited) file? Maybe my approach is not possible without purchasing something, which is not an option for me.

Kindly advise.

Regards,
N
0
F IgorDeveloperCommented:
There is no need of additional tools. The link is only for rerefence in different formats you could use. Try this modifications  in code:

            'Export the rows to CSV file
            For Each dr In dtMain.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each dc In dtMain.Columns
                    colIndex = colIndex + 1
                    if dc.ColumnName = "ChqNum" Then
                      oSheet.Cells(rowIndex + 1, colIndex).CellFormat.FormatString = "000000"
                    End If
                    if dc.ColumnName = "Amount" Then
                      oSheet.Cells(rowIndex + 1, colIndex).CellFormat.FormatString = "0.00"
                    End If
                    oSheet.Cells(rowIndex + 1, colIndex) = dr(dc.ColumnName)
                Next
            Next

Open in new window




Also if you want quickly a CSV file you could write it directly to file using the DataReader you could try this code, Using a StreamWriter to the CSV file (Please check syntax and try)

Imports System.Data.OleDb
Imports System.IO
Imports System.Data
Imports Microsoft.Win32
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

Public Class frmMain

    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click

        'Initialize the objects before use
        Dim dataAdapter As New OleDbDataAdapter
        Dim dataSet As New DataSet
        Dim command As New OleDbCommand
        Dim dtMain As New System.Data.DataTable()
        Dim conn As New OleDb.OleDbConnection

        'Assign connection string to connection object
        conn.ConnectionString = sConnString()
        command.Connection = conn
        command.CommandType = CommandType.Text

        'Run query
        command.CommandText = "SELECT Format(ChqNum, '000000') As ChqNum, Format(ChqDate, 'mm/dd/yyyy') As ChqDate, Format(Amount, 'Standard') As Amount, PayTo, Reference, DebitAcc, CreditAcc, Currency, PayeeBank, Unit, Format(Voided, 'mm/dd/yyyy') As Voided FROM tblJBRHist"
        dataAdapter.SelectCommand = command

        Try

            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")


            Dim dc As System.Data.DataColumn
            Dim dr As System.Data.DataRow
            Dim colIndex As Integer = 0
            Dim rowIndex As Integer = 0

            'Fill data to datatable
            conn.Open()
            dataAdapter.Fill(dtMain)
            conn.Close()

            'Set final path
            Dim fileName As String = "\ExpJBRHistory" + ".csv"
            Dim finalPath = sExportString() + fileName
			
			
			Dim objWriter As New System.IO.StreamWriter( finalPath )
			DIm separator as String = ";"

			objWriter.Write( TextBox1.Text )

            'Export the Columns to CSV file
            For Each dc In dtMain.Columns
                colIndex = colIndex + 1
				If colIndex > 1 Then 
					objWriter.Write( separator )
				EndIf
                objWriter.Write( dc.ColumnName )
            Next
			objWriter.WriteLine( "" )
			

            'Export the rows to CSV file
            For Each dr In dtMain.Rows
                rowIndex = rowIndex + 1
                colIndex = 0
                For Each dc In dtMain.Columns
                    colIndex = colIndex + 1
					If colIndex > 1 Then 
						objWriter.Write( separator )
					EndIf
                    objWriter.Write( dr(dc.ColumnName) )
                Next
				objWriter.WriteLine( "" )
            Next

			objWriter.Close()			

            MessageBox.Show("Export done successfully!")

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK)

        End Try

    End Sub

    Private Sub ReleaseObject(ByVal o As Object)

        Try

            While (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0)

            End While

        Catch

        Finally

            o = Nothing

        End Try
   
   End Sub
   
End Class

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinAuthor Commented:
Hi fraigor,

Thank you for your response.

I can't seem to get the first set of code to work with the "CellFormat..."

Keep getting the below warning when run:

RangeNotFound
The reason I am assuming that something needs to be referenced with another tool or something is because intellesence does not have an item for "CellFormat" only the below are available.

menu
So i'm assuming this is why I get that out of range error, because there is no reference for it.

However, your alternative with using the streamwriter for the export works like a charm. So will go with that approach since that works. Also I noticed with my previous approach that the Excel process would hang for whatever reason each time the export completed. So will scrap my initial approach all together.

Thanks very much for your help.

Kind Regards,
N
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.