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

x
?
Solved

reading into csvs from other csv

Posted on 2005-03-31
10
Medium Priority
?
178 Views
Last Modified: 2010-05-02
Hello All!
              I get a huge ABC.csv file that I would like to sort out into a number of new csvs - a,b,c. Depending on each ABC.csv string's pattern I want to put it into one of the newly created csv files  - a,b, or c.  Could you please help me with this leaving out the string analysis part.  I'd like the program to check if there are a,b, and c already in the designated MyFolder folder and if there are not create them anew. Also, every time I run the program, the new data have to override the old ones.

Thanks you very much
Serge
         
0
Comment
Question by:Autofreak
  • 4
  • 3
  • 3
10 Comments
 
LVL 19

Accepted Solution

by:
Shauli earned 1000 total points
ID: 13676669
Dim myLine As String
Open "ABC.csv" For Input As #1
    Open "a.csv" For Output As #2
    Open "b.csv" For Output As #3
    Open "c.csv" For Output As #4
        Do Until EOF(1)
            Line Input #1, myLine
                'do your analysis
                Select Case YourAnalysysResult  'modify
                    Case "A"   'modify
                        Print #2, myLine
                    Case "B"   'modify
                        Print #3, myLine
                    Case "C"   'modify
                        Print #4, myLine
                End Select
        Loop
    Close #4
    Close #3
    Close #2
Close #1

S
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13676678
ps. If you want to override then you don't need to check and / or create the files. The code above will create the files if they dont exist, or override if they do.

S
0
 

Author Comment

by:Autofreak
ID: 13677668
thanks Shauli ,
                           can't wait to check the code, will get back to you tomorrow.

Take care!
Serge
0
Industry Leaders: 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!

 
LVL 4

Assisted Solution

by:rdwillett
rdwillett earned 1000 total points
ID: 13677779
Not much difference in mine suggestion expect the file is further split by the comma separator allowing
each field in line to be checked.

Private Sub MakeCSVFiles()
   On Error GoTo err_open
    Dim aryRecords() As String
    Dim aryFields()  As String
    Dim idxRecord    As Long
    Dim idxField     As Long        'You could use this like idxRecord to look at each field in the line don't need it my example
    aryRecords = Split(ABC.csv, vbCrLf)
    Open "a.csv" For Output As #2
    Open "b.csv" For Output As #3
    Open "c.csv" For Output As #4
    ' Process the records:
    For idxRecord = 0 To UBound(aryRecords) - 1
        aryFields = Split(aryRecords(idxRecord), ",")
        Select Case aryFields(x)      'x would be field in the comma string to check for case 'i.e. aryFields(23) = "FirstFile"
        Case "YourText1"
             'Any other analysis
             Print #2, aryRecords(idxRecord)
        Case "YourText2"
             'Any other analysis
             Print #3, aryRecords(idxRecord)
        Case "YourText3"
             'Any other analysis
             Print #4, aryRecords(idxRecord)
        End Select
    Next idxRecord
    Exit Sub
err_open:
    MsgBox Err.Description
    Exit Sub
End Sub
0
 

Author Comment

by:Autofreak
ID: 13683793
What does this line do with the file?
aryRecords = Split(ABC.csv, vbCrLf)
0
 
LVL 4

Expert Comment

by:rdwillett
ID: 13684418
aryRecords = Split(ABC.csv, vbCrLf)
Splits the file by LineFeed
Each Line is now a Record i.e. aryRecords(idxRecord) where idxRecord = 0 is actuall line 1 of the data

aryFields = Split(aryRecords(idxRecord), ",")
Splits each line from above in Fields (from a csv file)
Thus aryFields(0) is the First Field of any Line

Consider the line (The, Dog, Ran, Fast)
aryFields(0) would be The
aryFields(1) would be Dog
aryFields(2) would be Ran
.................

Plus in my code I made a slight mistake.
I am going to correct it.
Do this.
Add the Component: Microsoft Common Dialog Control 6.0 under Project--> Components
Double Click it to add one to forms and accept default name.
Add one command button to form and accept default name.

Add the following Code:

Option Explicit
Option Explicit
Public Function FileExists(ByVal sFile As String) As Boolean
  On Error Resume Next
  FileLen sFile
  FileExists = (Err.Number = 0)
End Function

Public Function GetFile(ByVal sFile As String) As String
  Dim hFile As Integer

  If FileExists(sFile) Then
     hFile = FreeFile()
     Open sFile For Binary As #hFile
     GetFile = Input(LOF(hFile), hFile)
     Close #hFile
  End If
End Function

Private Sub Command1_Click()

On Error GoTo err_open
    Dim aryRecords() As String
    Dim aryFields()  As String
    Dim idxRecord    As Long
    Dim idxField     As Long        'You could use this like idxRecord to look at each field in the line don't need it my example

'You'll be given a menu to pick ABC.csv
With CommonDialog1
        .Filter = "All Files (*.*)|*.*"
        .FilterIndex = 1
        .Flags = cdlOFNOverwritePrompt
        .InitDir = ""
        .FileName = ""
        .ShowOpen
    End With
    If CommonDialog1.FileName = "" Then Exit Sub
    aryRecords = Split(GetFile(CommonDialog1.FileName), vbCrLf)  'Which will be ABC.csv if that's what u call it
    Open "a.csv" For Output As #2
    Open "b.csv" For Output As #3
    Open "c.csv" For Output As #4
    ' Process the records:
    For idxRecord = 0 To UBound(aryRecords) - 1
        aryFields = Split(aryRecords(idxRecord), ",")
        Select Case aryFields(x)      'x would be field in the comma string to check for case 'i.e. aryFields(23) = "FirstFile"
        Case "YourText1"               ' make sure u make x a number else u will get an error -- x must be a valid number between 0 and UBound(aryRecords) -1
             'Any other analysis
             Print #2, aryRecords(idxRecord)
        Case "YourText2"
             'Any other analysis
             Print #3, aryRecords(idxRecord)
        Case "YourText3"
             'Any other analysis
             Print #4, aryRecords(idxRecord)
        End Select
    Next idxRecord
    Exit Sub
err_open:
    MsgBox Err.Description
    Exit Sub
End Sub



0
 
LVL 4

Expert Comment

by:rdwillett
ID: 13684436
I wasn't at a computer but I tested the above and it works on csv files just fine.
0
 

Author Comment

by:Autofreak
ID: 13690055
Sorry Guys, I had so much on my plate on friday, didn't have a chance to try the code. I Will get back to you next week.

Have a great weekend,
Serge
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13728110
Ping

S
0
 

Author Comment

by:Autofreak
ID: 13731954
Hey Guys,
               it's just a crazy week, deadlines don't wait! I haven't tried the code yet but I have a feeling that those are excellent solutions. I learned a few things from both of you and split the mark even - rdwillett has put more effort but Shauli was first - fair? Great thanks! Keep up the good work!

Serge
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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
Course of the Month13 days, 21 hours left to enroll

581 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