Solved

Visual Basic Windows Ap import from CSV

Posted on 2012-03-13
23
273 Views
Last Modified: 2012-06-27
I had someone help develop code to import a csv file into an sdf database in VB. However I cannot get the project to work correctly. So I have changed by database source to an mdb file. I want to import from two csv files into two mdb tables. The database will be stored in the same path from which this applciation is called (a thumb drive).
Here is the code with the modifications I have made so far.

I need to replace the SQL commands with the ones used for an mdb file. I just do not know what they are.

Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "Time & Pay.mdb"
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = & _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath & "\" & DBName
        If Not System.IO.File.Exists(DBName) Then
            MessageBox.Show("The data base needed for this application does not exist. Please see your application developer.")
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim cmd1 As SqlCeCommand = cn1.CreateCommand()
            cmd1.CommandText = "SELECT * FROM Hours"
            If cn1.State = ConnectionState.Closed Then
                cn1.Open()
            End If
            Dim rs As SqlCeResultSet = cmd1.ExecuteResultSet(ResultSetOptions.Updatable Or ResultSetOptions.Scrollable)
            While Not sr.EndOfData
                Dim Line = sr.ReadFields()
                Dim rec As SqlCeUpdatableRecord = rs.CreateRecord()
                rec.SetString(0, Line(0).Replace("'", String.Empty))
                rec.SetDecimal(1, Line(1))
                rec.SetDecimal(2, Line(2))
                rec.SetString(3, Line(3))
                rec.SetString(4, Line(4))
                rec.SetString(5, Line(5).Replace("'", String.Empty))
                rs.Insert(rec)
            End While
        End Using
        Return True
    End Function
0
Comment
Question by:rawilken
  • 14
  • 9
23 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37719609
you cannot mix SqlCeResultSet and SqlCeCommand with OleDbConnection. It is one or the other!

instead of opening a resultet, insert directly into the connection object. check http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(v=vs.71).aspx for an example.
0
 

Author Comment

by:rawilken
ID: 37719732
You are correct about mixing the classes. I re-wrote it using OleDb but I have the rec.get.... incorrect. The error report is that it is being used before it is being declared a value. I am not sure of what to do. Here is the code...

Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "Time & Pay.mdb"
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath() & "\" & DBName
        If Not System.IO.File.Exists(DBName) Then
            MessageBox.Show("The data base needed for this application does not exist. Please see your application developer.")
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim cmd1 As OleDb.OleDbCommand = conn.CreateCommand()
            cmd1.CommandText = "SELECT * FROM Hours"
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            While Not sr.EndOfData
                Dim Line = sr.ReadFields()
                Dim rec As OleDb.OleDbDataReader
                rec.GetDecimal(0)
                rec.GetDecimal(1)
                rec.GetDecimal(2)
                rec.GetString(3)
                rec.GetString(4)
                rec.GetString(5)
            End While
        End Using
        Return True
    End Function
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37719774
you never execute your command.

you would have a much better performance by using an INSERT command as I stated in my previous comment
0
 

Author Comment

by:rawilken
ID: 37719973
Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "Time & Pay.mdb"
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath() & "\" & DBName
        If Not System.IO.File.Exists(DBName) Then
            MessageBox.Show("The data base needed for this application does not exist. Please see your application developer.")
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim strInsert As String = "INSERT INTO Hours"
            Dim cmd1 As New OleDb.OleDbCommand(strInsert)
            cmd1.Connection = conn
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            While Not sr.EndOfData
                cmd1.ExecuteNonQuery()
            End While
            cmd1.Connection.Close()
        End Using
        Return True
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 37720041
as shown in the example, you have to provide field names and values to the INSERT statement.

it would look something like this:

   Dim myInsertTemplate As String = "INSERT INTO Hours(YourField1, YourField2, ... YourFieldX) Values('@1@', '@2@', ... '@x@')"

While Not sr.EndOfData
        Dim myInsertQuery As String = myInsertTemplate
        myInsertQuery = myInsertQuery.Replace("@1@", YourValue1FromLine)
        myInsertQuery = myInsertQuery.Replace("@2@", YourValue2FromLine)
        ......
        myInsertQuery = myInsertQuery.Replace("@x@", YourValuexFromLine)

        Dim cmd1 As New OleDbCommand(myInsertQuery , conn)
        cmd1.ExecuteNonQuery()
End While
0
 

Author Comment

by:rawilken
ID: 37720280
I think I am getting it, but in the myInsertQery.Replace("@1@", what do I enter to reference the csv file here?

Public Function SaveHoursToDB(ByVal FileName As String) As Boolean
        Dim DBName As String = "Time & Pay.mdb"
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath() & "\" & DBName
        If Not System.IO.File.Exists(DBName) Then
            MessageBox.Show("The data base needed for this application does not exist. Please see your application developer.")
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim strInsert As String = "INSERT INTO Hours ([Employee ID], [Hours Worked], [OT], [From], [To], [Position]) VALUES('@1@', '@2@', '@3@', '@4@', '@5@', '@6@')"
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            While Not sr.EndOfData
                Dim insert = strInsert
                insert = insert.Replace(@1@)
                insert = insert.Replace(@2@)
                insert = insert.Replace(@3@)
                insert = insert.Replace(@4@)
                insert = insert.Replace(@5@)
                insert = insert.Replace(@6@)
                Dim cmd1 As New OleDb.OleDbCommand(strInsert)
                cmd1.ExecuteNonQuery()
            End While
            conn.Close()
        End Using
        Return True
    End Function
0
 

Author Comment

by:rawilken
ID: 37720287
??
insert = insert.Replace(@1@, GetObject)
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37720326
there is one thing you don't seem to understand. at some point you have to get the value from SR to apply it to your insert query
0
 

Author Comment

by:rawilken
ID: 37720632
You re correct. Can you please give me some direction here?
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 500 total points
ID: 37720808
I have an article that explains how to use the TextFieldParser: http://www.emoreau.com/Entries/Articles/2010/05/Do-you-know-the-TextFieldParser.aspx


While Not sr.EndOfData
      Dim arrFields As String() = sr.ReadFields()

        Dim myInsertQuery As String = myInsertTemplate
        myInsertQuery = myInsertQuery.Replace("@1@", arrFields(1))
        myInsertQuery = myInsertQuery.Replace("@2@", arrFields(2))
        ......
        myInsertQuery = myInsertQuery.Replace("@x@", YourValuexFromLine)

        Dim cmd1 As New OleDbCommand(myInsertQuery , conn)
        cmd1.ExecuteNonQuery()
End While
0
 

Author Comment

by:rawilken
ID: 37721337
I am getting a data type error. I think it is telling me that the data type of the data values in the array do not match up to the data type in the Access database. I did create the Access tables from the csv on an import so I thought that would not be the case. Any suggestions?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37721346
that might happen with an Access DB.

if you have dates, you need to replace ' with #
for numerical values, you can remove the '
0
 

Author Comment

by:rawilken
ID: 37721391
I will use cdbl on the two array fields that are numbers. I do have a question. I want to change the data type in the Access database on a field from integer to double. If I just do it  in the database itself will that update the datasource in my project? If not how do I update it in the data source.
0
 

Author Comment

by:rawilken
ID: 37721408
I modified the datatype of the array field but I am still getting the data type error...

While Not sr.EndOfData
               Dim arrFields As String() = sr.ReadFields()
                Dim insert = strInsert
                insert = insert.Replace("@1@", arrFields(0))
                insert = insert.Replace("@2@", CDbl(arrFields(1)))
                insert = insert.Replace("@3@", CInt(arrFields(2)))
                insert = insert.Replace("@4@", arrFields(3))
                insert = insert.Replace("@5@", arrFields(4))
                insert = insert.Replace("@6@", arrFields(5))
                Dim cmd1 As New OleDb.OleDbCommand(strInsert, conn)
                cmd1.ExecuteNonQuery()
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 500 total points
ID: 37721432
fields 0, 3, 4, 5 are all strings?

have you removed the quote from the SQL string:

Dim strInsert As String = "INSERT INTO Hours ([Employee ID], [Hours Worked], [OT], [From], [To], [Position]) VALUES('@1@', @2@, @3@, '@4@', '@5@', '@6@')"
0
 

Author Comment

by:rawilken
ID: 37721746
Did that now...

Syntax error (missing operator) in query expression '@2@'.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37721773
you haven't replaced @2@. Can you show complete/modified code?
0
 

Author Comment

by:rawilken
ID: 37721790
BTW. thanks for all your help!

 Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim strInsert As String = "INSERT INTO Hours ([Employee ID], [Hours Worked], [OT], [From], [To], [Position]) VALUES('@1@', @2@, @3@, '@4@', '@5@', '@6@')"
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            While Not sr.EndOfData
               Dim arrFields As String() = sr.ReadFields()
                Dim insert = strInsert
                insert = insert.Replace("@1@", arrFields(0))
                insert = insert.Replace("@2@", CDbl(arrFields(1)))
                insert = insert.Replace("@3@", CInt(arrFields(2)))
                insert = insert.Replace("@4@", arrFields(3))
                insert = insert.Replace("@5@", arrFields(4))
                insert = insert.Replace("@6@", arrFields(5))
                Dim cmd1 As New OleDb.OleDbCommand(strInsert, conn)
                cmd1.ExecuteNonQuery()
            End While
            conn.Close()
        End Using
0
 

Author Comment

by:rawilken
ID: 37721807
I want to change that CInt field to a double in the database. I did it in the source database but it did not change in the project. Do I have to do something in the project to modify the field type?
0
 

Author Comment

by:rawilken
ID: 37721837
Never mind that last one. I got the data type modified.
0
 

Author Comment

by:rawilken
ID: 37722123
Here is the code. Can you please help me work this out?

        Dim DBName As String = "Time & Pay.mdb"
        Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
        Dim conn As New System.Data.OleDb.OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & GetPath() & "\" & DBName
        If Not System.IO.File.Exists(DBName) Then
            MessageBox.Show("The data base needed for this application does not exist. Please see your application developer.")
        End If
        Using sr As New FileIO.TextFieldParser(FileName) With {.HasFieldsEnclosedInQuotes = True, .Delimiters = {","}}
            Dim strInsert As String = "INSERT INTO Hours ([Employee ID], [Hours Worked], [OT], [From], [To], [Position]) VALUES('@1@', @2@, @3@, '@4@', '@5@', '@6@')"
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            While Not sr.EndOfData
               Dim arrFields As String() = sr.ReadFields()
                Dim insert = strInsert
                insert = insert.Replace("@1@", arrFields(0))
                insert = insert.Replace("@2@", CDbl(arrFields(1)))
                insert = insert.Replace("@3@", CInt(arrFields(2)))
                insert = insert.Replace("@4@", arrFields(3))
                insert = insert.Replace("@5@", arrFields(4))
                insert = insert.Replace("@6@", arrFields(5))
                Dim cmd1 As New OleDb.OleDbCommand(strInsert, conn)
                cmd1.ExecuteNonQuery()
            End While
            conn.Close()
        End Using
        Return True
0
 

Author Closing Comment

by:rawilken
ID: 37722553
not completely resolved
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 37722841
why isn't it completly resolved? what's left?
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Hangfire / asp.net sample 3 31
Wpf develop 5 35
Setting runtime form location 4 19
Open a word document 23 19
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now