CODEWITHSUNDEEP
vb.netcsvvb.net-2010
Talguy
Talguy

Reputation: 1095

Opening a CSV file as a OLEDB Connection converts file text to double

I'm opening up a couple of CSV files and reading them in as a DataTable per the example I found here. The issue I am running into it the basic query I'm using to import the data is converting the column of IP addresses into Doubles. So I want to read in 10.0.0.1 and it shows up as 10.001. How can I get this column to read in as a string? I would like to not double process the file if I can.

Query I'm using is basic and is as follows:

SELECT * FROM [ComputerList.csv]

Here is my function to open and read the CSV file into a DataTable

Public Function OpenFile(ByVal strFolderPath as String, ByVal strQuery as String) as DataTable
   Dim strConn as String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFolderPath & ";Extended Propteries=""text; HDR=Yes;FMT=Delimited"""
   Dim conn as OleDb.OleDbConnection = New OleDb.OleDbConnection(strConn)

   Try
      conn.Open()
      Dim cmd as OleDb.OleDbCommand = New OleDb.OleDbCommand(strQuery, conn)
      Dim da as OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()

      da.SelectCommand = cmd
      Dim ds as DataSet = New DataSet()
      da.Fill(ds)
      da.Dispose()

      return ds.Tables(0)
   Catch
      return Nothing
   Finally
      conn.Close()
   End Try

End Function

Upvotes: 0

Views: 2006

Answers (2)

Talguy
Talguy

Reputation: 1095

ok I tried variations of everyone's suggested and have settled on a hybrid between all of them. My goal was to read in a CSV file manipulate it in a DataTable form and them write it back out. Some of my CSV files had multiple lines with in a cell and some had deliminators within a cell. Below you can find my hybrid solution that utilized TextFieldParser to read in the file and break it up.

Public Function OpenFile(ByVal File as String, NyVal delim as String) as DataTable
   Dim dt as New DataTable()
   Dim firstline as Boolean = True
   Using MyReader as New Microsoft.VisualBasic.FileIO.TextFieldParser(File)
      MyReader.TextFieldType = FileIO.FieldType.Delimited
      MyReader.SetDelimiters(delim)
      Dim currentRow as String()

      While Not MyReader.EndOfData
         Try
            currentRow = MyReader.ReadFields()

            If firstline
               firstline = false
               For Each col in currentRow
                  dt.Columns.Add(New DataColumn(col.ToString(), System.Type.GetType("System.String")))
               Next
            Else
               dt.Rows.Add(currentRow.ToArray())
            End If
         Catch ex as Microsoft.VisualBasic.FileIO.MalformedLineException
            Console.WriteLIne("Line " + ex.Message + " is not valid and will be skipped")
         End Try
      End While
   End Using

   return dt
End Function

Upvotes: 0

Danny James
Danny James

Reputation: 254

i use this to read csv's and force all to string.

Public Function convert_csv_to_data_table(ByVal File As String, ByVal separator As String) As DataTable
    Dim dt As New DataTable
    Dim firstLine As Boolean = True
    If IO.File.Exists(File) Then
        Using sr As New StreamReader(File)
            While Not sr.EndOfStream
                If firstLine Then
                    firstLine = False
                    Dim cols = sr.ReadLine.Split(separator)
                    For Each col In cols
                        dt.Columns.Add(New DataColumn(col, GetType(String)))
                    Next
                Else
                    Dim data() As String = sr.ReadLine.Split(separator)
                    dt.Rows.Add(data.ToArray)
                End If
            End While
        End Using
    End If
    Return dt
End Function

EDIT:- this will only work with a separator btw

Upvotes: 1

Related Questions