Urgent: Output to CSV file per iteration

Hi all,

I have a VB6 small program which opens a CSV file, parses the contents and outputs the result to a
second CSV file.
The opening and parsing is no problem. For every CSV record on one line I have some logic which changes the data. I then concatenate the csvrecords per line to a string variable called Outputswift.
When I debug.print the outputswift variable I get a result for each iteration of the loop.
However when I try and print the result to a new file, I only get the last iteration of the loop.
Can anybody please correct my piece of VB?

Thanks Investfx
--------------------------------------------
Open FILENAME For Input As #i
Do While Not EOF(i)   ' Loop until end of file.
  Line Input #i, sReadLine   ' Read line into variable.
  aCSVRecord = CSVLineToArray(sReadLine, ",")
  sLine = Split(sReadLine, DELIMETER)
  d = UBound(sLine)
   If Not IsNull(aCSVRecord(0)) Then
      aCSVRecord(0) = Mid(aCSVRecord(0), 5)  ':20 Trx Reference Number
    End If
   ......
 Outputswift = aCSVRecord(0) & "," & aCSVRecord(1) & "," & .....

Loop
 Close #1
 'Open "D:\AML_Actimize\Actimize_InstallFiles\MT100Test.txt" For Output As #fno 'First
 Open FILENAME2 For Output As #fno
 Print #fno, Outputswift
 Close #fno

End Sub
-----------------------------------------------
investfxAsked:
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.

Ralf KlattEmployee in Civil ServiceCommented:
Hi investfx,

I find it difficult to workup your code but here' what I would do:

1. Build a function as "fileopener" and get all of the textstream:

Public Function GetStrg(ByVal theFilename as String) As Variant
    Dim fso, f, ts
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile(theFilename")
    Set ts = f.OpenAsTextStream()
    GetStrg= ts.readall
    ts.Close
End Function

2. Build a "caller function"

Public Sub CreateCSV()
    Dim mySplitter as Variant, myDelimit as string
    Dim myStrg as string, i as long, j as long
    Dim OutPutSwift as string, myDLen as long
    myStrg = GetStrg("test.txt")
    'The following could be chr(10) or chr(13)
    myDelimit = "Any character or string you want"
    myDLen = len(myDelimit)
    mySplitter = Split(GetStrg, myDelimit)
    for i = 1 to len(myStrg)
        if mid(myStrg, i, myDLen) = myDelimit then j = j + 1
    next
    for i = 1 to j
        OutPutSwift = OutPutSwift & mySplitter(i) & "Anything you want"
        'Write your output to a new file or keep collecting it before writing it to a file
    next
End Sub


Hope that this gives you an idea ... it's a different technique but it gives you full control over a textstream ... I'm even using this technique today with .NET (exchanging network server data using the stream object)

Best regards, Raisor
0
iozturkCommented:
try to change your code as Open FILENAME For Input As #i
Open FILENAME2 For Output As #fno
Do While Not EOF(i)   ' Loop until end of file.
 Line Input #i, sReadLine   ' Read line into variable.
 aCSVRecord = CSVLineToArray(sReadLine, ",")
 sLine = Split(sReadLine, DELIMETER)
 d = UBound(sLine)
  If Not IsNull(aCSVRecord(0)) Then
     aCSVRecord(0) = Mid(aCSVRecord(0), 5)  ':20 Trx Reference Number
   End If
  ......
Outputswift = aCSVRecord(0) & "," & aCSVRecord(1) & "," & .....
Print #fno, Outputswift

Loop
Close #1
'Open "D:\AML_Actimize\Actimize_InstallFiles\MT100Test.txt" For Output As #fno 'First


Close #fno

End Sub
0
iozturkCommented:
ops there is a confisuon


Open FILENAME For Input As #i
Open FILENAME2 For Output As #fno
Do While Not EOF(i)   ' Loop until end of file.
 Line Input #i, sReadLine   ' Read line into variable.
 aCSVRecord = CSVLineToArray(sReadLine, ",")
 sLine = Split(sReadLine, DELIMETER)
 d = UBound(sLine)
  If Not IsNull(aCSVRecord(0)) Then
     aCSVRecord(0) = Mid(aCSVRecord(0), 5)  ':20 Trx Reference Number
   End If
  ......
Outputswift = aCSVRecord(0) & "," & aCSVRecord(1) & "," & .....
Print #fno, Outputswift

Loop
Close #1
'Open "D:\AML_Actimize\Actimize_InstallFiles\MT100Test.txt" For Output As #fno 'First


Close #fno

End Sub
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.

iozturkCommented:

try this


Open FILENAME For Input As #i
Open FILENAME2 For Output As #fno
Do While Not EOF(i)   ' Loop until end of file.
 Line Input #i, sReadLine   ' Read line into variable.
 aCSVRecord = CSVLineToArray(sReadLine, ",")
 sLine = Split(sReadLine, DELIMETER)
 d = UBound(sLine)
  If Not IsNull(aCSVRecord(0)) Then
     aCSVRecord(0) = Mid(aCSVRecord(0), 5)  ':20 Trx Reference Number
   End If
  ......
Outputswift = aCSVRecord(0) & "," & aCSVRecord(1) & "," & .....
Print #fno, Outputswift

Loop
Close #1
'Open "D:\AML_Actimize\Actimize_InstallFiles\MT100Test.txt" For Output As #fno 'First


Close #fno

End Sub
0
investfxAuthor Commented:
Hi
Thanks for speedy comments.
iozturk: I have tried this and I get an error message "Run-time error 55   File already open".
Any ideas?
0
iozturkCommented:
Sory for the third. Even I reloaded the question several times I did not see my postings.
0
iozturkCommented:
then most probably i and fno is not set
either try

i=1
fno=2

or change the code as

Open FILENAME For Input As #1
Open FILENAME2 For Output As #2

Open FILENAME2 For Output As #2
Do While Not EOF(1)   ' Loop until end of file.
Line Input #i, sReadLine   ' Read line into variable.
aCSVRecord = CSVLineToArray(sReadLine, ",")
sLine = Split(sReadLine, DELIMETER)
d = UBound(sLine)
 If Not IsNull(aCSVRecord(0)) Then
    aCSVRecord(0) = Mid(aCSVRecord(0), 5)  ':20 Trx Reference Number
  End If
 ......
Outputswift = aCSVRecord(0) & "," & aCSVRecord(1) & "," & .....
Print #2, Outputswift

Loop
Close #1
'Open "D:\AML_Actimize\Actimize_InstallFiles\MT100Test.txt" For Output As #fno 'First


Close #2

End Sub
0
iozturkCommented:
Line Input #i, sReadLine   ' Read line into variable.

should be

Line Input #1, sReadLine   ' Read line into variable.
0
investfxAuthor Commented:
Hi
Thanks for speedy comments.
iozturk: I have tried this and I get an error message "Run-time error 55   File already open".
Any ideas?
0
iozturkCommented:
If you did not open the file before you shoulnot have this message.
You are using i and fno if i and fno have same value (most prob. 0) getting this error is normal. By setting i and fno to different values before openning the files should solve your problem.

I have tried this and it worked. Of course I delete lines related your formatting

FileName = "c:\abc.txt"
filename2 = "c:\abc.log"

i = 1
fno = 2

Open FileName For Input As #i
Open filename2 For Output As #fno

Do While Not EOF(i)   ' Loop until end of file.
Line Input #i, sreadline   ' Read line into variable.

Print #fno, sreadline

Loop
Close #i
'Open "D:\AML_Actimize\Actimize_InstallFiles\MT100Test.txt" For Output As #fno 'First


Close #fno
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
iozturkCommented:
Probaby you get the error because of my typo two comments before this.

I have written two times

Open FILENAME2 For Output As #2

please delete one.
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.