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 String, ByRef dtCSVData As DataTable, ByVal strSelectList As String, _
ByVal strWhere As String, ByVal strGroupBy As String, ByVal blnHeader As Boolean) As 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 & vbCrLf & "FROM [" & strFile & "]"
If strWhere.Length > 0 Then
strSQL = strSQL & vbCrLf & strWhere
End If
If strGroupBy.Length > 0 Then
strSQL = strSQL & vbCrLf & 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 String) As 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
Submitted by Lee D. Johnson