JeremyH
asked on
CSV - Database
I need to read in a text or CSV file one line at a time.
I have to load the information of this file in a database.
*There are commas between differents fields of the text file.
I have to load the information of this file in a database.
*There are commas between differents fields of the text file.
You need to read the file a line-by-line, use the Split function to return the fields in an array, then loop through the array to work on the fields.
From Your "Project", "References" menu item, add "Microsoft Scripting Runtime" to your project and modify the followig function to do the job for you.
Here is the function:
Private Sub Command1_Click()
Dim fso As FileSystemObject
Dim f As TextStream, S$
Dim tmpArray, L%, X%, Y%
Set fso = CreateObject("Scripting.Fi leSystemOb ject")
Set f = fso.OpenTextFile(_your_fil e_name_, ForReading)
While Not f.AtEndOfStream
S = f.ReadLine
tmpArray = Split(S, ",")
U = UBound(tmpArray)
L = LBound(tmpArray)
For X = L To U
' Work on each field here
Next X
Wend
End Sub
From Your "Project", "References" menu item, add "Microsoft Scripting Runtime" to your project and modify the followig function to do the job for you.
Here is the function:
Private Sub Command1_Click()
Dim fso As FileSystemObject
Dim f As TextStream, S$
Dim tmpArray, L%, X%, Y%
Set fso = CreateObject("Scripting.Fi
Set f = fso.OpenTextFile(_your_fil
While Not f.AtEndOfStream
S = f.ReadLine
tmpArray = Split(S, ",")
U = UBound(tmpArray)
L = LBound(tmpArray)
For X = L To U
' Work on each field here
Next X
Wend
End Sub
Jeremy,
Here is a cool way to let ADO parse the csv file for you, and then write the results into a DB of your choise. I chose to use an ACCESS database here, but any would work.
Enjoy
Dim InConn As New ADODB.Connection
Dim OutConn As New ADODB.Connection
Dim InRS As New ADODB.Recordset
Dim OutRS As New ADODB.Recordset
Dim zColNames(10) As String
Dim zI As Integer
Dim zFieldCount As Integer
InConn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & "Data Source=C:\;Extended Properties=""TEXT;DriverId =27;FIL=te xt;MaxBuff erSize=204 8;PageTime out=5;"""
InRS.Open "select * from sample.csv", InConn, adOpenStatic, adLockReadOnly, adCmdText
OutConn.CursorLocation = adUseClient
OutConn.Provider = "Microsoft.Jet.OLEDB.3.51"
OutConn.Open App.Path & "\MyDatabase.MDB", "", ""
OutRS.Open "Select * from MyTable", OutConn, adOpenDynamic, adLockOptimistic
InRS.MoveFirst
zFieldCount = InRS.Fields.Count
For zI = 1 To zFieldCount
zColNames(zI) = InRS.Fields(zI).Value
Next zI
InRS.MoveNext
Do While Not InRS.EOF
OutRS.AddNew
For zI = 1 To zFieldCount
OutRS.Fields(zColNames(zI) ) = InRS.Fields(zI).Value
Next zI
OutRS.Update
InRS.MoveNext
Loop
InRS.Close
OutRS.Close
InConn.Close
OutConn.Close
Here is a cool way to let ADO parse the csv file for you, and then write the results into a DB of your choise. I chose to use an ACCESS database here, but any would work.
Enjoy
Dim InConn As New ADODB.Connection
Dim OutConn As New ADODB.Connection
Dim InRS As New ADODB.Recordset
Dim OutRS As New ADODB.Recordset
Dim zColNames(10) As String
Dim zI As Integer
Dim zFieldCount As Integer
InConn.Open "Provider=Microsoft.Jet.OL
InRS.Open "select * from sample.csv", InConn, adOpenStatic, adLockReadOnly, adCmdText
OutConn.CursorLocation = adUseClient
OutConn.Provider = "Microsoft.Jet.OLEDB.3.51"
OutConn.Open App.Path & "\MyDatabase.MDB", "", ""
OutRS.Open "Select * from MyTable", OutConn, adOpenDynamic, adLockOptimistic
InRS.MoveFirst
zFieldCount = InRS.Fields.Count
For zI = 1 To zFieldCount
zColNames(zI) = InRS.Fields(zI).Value
Next zI
InRS.MoveNext
Do While Not InRS.EOF
OutRS.AddNew
For zI = 1 To zFieldCount
OutRS.Fields(zColNames(zI)
Next zI
OutRS.Update
InRS.MoveNext
Loop
InRS.Close
OutRS.Close
InConn.Close
OutConn.Close
ASKER
select * from sample.csv ???????????????????????
r u sure that works ?
cause you're suppose to put a table's name
instead of sample.csv
r u sure that works ?
cause you're suppose to put a table's name
instead of sample.csv
You could also import the file ONCE into Access and set up an Import Specification, which is REAL easy. Save the specification and then when you import the .CSV with VB into your Access database, all you need to do is reference the Import Specification and all of your data will be imported exactly the same time, everytime. THis will also include specified column headings.
If you don't know how to set up an import specification, let me know.
If you do, here is the code you can use to import via VB.
~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~
Private Sub ImportData()
ConnectDB
appAccess.OpenCurrentDatab ase App.Path & "\Emp_data.mdb", True
' Transfer text file to database
appAccess.DoCmd.TransferTe xt acImportDelim, "Your Import Specification Name", "YourTableName", "D:\VB\YourDatabaseName.md b", 0
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~~~~~~~~~~ ~
Private Sub ConnectDB()
Dim Pathhold As String
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Set db = CreateObject("ADODB.Connec tion")
db.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & _
"Data Source= YourDatabaseName.mdb;" & _
"User Id=admin;" & _
"Password="
db.Close
End Sub
This works like a charm, I'll help you work through it if you have trouble.
If you don't know how to set up an import specification, let me know.
If you do, here is the code you can use to import via VB.
~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ImportData()
ConnectDB
appAccess.OpenCurrentDatab
' Transfer text file to database
appAccess.DoCmd.TransferTe
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ConnectDB()
Dim Pathhold As String
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Set db = CreateObject("ADODB.Connec
db.Open "Provider=Microsoft.Jet.OL
"Data Source= YourDatabaseName.mdb;" & _
"User Id=admin;" & _
"Password="
db.Close
End Sub
This works like a charm, I'll help you work through it if you have trouble.
ASKER
hey boys, i appreciate you helping me but none
of the codes seem to work
of the codes seem to work
ASKER
by the way i'm using an oracle database
Ok,
I dont have an oracle database to test with so I created a ms Access db to test. I found a few bugs, and this code worked like a champ. And Yes, you can open a csv table with a select statement, you can even select data that way like...
SELECT DISTINCT Status FROM sample.csv
SELECT Status FROM sample.csv WHERE Status = First
Private Sub Command1_Click()
Dim InConn As New ADODB.Connection
Dim OutConn As New ADODB.Connection
Dim InRS As New ADODB.Recordset
Dim OutRS As New ADODB.Recordset
Dim zColNames(10) As String
Dim zI As Integer
Dim zFieldCount As Integer
InConn.Open "Provider=Microsoft.Jet.OL EDB.4.0;" & "Data Source=C:\;Extended Properties=""TEXT;DriverId =27;FIL=te xt;MaxBuff erSize=204 8;PageTime out=5;"""
InRS.Open "select * from sample.txt", InConn, adOpenStatic, adLockReadOnly, adCmdText
OutConn.CursorLocation = adUseClient
OutConn.Provider = "Microsoft.Jet.OLEDB.4.0"
OutConn.Open "c:\sample.mdb", "", ""
OutRS.Open "Select * from MyTable", OutConn, adOpenDynamic, adLockOptimistic
InRS.MoveFirst
zFieldCount = InRS.Fields.Count - 1
For zI = 0 To zFieldCount
zColNames(zI) = InRS.Fields(zI).Name ' InRS.Fields(zI).Value
Next zI
InRS.MoveFirst
Do While Not InRS.EOF
OutRS.AddNew
For zI = 0 To zFieldCount
OutRS.Fields(zColNames(zI) ).Value = InRS.Fields(zI).Value
Next zI
OutRS.Update
InRS.MoveNext
Loop
InRS.Close
OutRS.Close
InConn.Close
OutConn.Close
End Sub
I dont have an oracle database to test with so I created a ms Access db to test. I found a few bugs, and this code worked like a champ. And Yes, you can open a csv table with a select statement, you can even select data that way like...
SELECT DISTINCT Status FROM sample.csv
SELECT Status FROM sample.csv WHERE Status = First
Private Sub Command1_Click()
Dim InConn As New ADODB.Connection
Dim OutConn As New ADODB.Connection
Dim InRS As New ADODB.Recordset
Dim OutRS As New ADODB.Recordset
Dim zColNames(10) As String
Dim zI As Integer
Dim zFieldCount As Integer
InConn.Open "Provider=Microsoft.Jet.OL
InRS.Open "select * from sample.txt", InConn, adOpenStatic, adLockReadOnly, adCmdText
OutConn.CursorLocation = adUseClient
OutConn.Provider = "Microsoft.Jet.OLEDB.4.0"
OutConn.Open "c:\sample.mdb", "", ""
OutRS.Open "Select * from MyTable", OutConn, adOpenDynamic, adLockOptimistic
InRS.MoveFirst
zFieldCount = InRS.Fields.Count - 1
For zI = 0 To zFieldCount
zColNames(zI) = InRS.Fields(zI).Name ' InRS.Fields(zI).Value
Next zI
InRS.MoveFirst
Do While Not InRS.EOF
OutRS.AddNew
For zI = 0 To zFieldCount
OutRS.Fields(zColNames(zI)
Next zI
OutRS.Update
InRS.MoveNext
Loop
InRS.Close
OutRS.Close
InConn.Close
OutConn.Close
End Sub
Jeremy,
I would be happy to email you the sample code, csv, and mdb file if you would like to have it. I created the csv you specified, put it in the root (you will have to pick a different location in COnnection string to change that). and then created an empty MDB in the root as well (to make it easier on me) run the above code, and it worked like a champ. Actually I'm going to keep this code, it is pretty useful, just add a "create table if none found" section and this would be a great little convertor.
Hope this helps
I would be happy to email you the sample code, csv, and mdb file if you would like to have it. I created the csv you specified, put it in the root (you will have to pick a different location in COnnection string to change that). and then created an empty MDB in the root as well (to make it easier on me) run the above code, and it worked like a champ. Actually I'm going to keep this code, it is pretty useful, just add a "create table if none found" section and this would be a great little convertor.
Hope this helps
ASKER
my csv is a txt file.
you use csv as a table??
you use csv as a table??
ASKER
by the way my email is jeremieharroch@hotmail.com
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"SEQNUM","Date","Hour","OR
20390312.00,10/7/2002 0:00:00,0,10/6/2002 23:58:39
20390979.00,10/7/2002 0:00:00,0,10/7/2002 0:02:38
20391106.00,10/7/2002 0:00:00,0,10/7/2002 0:01:59
20391641.00,10/7/2002 0:00:00,0,10/7/2002 0:05:05