Need assistance in outputting to a Text file 2

[Excelerate2004]

Hello to all,

I have a text file with customers names and their purchases over a 5 month period.

It would look like similar to whats below:

"Name", "M1", "M2", "M3", "M4", "M5"
"Harry Jones", 250, 300, 450, 100, 250
"Henny Penny", 100, 200, 150, 300, 250
"Tom Mack", 110, 320, 150, 345, 430
"Jenn Street", 320, 280, 450, 320, 840
"Mary Jacobs", 430, 350, 700, 630, 290

Using VB6 and reading this text file, lets call it customers.txt, how can I output each customers name with their calculated total purchases into another textfile lets call it custtot.txt that would look like this:

Customer name Total purchases for 5 months
"Harry Jones" 1350
"Henny Penny" 1000...

Does this make sense??

Basically I want to be able to calculate their total purchases from the input text file using VB6 and then output these calculated totals to another text file.

What is the quickest & easiest way to do this??

Thanks for any help I may get!

Cheers!

 

[ezypezy80]

Not sure if that's the best way of doing it but I would use an Access table to help.
Import into Access the source text file, comma delimited file, first row is row header.

Then with VB 6, loop through row 1 to last row of the table you just imported,
accumulating the values of M1,M2,M3, M4, and M5.

Write an output to a text file while calculating the total
of each row
Something like this:

dim gInputStr as string

Open "C:\TextOutput.txt" For Output As #1

'Loop through each row 1 to last row
', calculate total values,then output it
Print #1, gInputStr 'will output a line of string

Close #1

 

[ezypezy80]

>Not sure if that's the best way of doing it

sorry I made a typo,I mean ...I'm not sure if this is the best way of doing it

 

[JerryKlmns]

Not tested, just from memory....

Dim strName as String
Dim M1 as Long
Dim M2 as Long
Dim M3 as Long
Dim M4 as Long
Dim M5 as Long
Dim ReadFileNo as Integer
Dim WriteFileNo as Integer

ReadFileNo = FreeFile
WriteFileNo = FreeFile

Open "C:\TextOutput.txt" For Output As #WriteFileNo 
Open "C:\TextInput.txt" For Input As #ReadFileNo 

Do While not EOF(ReadFileNo)
    Line Input ReadFileNo, strName, M1, M2, M3, M4, M5
    Write WriteFileNo, strName, M1+M2+M3+M4+M5
Loop
Close WriteFileNo 
CLose ReadFileNo

 

[Excelerate2004]

Hi Jerry,

I tried your suggestion but I get an error thats telling me the file is already open.

As well, I had to put a # sign in front of ReadFileNo & WriteFileNo.

Any ideas on how I can get rid of the error thanks!

 

[JerryKlmns]

Check it

Dim strName As String
Dim M1 As Long
Dim M2 As Long
Dim M3 As Long
Dim M4 As Long
Dim M5 As Long
Dim ReadFileNo As Integer
Dim WriteFileNo As Integer

WriteFileNo = FreeFile
Open "C:\TextOutput.txt" For Output As #WriteFileNo
ReadFileNo = FreeFile
Open "C:\TextInput.txt" For Input As #ReadFileNo

Do While Not EOF(ReadFileNo)
    Input #ReadFileNo, strName, M1, M2, M3, M4, M5
    Write #WriteFileNo, strName, M1 + M2 + M3 + M4 + M5
Loop
Close WriteFileNo
Close ReadFileNo

 

[kermit01de]

I use two (or three) functions

To Read: String = ReadFile("C:\customers.txt")
To Write: Call writefileout("C:\newfile.txt")
To Append: Call writefileapp("C:\appendfile.txt")


------------------
Function ReadFile(ByRef Path As String) As String
Dim FileNr As Long

On Error Resume Next
If FileLen(Path) = 0 Then Exit Function
On Error GoTo 0

FileNr = FreeFile

Open Path For Binary As #FileNr
ReadFile = Space$(LOF(FileNr))
Get #FileNr, , ReadFile
Close #FileNr
End Function


Sub writefileout(ByRef Path As String, Text As String)
Dim FileNr As Long

FileNr = FreeFile
Open Path For Output As #FileNr
Print #FileNr, Text;
Close #FileNr
End Sub


Sub writefileapp(ByRef Path As String, Text As String)
Dim FileNr As Long

FileNr = FreeFile
Open Path For Append As #FileNr
Print #FileNr, Text;
Close #FileNr
End Sub


--------------------------------------
>>>>>> Bugs will appear in one part of a working program when another 'unrelated' part is modified <<<<<

 

[gmmastros]

Here's another option for you....

[green]' You will need a reference to Microsoft ActiveX Data Objects 2.x[/green]
Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset

[green]' You will need a reference to the Microsoft Scripting Runtime[/green]
Dim FSO As Scripting.FileSystemObject

Dim cOut As String

[green]' Open an ADO connection to the text file.[/green]
Set DB = CreateObject("ADODB.Connection")
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[red]c:\[/red];Extended Properties=""text;HDR=Yes;FMT=Delimited"""
DB.Open
Set RS = CreateObject("ADODB.Recordset")
Set RS = DB.Execute("Select Name, M1 + M2 + M3 + M4 + M5 as Total from [red]tektips.txt[/red]")

[green]' prepare the data for output[/green]
cOut = """Name"",""FiveMonthTotal""" & vbCrLf
While Not RS.EOF
    cOut = cOut & """" & RS.Fields.Item("Name").Value & """," & CStr(RS.Fields.Item("Total").Value) & vbCrLf
    RS.MoveNext
Wend
RS.Close
Set RS = Nothing
DB.Close
Set DB = Nothing

[green]' save the data.[/green]
Set FSO = CreateObject("Scripting.FileSystemObject")
Call FSO.CreateTextFile("[red]C:\custtot.txt[/red]", True).Write(cOut)
Set FSO = Nothing

Basically, this method allows you to treat the input file as a database table. The 'sql' does the addition for you. You'll need to change the filenames and folders (they appear in the code in red).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Excelerate2004]

From Jerrys solution:

The first suggestion I've tried is Jerry's, everything works out fine with one exception.

How can I apply the alias or column header (Total sales) to this calculation M1+M2+M3+M4+M5 as the header is not in the original input file.

What is the correct syntax for this line of code:

Write #WriteFileNo, strName, M1 + M2 + M3 + M4 + M5

:

so that I can apply the column header total sales to the output file

I've tried different ideas but all that keeps appearing in the column headers space is 0. I want that 0 to be Total Sales??

Thanks for this last bit of assistance.

 

[JerryKlmns]

Before the Do ... Loop add this line

Write #WriteFileNo, "Customer name", "Total purchases for 5 months"

And something to concider. If at least one customer, appears more than one time at TextInput.txt then you should concider gmmastros way, altering the sql select statement executed

Set RS = DB.Execute("SELECT [Name], Sum([M1]+[M2]+[M3]+[M4]+[M5] as Total FROM tektips.txt GROUP BY [Name]")

 

[SteveGlo]

gmmastros

Thanks for posting the example code - the syntax has helped me solve a problem that has been bugging me.

Steve

 

[Excelerate2004]

Thanks to all who helped me with this project!

Much appreciated!

Cheers!