PDA

View Full Version : CSV files processing



edg_r
10-03-2013, 03:24 AM
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
10-03-2013, 07:22 AM
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
10-03-2013, 09:31 AM
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
10-03-2013, 12:10 PM
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
10-03-2013, 12:26 PM
Thanks snb,

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

snb
10-03-2013, 12:50 PM
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
10-04-2013, 02:02 AM
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
10-04-2013, 07:52 AM
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/index.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.