0

I have a problem and hope that sombody can help me. I create a program with Visual Basic and want to import a CSV file into a SQLite database. I found this link: How to bulk insert a CSV file into SQLite C# The code is in C #, I rewrote it in VB.

Public Sub LoadCheckFiletoDatabase(ByVal checkFilePath As String)
Dim tempTable As DataTable = GetDataTableFromCsv(checkFilePath)

For Each DataR As DataRow In Me.memDataTable.Columns
    Dim Dic As Dictionary(Of String, Object) = New Dictionary(Of String, Object)()

    For Each DataCol As DataColumn In Me.memDataTable.Columns
        Dim field As String = DataCol.ColumnName.ToString()
        Dim value As Object = CStr(DataR(DataCol).ToString())
        Dic.Add(field, value)
    Next

    Using sQLiteConnection As SQLiteConnection = New SQLiteConnection("data source=" & databasepath)

        Using sQLiteCommand As SQLiteCommand = New SQLiteCommand(sQLiteConnection)
            Dim sQLiteHelper As System.Data.SQLite.SQLiteHelper = New 
SQLiteHelper(sQLiteCommand)
            sQLiteHelper.BeginTransaction()
            sQLiteHelper.Insert(tempTable.TableName, Dic)
        End Using

Public Shared Function GetDataTableFromCsv(ByVal path As String) As DataTable
Dim pathOnly As String = Path.GetDirectoryName(path)
Dim fileName As String = Path.GetFileName(path)
Dim sql As String = "SELECT * FROM [" & fileName & "]"

Using connection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathOnly & ";Extended Properties=""Text;HDR=" & "Yes" & """")

    Using command As OleDbCommand = New OleDbCommand(sql, connection)

        Using adapter As OleDbDataAdapter = New OleDbDataAdapter(command)
            Dim dataTable As DataTable = New DataTable()
            dataTable.Locale = CultureInfo.CurrentCulture
            adapter.Fill(dataTable)
            dataTable.TableName = fileName.TrimEnd(New Char() {"."c, "c"c, "s"c, "v"c})
            Return dataTable
        End Using
    End Using
End Using

I think I understand the code, but I have a problem here.

 System.Data.SQLite.SQLiteHelper

I am using System.Data.SQLite, which does not know this command. Is this a separate library, and if so, where can I find these and does this work in VB as well? As I said, I want to import CSV files into a SQLite database. It knows, it also works with sqlite3.exe, but the program should work on several different computers, as I find it hard to specify an exact path, as described in the link. Theoretically, I would have to: 1. open the CSV 2. Delete the top line with the names of the columns 3. Read each line individually 4. Transfer this to SQLite. Since SQLite does not seem to be able to "bulk insert", 3rd and 4th must be in a loop. So far the theory. I'm a bloody beginner and have no idea how to implement it as a code. Does anyone have an example, if possible in VB.net, I found despite several hours of search nothing on the Internet. Or can someone give me a tip on how to do it differently. As I said, I am a bloody beginner and very grateful for any kind of help.

Steve
  • 213,761
  • 22
  • 232
  • 286
halloWelt
  • 1
  • This is NOT an endorsement of any kind, but a web search reveal [this](https://www.nuget.org/packages/SqliteHelper/). – C Perkins Nov 17 '19 at 15:44
  • Note that of all the answers listed on that post, you chose the one with the least upvotes and by the person with the lowest rep. The answer makes it more difficult than it needs to be. You can use OleDB to fill a DataTable which is then used to update/insert into SQLIte. If it is indeed simply an import job it really is not complex enough to need a "helper". – Ňɏssa Pøngjǣrdenlarp Nov 17 '19 at 22:11

0 Answers0