rawilken
asked on
Visual Basic Windows Ap import from CSV
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.OleDbCon nection()
conn.ConnectionString = & _
"Provider=Microsoft.Jet.OL EDB.4.0;Da ta source=" & GetPath & "\" & DBName
If Not System.IO.File.Exists(DBNa me) 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(Fil eName) With {.HasFieldsEnclosedInQuote s = 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(Resu ltSetOptio ns.Updatab le Or ResultSetOptions.Scrollabl e)
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
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.OleDbCon
conn.ConnectionString = & _
"Provider=Microsoft.Jet.OL
If Not System.IO.File.Exists(DBNa
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(Fil
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(Resu
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
ASKER
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.OleDbCon nection()
conn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta source=" & GetPath() & "\" & DBName
If Not System.IO.File.Exists(DBNa me) 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(Fil eName) With {.HasFieldsEnclosedInQuote s = 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
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.OleDbCon
conn.ConnectionString = "Provider=Microsoft.Jet.OL
If Not System.IO.File.Exists(DBNa
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(Fil
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
you never execute your command.
you would have a much better performance by using an INSERT command as I stated in my previous comment
you would have a much better performance by using an INSERT command as I stated in my previous comment
ASKER
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.OleDbCon nection()
conn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta source=" & GetPath() & "\" & DBName
If Not System.IO.File.Exists(DBNa me) 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(Fil eName) With {.HasFieldsEnclosedInQuote s = True, .Delimiters = {","}}
Dim strInsert As String = "INSERT INTO Hours"
Dim cmd1 As New OleDb.OleDbCommand(strInse rt)
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
Dim DBName As String = "Time & Pay.mdb"
Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
Dim conn As New System.Data.OleDb.OleDbCon
conn.ConnectionString = "Provider=Microsoft.Jet.OL
If Not System.IO.File.Exists(DBNa
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(Fil
Dim strInsert As String = "INSERT INTO Hours"
Dim cmd1 As New OleDb.OleDbCommand(strInse
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.OleDbCon nection()
conn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta source=" & GetPath() & "\" & DBName
If Not System.IO.File.Exists(DBNa me) 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(Fil eName) With {.HasFieldsEnclosedInQuote s = 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(strInse rt)
cmd1.ExecuteNonQuery()
End While
conn.Close()
End Using
Return True
End Function
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.OleDbCon
conn.ConnectionString = "Provider=Microsoft.Jet.OL
If Not System.IO.File.Exists(DBNa
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(Fil
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(strInse
cmd1.ExecuteNonQuery()
End While
conn.Close()
End Using
Return True
End Function
ASKER
??
insert = insert.Replace(@1@, GetObject)
insert = insert.Replace(@1@, GetObject)
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
ASKER
You re correct. Can you please give me some direction here?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
that might happen with an Access DB.
if you have dates, you need to replace ' with #
for numerical values, you can remove the '
if you have dates, you need to replace ' with #
for numerical values, you can remove the '
ASKER
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.
ASKER
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(strInse rt, conn)
cmd1.ExecuteNonQuery()
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(strInse
cmd1.ExecuteNonQuery()
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Did that now...
Syntax error (missing operator) in query expression '@2@'.
Syntax error (missing operator) in query expression '@2@'.
you haven't replaced @2@. Can you show complete/modified code?
ASKER
BTW. thanks for all your help!
Using sr As New FileIO.TextFieldParser(Fil eName) With {.HasFieldsEnclosedInQuote s = 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(strInse rt, conn)
cmd1.ExecuteNonQuery()
End While
conn.Close()
End Using
Using sr As New FileIO.TextFieldParser(Fil
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(strInse
cmd1.ExecuteNonQuery()
End While
conn.Close()
End Using
ASKER
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?
ASKER
Never mind that last one. I got the data type modified.
ASKER
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.OleDbCon nection()
conn.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta source=" & GetPath() & "\" & DBName
If Not System.IO.File.Exists(DBNa me) 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(Fil eName) With {.HasFieldsEnclosedInQuote s = 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(strInse rt, conn)
cmd1.ExecuteNonQuery()
End While
conn.Close()
End Using
Return True
Dim DBName As String = "Time & Pay.mdb"
Dim connectionString = String.Format("Data Source=""{0}"";", DBName)
Dim conn As New System.Data.OleDb.OleDbCon
conn.ConnectionString = "Provider=Microsoft.Jet.OL
If Not System.IO.File.Exists(DBNa
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(Fil
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(strInse
cmd1.ExecuteNonQuery()
End While
conn.Close()
End Using
Return True
ASKER
not completely resolved
why isn't it completly resolved? what's left?
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.