Link to home
Start Free TrialLog in
Avatar of JeremyH
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.

Avatar of JeremyH
JeremyH

ASKER

for example :
"SEQNUM","Date","Hour","ORIG"
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
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.FileSystemObject")
   Set f = fso.OpenTextFile(_your_file_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
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.OLEDB.4.0;" & "Data Source=C:\;Extended Properties=""TEXT;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=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
           
Avatar of JeremyH

ASKER

select * from 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.OpenCurrentDatabase App.Path & "\Emp_data.mdb", True

' Transfer text file to database

    appAccess.DoCmd.TransferText acImportDelim, "Your Import Specification Name", "YourTableName", "D:\VB\YourDatabaseName.mdb", 0

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub ConnectDB()

Dim Pathhold As String
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset

    Set db = CreateObject("ADODB.Connection")
    db.Open "Provider=Microsoft.Jet.OLEDB.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.

Avatar of JeremyH

ASKER

hey boys, i appreciate you helping me but none
of the codes seem to work
Avatar of JeremyH

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.OLEDB.4.0;" & "Data Source=C:\;Extended Properties=""TEXT;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=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
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
Avatar of JeremyH

ASKER

my csv is a txt file.
you use csv as a table??
Avatar of JeremyH

ASKER

by the way my email is jeremieharroch@hotmail.com
ASKER CERTIFIED SOLUTION
Avatar of Shaka913
Shaka913

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial