reading into csvs from other csv

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
         
AutofreakAsked:
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.

ShauliCommented:
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

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
ShauliCommented:
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
AutofreakAuthor Commented:
thanks Shauli ,
                           can't wait to check the code, will get back to you tomorrow.

Take care!
Serge
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.

rdwillettCommented:
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
AutofreakAuthor Commented:
What does this line do with the file?
aryRecords = Split(ABC.csv, vbCrLf)
0
rdwillettCommented:
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
rdwillettCommented:
I wasn't at a computer but I tested the above and it works on csv files just fine.
0
AutofreakAuthor Commented:
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
ShauliCommented:
Ping

S
0
AutofreakAuthor Commented:
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
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.