importing a csv to access table

[penseur11]

Hi,

I'm trying to write a small application that imports data in a column from a csv file into a table in access. I'm very new to programming, but this is what i've come up with so far. It runs, but it's not actually bringing the data over to my database.

Can anyone help?

Here is the code:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim dbPath As String
        dbPath = "f:\db1.mdb"

        Dim oConn As OleDb.OleDbConnection
        oConn = New OleDb.OleDbConnection
        oConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = F:\db1.mdb;Persist Security Info=False"
        oConn.Open()

        Dim cmd As OleDb.OleDbCommand
        cmd = New OleDb.OleDbCommand
        cmd.CommandText = ("INSERT INTO tblName(Field1) FROM [Text;DATABASE=F:\myCSVFile.csv](Numbers)")

        oConn.Close()

    End Sub

[tlhintoq]

Very new to programming... so for an early learning exercise you picked the importation of Excel files to a database? Hmmm...

Database How-to parts 1 and 2
Database tutorial Part 1
Database tutorial Part 2

[penseur11]

Thanks tlhintoq, so my error is using an oledb connection and command instead of sql?

[tlhintoq]

I'm not that well versed on the two: That's why my first response to such questions when nobody else has chimed in with their 2 cents worth, is to point toward the tutorials.

I'm sure as more Bytes Volunteers check their mail during the day etc., someone with more experience will throw out some other ideas.

[CroCrew]

Hello penseur11,

I don’t know if this is the best solution to your question but I find that I don’t have many problems importing data in Microsoft Access using this type of line by line INSERT brute force technique.

Happy Coding,
CroCrew~

    Imports System.IO
	Imports System.Data.OleDb
	
	Sub LoadData()
		Dim objStreamReader As StreamReader = File.OpenText("F:\myCSVFile.csv")
		While objStreamReader.Peek() <> -1
			Dim Str1() As String = Split(objStreamReader.ReadLine(), ",")
			AddRecord(Str1(0), Str1(1))
		End While
		objStreamReader.Close()
    End Sub
	
	Sub AddRecord(ByVal FieldOne As String, ByVal FieldTwo As String)
        Dim objConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\db1.mdb")
        objConnection.Open()
        Dim SQL As String = "INSERT INTO tblName VALUES ('" & FieldOne & "', '" & FieldTwo & "')"
        Dim objCommand As OleDbCommand
        objCommand = New OleDbCommand(SQL, objConnection)
        objCommand.ExecuteNonQuery()
        objConnection.Close()
    End Sub

[penseur11]

Sort of working

Thank you Cocrew, got it working!! This is the final code I used, if anyone needs it for future.

I'm running into one problem though, in the db table the that I'm importing the data to is a primary key field and there will always be duplicates when I run the app. I keep getting this message each time I run the app. Is there any way to prevent this from popping up?

It says "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship.

Screenshot of error message:

Any ideas?

Imports System.IO
Imports System.Data.OleDb
Public Class Form1
    Sub LoadData()
        Dim objStreamReader As StreamReader = File.OpenText("F:\myCSVFile.csv")
        While objStreamReader.Peek() <> -1
            Dim Str1() As String = Split(objStreamReader.ReadLine(), ",")
            AddRecord(Str1(0))
        End While
        objStreamReader.Close()
    End Sub
    Sub AddRecord(ByVal Field1 As String)
        Dim objConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\db1.mdb")
        objConnection.Open()
        Dim SQL As String = "INSERT INTO tblName(Field1) VALUES ('" & Field1 & "')"
        Dim objCommand As OleDbCommand
        objCommand = New OleDbCommand(SQL, objConnection)
        objCommand.ExecuteNonQuery()
        objConnection.Close()
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Me.LoadData()

    End Sub
End Class

[CroCrew]

Hello penseur11,

The problem is that you are inserting duplicate data in your field when you have the properties for that field set to be “Indexed: Yes (No Duplicates)” in your database.

Change the properties for that field to “Indexed: Yes (Duplicates OK)” in your database and you should be ok.

Happy Coding,
CroCrew~