Home » Microsoft TechnologiesRSS

Create DataTable from CSV

So I'm sure this sample code exists somewhere, but at this point I just need some guidance. What I am trying to do is import all of the data from a CSV I have (it's about 100 columns of data that are numbers, not strings, and have headers) and create a DataTable so that I can plot to a 3rd party chart control called Zedgraph. The problem is that when I plot columns from the DataTable that is created by the following code, it simply plots a diagonal line of the points count (in other words, if I create a curve from a column containing 400 data points, it plots a line from 0,0 to 400,400, instead of plotting the actual data). What I think I have finally figured out is that I'm importing the data as String, rather than Double or whatever other number type I could use. The problem is that I cannot seem to get any OLEDB connection to actually fill my dataset, so I have had to stick with this code for now. FYI, I am using Visual Basic Express 2010 and the file is a CSV created from Excel 2007. Thanks for any sample code you can lead me to or edits to the code to make this work.

 

Imports ZedGraph

Public Class Form1

    Private Sub CreateDataset(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DataSet As New DataSet
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            DataSet = getCsvToDataset(OpenFileDialog1.FileName)
        End If

        Call ChartFill(DataSet)

    End Sub

    Private Sub ChartFill(ByVal DS As DataSet)
        Dim ChartPane As GraphPane = ZedGraphControl1.GraphPane
        Dim dspl As New DataSourcePointList
        With dspl
            .DataSource = DS.Tables(0)
            .YDataMember = "Engine RPM"
        End With

        Dim line1 As LineItem
        line1 = ChartPane.AddCurve("Engine RPM", dspl, Color.Aquamarine)

        ChartPane.AxisChange()

    End Sub

    Private Shared Function getCsvToDataset(ByVal strPath As String) As DataSet
        Dim strLine As String
        Dim strArray As String()
        Dim charArray As Char() = New Char() {","c}
        Dim ds As New DataSet()
        Dim dt As DataTable = ds.Tables.Add("TheData")
        Dim aFile As New IO.FileStream(strPath, IO.FileMode.Open)
        Dim sr As New IO.StreamReader(aFile)

        strLine = sr.ReadLine()
        strArray = strLine.Split(charArray)

        For x As Integer = 0 To strArray.GetUpperBound(0)
            dt.Columns.Add(strArray(x).Trim())
        Next

        strLine = sr.ReadLine()
        While strLine IsNot Nothing
            strArray = strLine.Split(charArray)
            Dim dr As DataRow = dt.NewRow()
            For i As Integer = 0 To strArray.GetUpperBound(0)
                dr(i) = strArray(i).Trim()
            Next
            dt.Rows.Add(dr)
            strLine = sr.ReadLine()
        End While
        sr.Close()
        Return ds
    End Function
End Class

 

2 Answers Found

 

Answer 1

Yes this code  exist in many ways, more persons have taken that long road.

You can also take an easy way using OleDB

http://www.vb-tips.com/dbpages.aspx?Search=csv

 

 

Answer 2

If you write a file called a Schema.ini in the same folder as the csv  file you can load it straight into a datatable  with the datatypes and headers you want.  Here is the documentation on Schema.ini is here http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx

Here is some sample  code to do it:


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        
Dim strCSVFile = "c:\temp\data.csv"
        Dim dtReturnData As New DataTable

        
If CreateSchema(strCSVFile) Then
            CSV2DataTable(strCSVFile, dtReturnData, "SELECT *"""""True)
        
End If


    End Sub

    Public Function CSV2DataTable(ByVal strFilename As StringByRef dtCSVData As DataTable, ByVal strSelectList As String, _
                                   
ByVal strWhere As StringByVal strGroupBy As StringByVal blnHeader As BooleanAs Boolean

        If Not System.IO.File.Exists(strFilename) Then
            Return False
        End If

        Dim strFullPath As String = System.IO.Path.GetFullPath(strFilename)
        
Dim strFile As String = System.IO.Path.GetFileName(strFilename)
        
Dim strDir As String = System.IO.Path.GetDirectoryName(strFilename)

        
Dim strConnection As String

        If blnHeader = True Then
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                        
"Data Source=" & strDir & "\;" & _
                                        
"Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"""
        Else
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       
"Data Source=" & strDir & "\;" & _
                                       
"Extended Properties=""text;HDR=No;FMT=Delimited(,)"""
        End If

        Dim conCSV As OleDb.OleDbConnection


        conCSV = 
New OleDb.OleDbConnection(strConnection)
        conCSV.Open()

        
Dim strSQL As String

        strSQL = strSelectList & "FROM [" & strFile & "]"

        If strWhere.Length > 0 Then
            strSQL = strSQL & " " & strWhere
        
End If

        If strGroupBy.Length > 0 Then
            strSQL = strSQL & " " & strGroupBy
        
End If

        Dim cmdCSV As New OleDb.OleDbCommand(strSQL, conCSV)

        
Try

            Dim daCSV As New OleDb.OleDbDataAdapter(cmdCSV)

            daCSV.Fill(dtCSVData)


        
Catch ex As Exception

            MessageBox.Show(ex.Message, 
"Error Importing Data", MessageBoxButtons.OK, MessageBoxIcon.Error)

        
Finally

            conCSV.Close()
            conCSV.Dispose()

        
End Try


        Return dtCSVData.Rows.Count > 0


    
End Function

    Private Function CreateSchema(ByVal strFileName As StringAs Boolean
        Dim ascii As System.Text.Encoding = System.Text.Encoding.ASCII
        
Dim swSchema As System.IO.StreamWriter = Nothing
        Dim blnReturn As Boolean

        Dim strSchemaPath As String = System.IO.Path.GetFileName(strFileName)

        
Try
            strSchemaPath = System.IO.Path.GetDirectoryName(strFileName) & "\Schema.ini"
            swSchema = My.Computer.FileSystem.OpenTextFileWriter(strSchemaPath, False, ascii)
            
Dim strFile As String = System.IO.Path.GetFileName(strFileName)

            swSchema.WriteLine(
"[" & strFileName & "]")
            swSchema.WriteLine(
"ColNameHeader=False")
            swSchema.WriteLine(
"Format=Delimited(,)")
            swSchema.WriteLine(
"Col1=Value1 Text")
            swSchema.WriteLine(
"Col2=Value2  Text")
            swSchema.WriteLine(
"Col3=SomeDate Date")
            swSchema.WriteLine(
"Col4=SomePoint1 Integer")
            swSchema.WriteLine(
"Col5=SomePoint2 Integer")

            
'Continue for all fields

            blnReturn = True

        Catch ex As Exception
            blnReturn = 
False
        Finally
            If swSchema IsNot Nothing Then
                swSchema.Close()
            
End If
        End Try

        Return blnReturn

    
End Function

 

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter