?
Solved

CSV - Database

Posted on 2003-03-31
12
Medium Priority
?
323 Views
Last Modified: 2010-05-01
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.

0
Comment
Question by:JeremyH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 

Author Comment

by:JeremyH
ID: 8240490
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
 
LVL 3

Expert Comment

by:emadat
ID: 8240777
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
 
LVL 3

Expert Comment

by:Shaka913
ID: 8240838
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:JeremyH
ID: 8241179
select * from sample.csv ???????????????????????
r u sure that works ?
cause you're suppose to put a table's name
instead of sample.csv
0
 

Expert Comment

by:TinLemon
ID: 8241183
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
 

Author Comment

by:JeremyH
ID: 8241242
hey boys, i appreciate you helping me but none
of the codes seem to work
0
 

Author Comment

by:JeremyH
ID: 8241251
by the way i'm using an oracle database
0
 
LVL 3

Expert Comment

by:Shaka913
ID: 8241651
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
 
LVL 3

Expert Comment

by:Shaka913
ID: 8241664
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
 

Author Comment

by:JeremyH
ID: 8241716
my csv is a txt file.
you use csv as a table??
0
 

Author Comment

by:JeremyH
ID: 8241719
by the way my email is jeremieharroch@hotmail.com
0
 
LVL 3

Accepted Solution

by:
Shaka913 earned 150 total points
ID: 8241745
Sent the code to you email.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

764 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