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.

JeremyHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JeremyHAuthor Commented:
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
0
emadatCommented:
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
0
Shaka913Commented:
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
           
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JeremyHAuthor Commented:
select * from sample.csv ???????????????????????
r u sure that works ?
cause you're suppose to put a table's name
instead of sample.csv
0
TinLemonCommented:
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.

0
JeremyHAuthor Commented:
hey boys, i appreciate you helping me but none
of the codes seem to work
0
JeremyHAuthor Commented:
by the way i'm using an oracle database
0
Shaka913Commented:
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
0
Shaka913Commented:
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
0
JeremyHAuthor Commented:
my csv is a txt file.
you use csv as a table??
0
JeremyHAuthor Commented:
by the way my email is jeremieharroch@hotmail.com
0
Shaka913Commented:
Sent the code to you email.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.