I wrote a short function with Oledb which should read large amount of data but there are still several issues I couldnt't solve, it's about reading and inserting semicolon separated data in sql database
Private Function GetCSVFile(ByVal file As String) As DataTable
Try
Dim dt As New DataTable
Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox1.Text & ";Extended Properties=""TEXT;HDR=Yes;FMT=Delimited"""
Dim conn As New OleDb.OleDbConnection(ConStr)
Dim da As New OleDb.OleDbDataAdapter("Select * from " & _table & ".csv", conn)
da.Fill(dt)
Application.DoEvents()
getData = dt
Catch ex As OleDbException
MessageBox.Show(ex.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Return getData
End Function
1. it reads whole file but i need to tell the function that it should read only 50.000 rows and pass them to another function step for step, it should probably better work in for loop because jet oledb doesn't read files larger than 1 GB
- i need to replace characters
value(i) = value(i).Replace("\t", Constants.vbTab).Replace("\n", Constants.vbLf).Replace("\r", Constants.vbCr).Replace("\""", """").Replace("\\", "\")
but it usually works only for strings
I need to recognize data types like integer, double, string etc. my first idea was to do it over sql query and check the tables over tryparse
Dim dtInserts As DataTable = db.GetDataTable("SELECT TOP 0 * FROM " & _table) Dim ListOfTypes As New List(Of System.Type) For Each _col As DataColumn In dtInserts.Columns Dim _type As System.Type = _col.DataType ListOfTypes.Add(_type) Next Dim _wert1 As String = "11.11.2011" Dim _type1 As System.Type = ListOfTypes.Item(1) If DateTime.TryParse(_wert1, New Date) Then End If
but still not sure if it will work
All data that have been read should be encoded in 1252 Codepage. this one doesn't really work
Dim ConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox1.Text & ";Extended Properties=""TEXT;HDR=Yes;FMT=Delimited;CODEPAGE=1252"""
anyone idea what could be done?
Application.DoEvents()
- it can make strange things happen. – Andrew Morton