add fields from csv - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » add fields from csv

Page 1 of 2

Topic Options

#181168 - 2007-10-05 06:12 PM add fields from csv
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	I am trying to write a script that will read all of the lines of a csv file and add up the numbers in one of the fields. For example, the csv fields are similar to: Date\|account#\|account type\|work description\|balance There can be several hundred lines in each file. I would like to read all of the balances from each line and add them up. It's also important to know that there are multiple lines with the same account#. I only need to include one of the lines in the summation if the account# appears more than once. So far I have this code: Code: $csv="C:\temp\test.csv" Open(3,$csv) = 0 $read=ReadLine(3) $csv_array=Split($read,"\|") ? $csv_array[5] ;this is the account# field ? $csv_array[66] ;this is the balance field This just reads the first line of the file. So my questions are: 1. How do I loop through each line of the file to get each balance value and save that in an array to add together later. 2. How do I prevent the balance from being added to the array if that account number has already been read. I hope this makes sense. Thanks.
Top

#181169 - 2007-10-05 06:25 PM Re: add fields from csv [Re: booey]
Glenn Barnas Glenn Barnas KiX Supporter Registered: 2003-01-28 Posts: 4401 Loc: New Jersey	CSV is more than simply fields separated with commas. Per the example in the manual, you open a file, then read the first line. Then you enter a loop waiting for the end-of-file error. You read the next line at the end of the loop. Get the CSV UDF and include it in your code, and try the following: Code: ; Assumes CSV() is loaded/included $csv="C:\temp\test.csv" Open(3,$csv) = 0 $read=CSV(ReadLine(3)) ; read first line While Not @ERROR ; loop until EndOfFile error occurs $csv_array[5] ? ;this is the account# field $csv_array[66] ? ;this is the balance field $read=CSV(ReadLine(3)) Loop $ = Close(3) For item 2, a simple way is to add the account number to a string, delimited with some chars, $Accts = $Accts + '~' + $csv_array[5] + '~' then use If Not Instr($Accts,'~' + $csv_array[5] + '~') ; add to subtotal ; then add account number to $Accts string EndIf Appending them to an array and using AScan is a good alternative, but a tad more complex. Glenn _________________________ Actually I am a Rocket Scientist! $\:D$
Top

#181179 - 2007-10-05 10:36 PM Re: add fields from csv [Re: Glenn Barnas]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	Thanks for the help. I couldn't get the CSV function to work properly, however, I played around with readline and I think I got that to work ok (as far as reading the data). Or do I need to use the CSV function? Here's the latest script: Code: $csv="C:\temp\test.csv" Open(3,$csv) = 0 $read=ReadLine(3) While Not @ERROR $read=ReadLine(3) $csv_array=Split($read,"\|") If UBound($csv_array)>=3 ? "Account: " + $csv_array[5] + " Balance: " + $csv_array[66] Else EndIf Loop $read = Close(3) This script now outputs the account and balance fields for each line in the file. However, I'm stuck now with how to sum all of the balances in $csv_array[66]. How do I do that? Also, I was getting the "array is out of bounds" error until I added the UBound line that I found searching this forum. Even though it fixed the problem, I'm not really sure what this command does or when to use it. Can someone explain that to me?
Top

#181180 - 2007-10-05 10:51 PM Re: add fields from csv [Re: booey]
Les Les KiX Master Registered: 2001-06-11 Posts: 12734 Loc: fortfrances.on.ca	As far as UBound goes, the manual explains it well. Quote: UBOUND Action Returns the largest available subscript for one of the dimensions of an array. Syntax UBOUND (array, dimension) Parameter Array The array you want to know the upper boundary of. Dimension Optional parameter indicating the dimension of the array you want to know the upper boundary of. The default is 1. What part of that do you need help with? _________________________ Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.
Top

#181181 - 2007-10-05 11:03 PM Re: add fields from csv [Re: Les]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	It would help do know what they're saying in layman's terms. I'm not a very experience programmer, so I don't know what they're talking about with upper boundary and subscript. Thanks.
Top

#181182 - 2007-10-05 11:37 PM Re: add fields from csv [Re: booey]
Gargoyle Gargoyle MM club member Registered: 2004-03-09 Posts: 1597 Loc: Valley of the Sun (Arizona, US...	Ubound simply returns the total # of elements in the array. Just remember that it starts at 0 and not 1 _________________________ Today is the tomorrow you worried about yesterday.
Top

#181183 - 2007-10-06 12:46 AM Re: add fields from csv [Re: Gargoyle]
Glenn Barnas Glenn Barnas KiX Supporter Registered: 2003-01-28 Posts: 4401 Loc: New Jersey	Try: Code: $Array = 'one','two','three' UBound($Array) ' zero-base elements' ? 'The array contains ' UBound($Array) + 1 ' elements!' ? to get a better understanding. Glenn _________________________ Actually I am a Rocket Scientist! $\:D$
Top

#181192 - 2007-10-06 06:39 AM Re: add fields from csv [Re: booey]
NTDOC NTDOC Administrator Registered: 2000-07-28 Posts: 11628 Loc: CA	Originally Posted By: booey It would help do know what they're saying in layman's terms. I'm not a very experience programmer, so I don't know what they're talking about with upper boundary and subscript. Thanks. I hear you and I'm with you guy. Many people for some reason forget what it was like at first for them. I can see it on every type of technical board. I posted on a SQL board and got some responses that I'm sure regulars knew but it meant nothing to me as I don't live and breath SQL on a daily basis. Don't forget there is a learning forum where there are some posted examples of how to do things, the manual, and asking questions here. Best luck and just keep at it and practice and in no time you'll be doing great I'm sure.
Top

#181297 - 2007-10-08 05:42 PM Re: add fields from csv [Re: NTDOC]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	Ok, I understand the ubound deal now. NTDOC, I appreciate the support as well as everyone's response even though I might not understand it. Regarding my other issue, how do I sum up all of the numbers in csv_array[66]? The script below is correctly grabbing all of the lines in the file and the correct data, but I can't find an "add" function. Code: $csv="C:\temp\test.csv" Open(3,$csv) = 0 $read=ReadLine(3) While Not @ERROR $read=ReadLine(3) $csv_array=Split($read,"\|") If UBound($csv_array) ? "Account: " + $csv_array[5] + " Balance: " + $csv_array[66] Else EndIf Loop $read = Close(3) Along these same lines, is there some way to count the number of lines that readline goes through? Thanks.
Top

#181304 - 2007-10-08 06:53 PM Re: add fields from csv [Re: booey]
Gargoyle Gargoyle MM club member Registered: 2004-03-09 Posts: 1597 Loc: Valley of the Sun (Arizona, US...	You can use mulitiple arrays for your data, and then just do a recursion through one of the arrays to add the sums up. Assuming data lines = "Date\|account#\|account type\|work description\|balance" Code: $count1 = 0 $RL = Readline(1) While not @error $Bits = Split($RL) If InArray($Array,$Bits[1] <0 $Array[$count1] = $Bits[1] $Array2[$count1] = $Bits[66] $Count1 = $Count1 + 1 EndIf Loop For each $Value in $Array2 $sum = $sum + $value Next Not the most elegant, but it can do what you want. _________________________ Today is the tomorrow you worried about yesterday.
Top

#181337 - 2007-10-08 11:19 PM Re: add fields from csv [Re: Gargoyle]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	My data lines aren't exactly the way that I mentioned originally, but that's the basic format. There are actually around 70 fields on each line of the file. That being said, what do I need to edit in your script to modify it to the actual setup? I tried playing around with this all afternoon, but I'm not having any luck. Also, in your split function, you don't have anything for the delimiter. Is that correct? Thanks.
Top

#181345 - 2007-10-09 04:03 AM Re: add fields from csv [Re: booey]
Gargoyle Gargoyle MM club member Registered: 2004-03-09 Posts: 1597 Loc: Valley of the Sun (Arizona, US...	You are correct I forgot the delimiter. You have to define what "Bit" out of the string you want. Try using this code to find what Bit is what Code: $Counter = 0 For each $Bit in $Split "Element "+$Counter+" = "+$Bit? $Counter + $Counter + 1 Next _________________________ Today is the tomorrow you worried about yesterday.
Top

#181406 - 2007-10-09 09:32 PM Re: add fields from csv [Re: Gargoyle]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	I verified with the script that the dimension in the array that I want is 66. I tried entering that for array2, but I still can't get it to work. I get the error "error in expression" for line 65, which is: Code: $array[$count1] = $Bits[1] Here is the full code that I have: Code: $csv="C:\temp\test.csv" Open(3,$csv) = 0 $count1 = 0 $RL = ReadLine(3) While not @ERROR $RL = ReadLine(3) $Bits = Split($RL,"\|") If InArray($Array,$Bits[1]) = 0 $array[$count1] = $Bits[1] $array2[$count1] = $Bits[66] $Count1 = $Count1 + 1 EndIf Loop For Each $Value In $Array2 $sum = $sum + $value Next What is wrong with this? Do I need to change the bits[1] to anything? Thanks.
Top

#181409 - 2007-10-09 09:52 PM Re: add fields from csv [Re: booey]
Mart Mart KiX Supporter Registered: 2002-03-27 Posts: 4673 Loc: The Netherlands	Error on line 65? There is no line 65 in the code you posted. Here is more logical rewrite of your code. InArry is not a build inn kixtart function. Are you using a UDF or something? If not you should use ascan (see example). Open(3,$csv) = 0 is also not valid. I changed it to If Open(3,$csv) = 0 and added an else statement and an endif. I cant test because I do not know your setup and the content of the csv file but this is closer to a working solution imho. Code: $csv="C:\temp\test.csv" If Open(3,$csv) = 0 $count1 = 0 $RL = ReadLine(3) While NOT @ERROR $Bits = Split($RL,"\|") If ASCAN($array, $bits[1]) = "-1" $array[$count1] = $Bits[1] $array2[$count1] = $Bits[66] $Count1 = $Count1 + 1 EndIf $RL = ReadLine(3) Loop For Each $Value In $Array2 $sum = $sum + $value Next Else ?"There was an error opening the csv file." ?@ERROR " - " @SERROR EndIf _________________________ Mart - Chuck Norris once sold ebay to ebay on ebay.
Top

#181410 - 2007-10-09 10:00 PM Re: add fields from csv [Re: Mart]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	Thanks. I'll give it a try. It was line 65 in the complete script. I have a lot of stuff commented out. $\:\)$
Top

#181411 - 2007-10-09 10:13 PM

Re: add fields from csv [Re: booey]

Witto

MM club member

Registered: 2004-09-29
Posts: 1828
Loc: Belgium

You can also put the info in a two dimensional array

;*************************************************************************
; Script Name: SumBalances.kix
; Author: Wim Rotty
; Date: 9/10/2007
; Description: Sum balances per Account
;*************************************************************************
;Script Options
If Not @LOGONMODE
Break On
Else
Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit", "On")
$RC = SetOption("NoMacrosInStrings", "On")
$RC = SetOption("NoVarsInStrings", "On")
If @SCRIPTEXE = "KIX32.EXE"
$RC = SetOption("WrapAtEOL", "On")
EndIf

;Declare variables
Dim $Handle, $Text, $Line
Dim $AccountRow, $BalanceRow
Dim $Result[1,0]
Dim $Temp, $i

;Initialize variables
$Handle = FreeFileHandle()
$Text = @SCRIPTDIR+"\test.txt"
$AccountRow = 1
$BalanceRow = 4

;Code
$RC = Open($Handle, $Text)
$Line = ReadLine($Handle)
While Not @ERROR
If InStr($Line,"|")
$Line = Split($Line,"|")
$Temp = -1
For $i = 0 to UBound($Result,2)
If $Result[0,$i] = $Line[$AccountRow]
$Temp = $i
$i = UBound($Result,2)
EndIf
Next
If $Temp > -1
$Result[1,$Temp] = CDbl($Result[1,$Temp]) + $Line[$BalanceRow]
Else
If Not $Result[0,0] = ""
ReDim Preserve $Result[1,UBound($Result,2)+1]
EndIf
$Result[0,UBound($Result,2)] = $Line[$AccountRow]
$Result[1,UBound($Result,2)] = $Line[$BalanceRow]
EndIf
EndIf
$Line = ReadLine($Handle)
Loop
$RC = Close($Handle)
For Each $RC in $Result
$RC ?
Next
Get $RC
;Personal UDF Section

;UDF Section

The text file I used looks like this:

Code:

2007-07-15|1|Employee|Accounting|100
2007-07-15|2|Worker|Garage|25
2007-07-15|1|Employee|Accounting|100
2007-07-15|1|Employee|Accounting|100

This is the result on screen:

Code:

Top

#181412 - 2007-10-10 12:16 AM Re: add fields from csv [Re: Witto]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	Witto, that looks very close to what I'm looking for. The last thing that I would like to do is add up all of the balances for all accounts. So in your example, I would like to add up the 300 and 25. How can I do that? Thanks.
Top

#181413 - 2007-10-10 12:26 AM

Re: add fields from csv [Re: booey]

Witto

MM club member

Registered: 2004-09-29
Posts: 1828
Loc: Belgium

Loop through the balances dimension and add them one by one

;*************************************************************************
; Script Name: SumBalances.kix
; Author: Wim Rotty
; Date: 9/10/2007
; Description: Sum balances per Account
;*************************************************************************
;Script Options
If Not @LOGONMODE
Break On
Else
Break Off
EndIf
Dim $RC
$RC = SetOption("Explicit", "On")
$RC = SetOption("NoMacrosInStrings", "On")
$RC = SetOption("NoVarsInStrings", "On")
If @SCRIPTEXE = "KIX32.EXE"
$RC = SetOption("WrapAtEOL", "On")
EndIf

;Declare variables
Dim $Handle, $Text, $Line
Dim $AccountRow, $BalanceRow
Dim $Result[1,0]
Dim $Temp, $i
Dim $Total

;Initialize variables
$Handle = FreeFileHandle()
$Text = @SCRIPTDIR+"\test.txt"
$AccountRow = 1
$BalanceRow = 4

;Code
$RC = Open($Handle, $Text)
$Line = ReadLine($Handle)
While Not @ERROR
If InStr($Line,"|")
$Line = Split($Line,"|")
$Temp = -1
For $i = 0 to UBound($Result,2)
If $Result[0,$i] = $Line[$AccountRow]
$Temp = $i
$i = UBound($Result,2)
EndIf
Next
If $Temp > -1
$Result[1,$Temp] = CDbl($Result[1,$Temp]) + $Line[$BalanceRow]
Else
If Not $Result[0,0] = ""
ReDim Preserve $Result[1,UBound($Result,2)+1]
EndIf
$Result[0,UBound($Result,2)] = $Line[$AccountRow]
$Result[1,UBound($Result,2)] = $Line[$BalanceRow]
EndIf
EndIf
$Line = ReadLine($Handle)
Loop
$RC = Close($Handle)
For Each $RC in $Result
$RC ?
Next
For $i = 0 to UBound($Result,2)
$Total = CDbl($Result[1,$i]) + $Total
Next
$Total ?
Get $RC

;Personal UDF Section

;UDF Section

Top

#182402 - 2007-11-09 04:57 PM Re: add fields from csv [Re: Witto]
booey booey Getting the hang of it Registered: 2005-07-25 Posts: 76 Loc: USA	I played around with this script for a while now and I ended up rewriting it some. Unfortunately, I don't know enough about Kixtart to modify the script from Witto to fit my needs so I had another go at it myself. There are two balance fields that I need to add up. The balance is in a different field depending on what type of bill it is so I need to add up the array elements in array positions 66 and 71. The script below works for me except for the fact that it doesn't read the very first line in the csv file which causes the balance sum to be off. Am I missing something to make it read the first line? Here's what I have so far. Thanks. Code: Break ON DIM $csv,$bal,$csv_array,$read,$controlnum,$line,$x RedirectOutput("C:\temp\output.txt",1) $csv="C:\temp\test.csv" If Open(3,$csv) = 0 $read=ReadLine(3) While @ERROR = 0 $read=ReadLine(3) $csv_array=Split($read,"\|") If UBound($csv_array) ;Add together first balances ? "Account: " + ($csv_array[5]) + " Balance: " + ($csv_array[71]) $cintndc = Round($csv_array[66],2) $balsndc = $balsndc + $cintndc ;$balsndc ? ;add together second balances $cintproc = Round($csv_array[71],2) $balsproc = $balsproc + $cintproc ;$balsproc ? Else EndIf Loop @CRLF ;add together first and second balances $total = $balsndc + $balsproc "Total balance is: " + $total ? $read = Close(3) RedirectOutput("")
Top

#182403 - 2007-11-09 05:09 PM Re: add fields from csv [Re: booey]
Darren_W Darren_W Hey THIS is FUN Registered: 2001-10-10 Posts: 208 Loc: Bristol, England	I'm assuming that you are taking into consideration that arrays start at 0 as apposed to 1? so you are looking at lines 70 to 65? Darren _________________________ I want to share something with you - the three sentences that will get you through life. Number 1, 'cover for me.' Number 2, 'oh, good idea, boss.' Number 3, 'it was like that when I got here'.
Top

Page 1 of 2

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org