?
Solved

reading into csvs from other csv

Posted on 2005-03-31
10
Medium Priority
?
176 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
[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
  • 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

Technology Partners: 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!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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
Course of the Month12 days, 10 hours left to enroll

777 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