?
Solved

Urgent: Output to CSV file per iteration

Posted on 2003-03-09
11
Medium Priority
?
401 Views
Last Modified: 2010-04-07
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
-----------------------------------------------
0
Comment
Question by:investfx
[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
  • 8
  • 2
11 Comments
 
LVL 15

Expert Comment

by:Ralf Klatt
ID: 8101247
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
 
LVL 4

Expert Comment

by:iozturk
ID: 8101465
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
 
LVL 4

Expert Comment

by:iozturk
ID: 8101472
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:iozturk
ID: 8101511

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
 

Author Comment

by:investfx
ID: 8101512
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
 
LVL 4

Expert Comment

by:iozturk
ID: 8101513
Sory for the third. Even I reloaded the question several times I did not see my postings.
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8101525
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
 
LVL 4

Expert Comment

by:iozturk
ID: 8101532
Line Input #i, sReadLine   ' Read line into variable.

should be

Line Input #1, sReadLine   ' Read line into variable.
0
 

Author Comment

by:investfx
ID: 8101629
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
 
LVL 4

Accepted Solution

by:
iozturk earned 2000 total points
ID: 8101697
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
 
LVL 4

Expert Comment

by:iozturk
ID: 8101715
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

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month8 days, 10 hours left to enroll

765 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