Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

CSV - Database

Posted on 2003-03-31
12
Medium Priority
?
325 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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

580 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