Link to home
Start Free TrialLog in
Avatar of Autofreak
Autofreak

asked on

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
         
ASKER CERTIFIED SOLUTION
Avatar of Shauli
Shauli

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Shauli
Shauli

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
Avatar of Autofreak

ASKER

thanks Shauli ,
                           can't wait to check the code, will get back to you tomorrow.

Take care!
Serge
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
What does this line do with the file?
aryRecords = Split(ABC.csv, vbCrLf)
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



I wasn't at a computer but I tested the above and it works on csv files just fine.
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
Ping

S
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