Geeks With Blogs
Chris B's Blog My Blog about Hobby Electronics

Introduction

 

I’ve been asked on several occasions to export data in a format that can be easily loaded into Excel or parsed by a simple application.  CSV is an excellent format since it’s widely recognized and supported by Excel.  In this article, I’ll provide a simple way to build a string containing the data in CSV format.  As a bonus, I’ll cover saving the returned string to a file and include the application that I originally wrote the code for.

 

The Code

 

Below you will find a fairly basic function that will take a given DataSet, loop through the data and produce CSV formatted data.  One thing that you might notice is that I’ve used a StringBuilder.  Using string concatenation wouldn’t be an issue if you are expecting a small number of records, but using the StringBuilder class allows us to process a large number of records without causing a memory issue.  You may also notice that the Delimiter is a parameter of the function, rather than being a hard-coded value.  Space and Tab delimited data is also common, so I thought it would be a good idea to make the function flexible.  Additionally, you should notice that it conditionally exports the column names.  If you are using Excel, this would allow you to import the data with column names, making it easier to read.

 

From there, the function simply loops through the columns in all of the available rows and appends them to the StringBuilder.  Please note that this code requires you to import the System.Text namespace for the StringBuilder class.

 

    Public Function DelimitData(ByVal Data As DataSet, ByVal Delimiter As String, ByVal NoHeader As Boolean) As String

        Dim sbDelimited As New StringBuilder()

        If Data.Tables.Count > 0 Then

            With Data.Tables(0)

                If Not NoHeader Then

                    For iCols As Integer = 0 To (.Columns.Count - 1)

                        If iCols <> 0 Then

                            sbDelimited.Append(Delimiter)

                        End If

                        sbDelimited.Append(.Columns(iCols).ColumnName)

                    Next

                    sbDelimited.AppendLine()

                End If

                For iRow As Integer = 0 To (.Rows.Count - 1)

                    For iCol As Integer = 0 To (.Columns.Count - 1)

                        If iCol <> 0 Then

                            sbDelimited.Append(Delimiter)

                        End If

                        sbDelimited.Append(.Rows(iRow)(iCol))

                    Next

                    sbDelimited.AppendLine()

                Next

            End With

        End If

        Return sbDelimited.ToString()

    End Function

 

Exporting the CSV to a File

 

Once again, this is a very simple block of code.  We’ll make use of a simple StreamWriter to output the string to a file.  I attempted to make use of a function in the new My namespaces (My.Computer.FileSystem.WriteAllText), but ended up with some strange characters in the first line of the file.  However, this method appears to work without any issues.

 

        Dim sOutput As String = ""

        sOutput = DelimitData(dsData, sDelim, bNoHeader)

        Dim sw As New StreamWriter(sOutputFile, False)

        sw.Write(sOutput)

        sw.Close()

 

Additional Applications

 

The reason why I wrote the above blocks of code was because we needed a simple console application that could run a query against an access database at a scheduled interval.  It’s a pretty simple application and might be of use to someone, so I’ll include the code below.  It is also available for download here.

 

Imports System.Data

Imports System.Data.OleDb

Imports System.IO

Imports System.Text

 

 

Module Export

 

    ''' <summary>

    ''' Main execution function

    ''' </summary>

    ''' <param name="cmdArgs">Command line arguments</param>

    ''' <remarks></remarks>

    Sub Main(ByVal cmdArgs() As String)

        Try

            Trace.Listeners.Add(New TextWriterTraceListener(My.Application.Info.DirectoryPath() + "\Export.log"))

            Trace.AutoFlush = True

            Console.WriteLine("Log file - " + My.Application.Info.DirectoryPath() + "\Export.log")

        Catch ex As Exception

            Console.WriteLine("Unable to start log file")

            Console.WriteLine(ex.ToString())

        End Try

        Dim sDelim As String = ","

        Dim sAccessFile As String = ""

        Dim sQuery As String = ""

        Dim sOutputFile As String = "Export.csv"

        Dim bNoHeader As Boolean

        Console.WriteLine("Starting" + Date.Now.ToString())

        Trace.WriteLine("Starting" + Date.Now.ToString())

        For i As Integer = 0 To (cmdArgs.Length - 1)

            Console.WriteLine(cmdArgs(i))

            If String.Compare(cmdArgs(i), "/file", True) = 0 Then

                If i + 1 < cmdArgs.Length Then

                    sAccessFile = cmdArgs(i + 1)

                End If

            ElseIf String.Compare(cmdArgs(i), "/query", True) = 0 Then

                If i + 1 < cmdArgs.Length Then

                    sQuery = cmdArgs(i + 1)

                End If

            ElseIf String.Compare(cmdArgs(i), "/export", True) = 0 Then

                If i + 1 < cmdArgs.Length Then

                    sOutputFile = cmdArgs(i + 1)

                End If

            ElseIf String.Compare(cmdArgs(i), "/d", True) = 0 Then

                If i + 1 < cmdArgs.Length Then

                    sDelim = cmdArgs(i + 1)

                End If

            ElseIf String.Compare(cmdArgs(i), "/noheader", True) = 0 Then

                bNoHeader = True

            ElseIf cmdArgs(i)(0) = "/"c Then

                'must be a sql parameter

                If i + 1 < cmdArgs.Length Then

                    sQuery = sQuery.Replace("<" + cmdArgs(i).Remove(0, 1) + ">", cmdArgs(i + 1))

                End If

            End If

        Next

        sQuery = sQuery.Replace("<date>", Date.Now.ToShortDateString)

        If sAccessFile = "" Or sQuery = "" Then

            Console.WriteLine("Missing Parameters.  Syntax:")

            Console.WriteLine("/file c:\exportTest.mdb /query ""select * from email"" /export file.csv(optional - default Export.csv) /d , (optional - default ,)")

        Else

            Try

                Dim sOutput As String = ""

                sOutput = DelimitData(GetData(sAccessFile, sQuery), sDelim, bNoHeader)

                Dim sw As New StreamWriter(sOutputFile, False)

                sw.Write(sOutput)

                sw.Close()

            Catch ex As Exception

                Console.WriteLine(ex.ToString())

                Trace.WriteLine(ex.ToString())

            End Try

            Trace.WriteLine("Done" + Date.Now.ToString())

            Console.WriteLine("Done" + Date.Now.ToString())

        End If

    End Sub

 

    ''' <summary>

    ''' Takes any data and formats it into a delimited string

    ''' </summary>

    ''' <param name="Data"></param>

    ''' <param name="Delimiter"></param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Public Function DelimitData(ByVal Data As DataSet, ByVal Delimiter As String, ByVal NoHeader As Boolean) As String

        Dim sbDelimited As New StringBuilder()

        If Data.Tables.Count > 0 Then

            With Data.Tables(0)

                If Not NoHeader Then

                    For iCols As Integer = 0 To (.Columns.Count - 1)

                        If iCols <> 0 Then

                            sbDelimited.Append(Delimiter)

                        End If

                        sbDelimited.Append(.Columns(iCols).ColumnName)

                    Next

                    sbDelimited.AppendLine()

                End If

                For iRow As Integer = 0 To (.Rows.Count - 1)

                    For iCol As Integer = 0 To (.Columns.Count - 1)

                        If iCol <> 0 Then

                            sbDelimited.Append(Delimiter)

                        End If

                        sbDelimited.Append(.Rows(iRow)(iCol))

                    Next

                    sbDelimited.AppendLine()

                Next

            End With

        End If

        Return sbDelimited.ToString()

    End Function

    ''' <summary>

    ''' Runs the requested query agains the provided Access database

    ''' </summary>

    ''' <param name="FileName">Location of the Access Database</param>

    ''' <param name="Query">Query to run</param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Public Function GetData(ByVal FileName As String, ByVal Query As String) As DataSet

        Dim sConnectString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName

        Dim dsData As New DataSet

        Try

            Console.WriteLine("Running Query " + Query)

            Trace.WriteLine("Running Query " + Query)

            Dim daAccess As New OleDbDataAdapter(Query, sConnectString)

            daAccess.Fill(dsData)

        Catch ex As Exception

            Trace.WriteLine(ex.ToString())

            Console.WriteLine(ex.ToString())

        End Try

        Return dsData

    End Function

 

End Module

 

Posted on Thursday, May 18, 2006 10:01 AM | Back to top


Comments on this post: Exporting data to CSV

# re: Exporting data to CSV
Requesting Gravatar...
Hi,
Dont take me the wrong way. I read your Blog .... and just thought it would be fun to do it in Visual Foxpro. Call me showing off but just want the C# coders to know what VFP programmers had at their finger tips since 1992 FoxPro for windows ecluding the OOP, that came in 1995 since release of Version 3. The meat and potaoes of this little app i wrote is :
COPY TO (.exportfile) TYPE CSV
thats it, all the other stuff is to show off OOP and other stuff...
Hope this gets you to explore VFP as a real alternative to Data Centric Applications..

Thanks
A

*---------------------------------------------------------------------

oCSV = Createobject("makecsv") && Create the MAKECSV Object
IF TYPE('oCSV') = 'O'
cSuccess = oCSV.dumpcsv("C:\Reports\Province\KZN\My_KZN_Customers","Select * From Customers Where Province = 'KZN'")
IF !Empty(lSuccess)
*- Yay We Can open It for user if thats the option as makescv returned empty string
ELSE
MESSAGEBOX(lSuccess,16,"Dump the CSV")
ENDIF
ELSE
*- Could not Create Object
ENDIF
RELEASE oCSV
*....
*....

*----- Stored Somewhere in Your library ----*
Define Class makecsv As Custom OLE Public
exportfile = ''
MyCursor = ''
PROCEDURE dumpcsv (paraFileName,paraSqlString)
WITH This
*------ Validate parameters
IF PARAMETERS() != 2 && Correct amount of parameters recieved ?
RETURN 'Invalid Amount Of Parameters'
ENDIF

cRtn =
*- Validate File Name 1.
IF TYPE('paraFileName') != 'C' && Data type of filename is string ?
RETURN 'Filename Not Correct Datatype'
ELSE
IF Empty('paraFileName') && Is the filename empty ?
.exportfile = SYS(2023) + '\' + SYS(2015) && Assign defualt name in temp folder !
ELSE
.exportfile = paraFileName
ENDIF
ENDIF

*------ Validate File Dump Folder. lets assume that there is a validate path METHOD
IF .ValidateFilePath(.exportfile) = .F.
RETURN "The Path Specified Does Not Exist"
ELSE
*- Continue
ENDIF

*- Validate SQl String
IF TYPE('paraSqlString') != 'C' && Data type of filename is string ?
RETURN 'SQL Sring Not Correct Datatype'
ELSE
IF Empty('paraFileName') && Is the filename empty ?
RETURN 'SQL Sring Is Emtpy'
ELSE
*- Continue
ENDIF
ENDIF

*------ Validate SQL String .
IF .ValidateSQLString(paraSqlString) = .F.
RETURN "SQL String Not Valid"
ELSE
*- Continue
ENDIF

*------- OK Thus Far all peachy and we can get to the meat and potatoes of this Class -*
IF USED(.MyCursor)
SELECT (.MyCursor)
IF RECCOUNT(.MyCursor) = 0
RETURN "There Are No Records To Process"
ELSE
COPY TO (.exportfile) TYPE CSV && This is the Meat and potatoes, actuuly dont need all the other bloat
ENDIF
ELSE
RETURN "Long story Short, Something Weird ..."
ENDIF
*------

RETURN ''
ENDWITH


ENDPROC

PROCEDURE ValidateFilePath(paraParaFileName)
TRY
RETURN IIF(DIRECTORY(SUBSTR(paraparaFileName,1,ATC('\',paraparaFileName,OCCURS('\',paraparaFileName)))),.T.,.F.)
CATCH TO cError
*- We not gonning to use this erro thing now
ENDTRY
ENDPROC

PROCEDURE ValidateSQLString(paraParaSqlString)
*- 1. Lets assume u have your connect METHOD To Call
*- 2. if u need me to post it i will, but i am writing this on the fly 20 minutes has passed allready
*- 3. So some stuff was done here ...Connect, prepare the SQl, get the cursor and return true ...
This.MyCursor = 'Customers' && Store the name of your cursor ..
RETURN .T.
ENDPROC

PROCEDURE destroy
*- CLEAN up when this class is released
*- close your sql connect

IF USED(This.MyCursor)
USE IN This.MyCursor && Close your Cursor
ELSE
*-
ENDIF
ENDPROC
Enddefine
Left by Adrian on May 19, 2006 11:02 AM

# re: Exporting data to CSV
Requesting Gravatar...
Adrian,
Thanks for stopping by and leaving a comment!

Chris
Left by cubeberg on May 19, 2006 11:31 AM

Your comment:
 (will show your gravatar)


Copyright © Christopher Berg | Powered by: GeeksWithBlogs.net