The following will hopefully get you started:
Export contents of table to CSV file.
'Open a connection to the database
Dim connectionString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("test.mdb")
Dim dbConnection As OleDbConnection
Dim dbCommand As OleDbCommand
Dim dbAdapter As OleDbDataAdapter
Try
'Open the connection
dbConnection = New OleDbConnection(connectionString)
dbConnection.Open()
'Retrieve all data from the Customers table.
dbCommand = New OleDbCommand("SELECT * FROM Customers", dbConnection)
dbAdapter = New OleDbDataAdapter
dbAdapter.SelectCommand = dbCommand
'Populate a DataTable with the contents of the Customers table.
Dim dbTable As New DataTable
dbAdapter.Fill(dbTable)
'Create the CSV file with column headers first.
Dim sw As New System.IO.StreamWriter(Server.MapPath("CSVTest.csv"))
Dim columnHeaders As New System.Text.StringBuilder
For Each col As DataColumn In dbTable.Columns
If columnHeaders.ToString.Length > 0 Then columnHeaders.Append(",")
columnHeaders.Append("""" & col.ColumnName & """")
Next
'Write out the column headers
sw.WriteLine(columnHeaders.ToString)
'Add the data
Dim dataValues As New System.Text.StringBuilder
For Each row As DataRow In dbTable.Rows
For Each col As DataColumn In dbTable.Columns
If dataValues.ToString.Length > 0 Then dataValues.Append(",")
dataValues.Append("""" & row(col.ColumnName).ToString & """")
Next
sw.WriteLine(dataValues.ToString)
dataValues = New System.Text.StringBuilder
Next
sw.Close()
Catch ex As OleDbException
Response.Write(ex.Message)
Catch ex As InvalidOperationException
Response.Write(ex.Message)
Catch ex As Exception
Response.Write(ex.Message)
Finally
If Not dbConnection Is Nothing Then dbConnection.Dispose()
If Not dbAdapter Is Nothing Then dbAdapter.Dispose()
If Not dbCommand Is Nothing Then dbCommand.Dispose()
End Try
This next chunk of code will import a csv file into a table. Note however that there is some limitation to this code as it stands. Firstly, it doesn’t determine the data type of the field that the data will be stored in and thus treats all fields as text. You will need to come up with some logic that will query the structure of the target table for each field name and act accordingly. The DataColumn object will be of use here. The next limitation to this code is that if the data contains a comma, it will result in an SQL syntax error. Again, you will need to come up with logic to handle this. My personal preference would be to export the data using something other than a comma as the delimiter (possibly a tilde ~ or other such character that is not regularly used) but this depends on your situation.
Import CSV file
'Open a connection to the database
Dim connectionString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("test.mdb")
Dim dbConnection As OleDbConnection
Dim dbCommand As OleDbCommand
Dim dbAdapter As OleDbDataAdapter
Try
'Open the connection
dbConnection = New OleDbConnection(connectionString)
dbConnection.Open()
'Retrieve the columns for the table that the CSV file will be imported to.
Dim dbTable As DataTable
Dim tableName As String = "TestImport" 'Rename to table where data will be imported too.
dbTable = dbConnection.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, New Object() {Nothing, Nothing, tableName, Nothing})
Dim tableColumnNames As New ArrayList
For Each row As DataRow In dbTable.Rows
tableColumnNames.Add(row("COLUMN_NAME"))
Next
'Open the CSV File
Dim sr As New System.IO.StreamReader(Server.MapPath("CSVTest.csv"))
Dim csvHeadings() As String
Dim csvData() As String
Dim lineCount As Integer
'Read the CSV File
Do While sr.Peek <> -1
'If this is the first line then read the headings and determine if they match the table
'that this file is to be imported into.
If lineCount = 0 Then
csvHeadings = sr.ReadLine.Replace("""", "").Split(Char.Parse(","))
'First ensure that both the csvHeadings array and the tableColumnNames array
'have the same length. If so, then enter a for loop ensuring that each field in
'the csvHeadings array exists in the tableColumnNames array.
If csvHeadings.Length <> tableColumnNames.Count Then
Exit Do
Else
For i As Integer = 0 To csvHeadings.Length - 1
If tableColumnNames.IndexOf(csvHeadings(i)) = -1 Then
Exit Do
End If
Next
End If
End If
'Read the Data line
csvData = sr.ReadLine.Replace("""", "").Split(Char.Parse(","))
'Execute an INSERT INTO query using the column names from the csvHeadings
'array and the data from the csvData array.
Dim SQL As New System.Text.StringBuilder
SQL.Append("INSERT INTO " & tableName & "(")
For i As Integer = 0 To csvHeadings.Length - 1
If i > 0 Then SQL.Append(",")
SQL.Append("[" & csvHeadings(i) & "]")
Next
SQL.Append(") VALUES (")
For i As Integer = 0 To csvData.Length - 1
If i > 0 Then SQL.Append(",")
SQL.Append("'" & csvData(i).Replace("'", "''") & "'")
Next
SQL.Append(")")
dbCommand = New OleDbCommand
With dbCommand
.Connection = dbConnection
.CommandText = SQL.ToString
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With
lineCount += 1
Loop
sr.Close()
Catch ex As OleDbException
Response.Write(ex.Message)
Catch ex As InvalidOperationException
Response.Write(ex.Message)
Catch ex As Exception
Response.Write(ex.Message)
Finally
If Not dbConnection Is Nothing Then dbConnection.Dispose()
If Not dbAdapter Is Nothing Then dbAdapter.Dispose()
If Not dbCommand Is Nothing Then dbCommand.Dispose()
End Try