CSV files processing

[edg_r]

Hi everybody.
I'm new on this forum.
Can anybody advise on the sample of the VBA script, which I can run from MS Excel, which will execute following steps:
1. Open CSV file
2. Copy data to Array1
3. Split comma-separated values and copy to Array2
4. Analyse each value
5. Remove some elements from Array1
6. Save the result Array1 into another CSV file

Can above be done without copying data to Array1?

[Kenneth Hobs]

Welcome to the forum!

Being new, you have asked for a whole project which is why no responses have been posted I suspect. Even with all of the steps listed, a few questions come to mind. Rather than stating steps, just state a goal. Often, we can think of ways to speed the process time. If you have analysis criterion, state it. We need to know details.

Posting short example file(s), helps us help you more readily. These files can include special details that explain or show what is needed.

[edg_r]

Dear Kenneth,

Thank you for your response.
I just need to Process some CSV file and apply some filtering rules, depending on the values of the fields.
I could do that easily with PERL, but I need MS Office tools to perform the mentioned operations.
So what I need is sample script of opening CSV file, going through records and values and saving it back.
Hope this clarifies and I can get the hint.

[snb]

sub M_snb()
  with open("C:\file.csv")
    array1=.content
    for each ln in array1
      array2=split(ln,";")
      for each it in array2
         if it="remove" then 
            ln.delete
            exit for
         end if
      next
    next
  end with

  createcsvfile("C:\file2.csv").write array1
End Sub

I hope my suggestion is as clear as your question.

[edg_r]

Thanks snb,

Could you please provide a source on VBA, chich I can run from MSExcel?

[snb]

As you may be well aware of you can't code 'to Process some CSV file', nor to 'apply some filtering rules'.
Nobody can provide some source on VBA.
If your questions are fuzzy, the answers will be too.
You'd better read some book on VBA first.

[Jan Karel Pieterse]

Some sample code then:

Sub GetOpenFileNameExample2()
    Dim vFilename As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim lCount As Long
    sPath = "c:\windows\temp\"
    ChDrive sPath
    ChDir sPath
    vFilename = Application.GetOpenFilename("text files (*.csv),*.csv", , "Please select the file(s) to import", , True)
    If TypeName(vFilename) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilename) To UBound(vFilename)
        ImportThisOne CStr(vFilename(lCount))
    Next
End Sub
Sub ImportThisOne(sFileName As String)
    Dim oBook As Workbook
    Workbooks.Open sFileName
    Set oBook = ActiveWorkbook
    'Now do your processing on the newly imported sheet
    
    'Copy new sheet into this workbook
    With oBook.Worksheets(1).UsedRange
        'filter out any foobars in column 1
        .AutoFilter 1, "<>foobar"
        .SpecialCells(xlCellTypeVisible).Copy
        oBook.Worksheets.Add
        ActiveSheet.Paste
        oBook.SaveAs "csv2.csv", xlCSV
    End With
        
    'close text file, do not save changes
    oBook.Close False
    Set oBook = Nothing
End Sub

[Kenneth Hobs]

Jan's code uses a foreground type of method and is easy to understand.

My approach would use an ADO method. You can use SQL to query the data and sort it or whatever needed. All of that would be done in the background. There would be no need for arrays. To form the SQL string, obviously I would need to know how you want the data "filtered". To know that, one needs to know field names and such most likely. Ergo my request for a sample file. ADO can be confusing to the novice. If you want to learn more about ADO, here is one site. http://www.erlandsendata.no/english/...php?t=envbadac

For small sets of data, foreground methods work just fine. There can be many solutions to a problem. With the speed of today's computers, unless there is a big difference in processing time, just go with a solution that works.