wutzke Posted November 12, 2006 Share Posted November 12, 2006 I've been reading the Posts about randallc's ExcelCOM stuff... I'm trying to open a CSV file and reformat each value into another Excel value. I have a generated report that looks like this: expandcollapse popupinvstkst.clc ?DB?0503.073 ,,,,,, +------------------------------ *** ADD MODE *** ------------------------------+,,,,,, | [F1]-Help [Esc]-Abandon this entry [Ins]-Add this entry [F10]-Run the report |,,,,,, | [PageUp]/[PageDown] scroll. Arrows and [Tab]/[BackTab] move cursor. |,,,,,, +-invrptdf 0503.073-261----/work3/*********/userdata/---------------10/16/2006-+,,,,,, | Inventory stock status report invstkst |,,,,,, | CLOTHIG - |,,,,,, | [P]rint or [D]isplay: D Select 'sort by' (I,D,V,M,C,L,A): V | | Item number.........: From: To: |,,,,,, | Description.........: From: To: |,,,,,, | Vendor..............: * From: BRAND123 To: BRAND123 |,,,,,, | Vendor Stock Number.: From: To: |,,,,,, | Class...............: From: To: |,,,,,, | Location............: From: To: |,,,,,, | Alternate item numbr: From: To: |,,,,,, | Report format.................: N Which unit for costs/prices?.: S |,,,,,, | Avg/Mkt/Hi/Lo/laSt Cost.......: A Price level..................: 1 |,,,,,, | Months of receiving history...: 0 Replace price with this cost.: |,,,,,, | Months of sales history.......: 33 Status codes.................: |,,,,,, | Print serial number details?..: N Movement codes: Excld/Incld: I |,,,,,, | Print vendor's stock numbers?.: N Use primary vendor only(y/n)?: N |,,,,,, | On-hand quantities to include.: A Print recap (y/n)?...........: N |,,,,,, | Include on-order in avail. qty: N Include wholesale cost (y/n)?: N |,,,,,, | Run: 10/16/2006, 15:56:04 Selected printer: Pitch: |,,,,, "| Report format: Normal (8 1/2"" X 11"") |",,,,,, | Version: $Id: invstkst.clc,v 8.1.2.2 2005/10/11 21:56:49 john Exp $ |,,,,, +------------------------------Stock status report-----------------------------+,,,,,, +------------------------------------------------------------------------------+,,,,,, + Inventory locations: 04 +,,,,,, +------------------------------------------------------------------------------+,,,,,, invstkst.clc ?DB?0503.073 ,,,,,, 10/16/2006 15:56:04 Normal Style Stock Status report for USER Page,,,,, 1,,,,,, Dept Lc Item number Item description Unit Qty OnHand Available Min OnHd Max OnHd On-order Avg. cost Reta,,,,,, il,,,,,, ---- -- ---------------- ---------------------------------- ---- ---------- -------- --------- -------- -------- ---------- --------,,,,,, --,,,,,, 2300 04 -CLO CLOTHING EA 8.00 8.00 0.00 0.00 777.00 5.18 61.,,,,,, 20,,,,,, Sep '06 459.0000 Dec '05 127.0000 Mar '05 184.0000 Jun '04 27.0000 Yr 1 avg : 89.5833,,,,,, Aug '06 68.0000 Nov '05 58.0000 Feb '05 50.0000 May '04 19.0000 total : 1075.0000,,,,,, Jul '06 74.0000 Oct '05 50.0000 Jan '05 30.0000 Apr '04 68.0000 Mn 1 stk : 0.09,,,,,, Jun '06 51.0000 Sep '05 530.0000 Dec '04 146.0000 Mar '04 45.0000 Yr 2 avg : 114.4167,,,,,, May '06 40.0000 Aug '05 83.0000 Nov '04 78.0000 Feb '04 46.0000 total : 1373.0000,,,,,, Apr '06 34.0000 Jul '05 42.0000 Oct '04 53.0000 Jan '04 38.0000 Mn 2 stk : 0.07,,,,,, Mar '06 44.0000 Jun '05 67.0000 Sep '04 38.0000 Yr 3 avg : 42.5556,,,,,, Feb '06 32.0000 May '05 53.0000 Aug '04 48.0000 total : 383.0000,,,,,, Jan '06 38.0000 Apr '05 57.0000 Jul '04 54.0000 Mn 3 stk : 0.19,,,,,, Month stock average 3 : 0.19,,,,,, 2330 04 CAPS BASEBALL CAPS MALE/FEMALE/KIDS EA 2.00 2.00 0.00 0.00 0.00 0.00 14.,,,,,, 95,,,,,, Sep '06 2.0000 Dec '05 2.0000 Mar '05 1.0000 Jun '04 4.0000 Yr 1 avg : 0.8333,,,,,, Aug '06 0.0000 Nov '05 0.0000 Feb '05 1.0000 May '04 0.0000 total : 10.0000,,,,,, Jul '06 1.0000 Oct '05 0.0000 Jan '05 2.0000 Apr '04 -1.0000 Mn 1 stk : 2.40,,,,,, Jun '06 1.0000 Sep '05 0.0000 Dec '04 3.0000 Mar '04 0.0000 Yr 2 avg : 0.7500,,,,,, May '06 1.0000 Aug '05 0.0000 Nov '04 0.0000 Feb '04 2.0000 total : 9.0000,,,,,, Apr '06 0.0000 Jul '05 0.0000 Oct '04 0.0000 Jan '04 0.0000 Mn 2 stk : 2.67,,,,,, Mar '06 0.0000 Jun '05 0.0000 Sep '04 0.0000 Yr 3 avg : 1.0000,,,,,, 16-Oct, 06 16:04 Page 1/1252,,,,, The first 31 lines are the report format and would be used just for description. As you can see the rest is a sales report 1st by item description, then a detail and sales by month. I'd like to read thru each item and place the sales data into columns to total sales by date ranges. I'm fairly savvy with Excel, but not with Autoit. Any ideas or direct would be helpful Thanks! Link to comment Share on other sites More sharing options...
Confuzzled Posted November 19, 2006 Share Posted November 19, 2006 It looks like your report is in a fairly fixed layout and you will need a loop to read your input file in sequence and then do a lot of string manipulation on the detail extracted. Consider putting the extracted information into another file in comma separated values and then import that into your spreadsheet. The help file and the tutorials in this forum will assist you - they are excellent. Link to comment Share on other sites More sharing options...
Thatsgreat2345 Posted November 19, 2006 Share Posted November 19, 2006 (edited) so does the top part matter really you just want the numbers? Edited November 19, 2006 by thatsgreat2345 Link to comment Share on other sites More sharing options...
wutzke Posted November 19, 2006 Author Share Posted November 19, 2006 Actually just these lines... | Months of sales history.......: 33 Status codes.................: |,,,,,, | Run: 10/16/2006, 15:56:04 Selected printer: Pitch: |,,,,, Link to comment Share on other sites More sharing options...
wutzke Posted November 23, 2006 Author Share Posted November 23, 2006 I can do ... #include <Array.au3> Dim $mwArray[1] $mwArray[0] = "test.txt" $file = FileOpen("test.txt", 0) ; Check if file opened for reading OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf ; 1st 33 lines $x = 0 While $x < 33 $x = $x + 1 $line = FileReadLine($file,$x) _ArrayAdd( $mwArray, $line) WEnd $mwarray = _ArrayTrim( $mwArray, 6, 1) _ArrayDisplay( $mwArray, "Updated Array" ) FileClose($file) Which will put the 1st lines into an array for later use Link to comment Share on other sites More sharing options...
wutzke Posted November 23, 2006 Author Share Posted November 23, 2006 then I did expandcollapse popup#include<Array.au3> #include<File.au3> Dim $mwArray[1] $mwArray[0] = "test.txt------" $file = FileOpen("test.txt", 0) ; Check if file opened for reading OK If $file = -1 Then MsgBox(0, "Error", "Unable to open file.") Exit EndIf $x = 34 $line = FileReadLine($file,$x) $line = StringTrimRight($line,6) $x = $x + 4 $line = FileReadLine($file,$x) $line = StringTrimRight($line,6) $line2 = FileReadLine($file,$x+1) $line2 = StringTrimRight($line2,6) $line3 = $line & $line2 & "------" _ArrayAdd( $mwArray, $line3) $myNewLine = " 04 " $line3 = FileReadLine($file,$x) $lineCheck = StringMid($line3,5,4) While $x < 51 $line = FileReadLine($file,$x) $lineCheck = StringMid($line,5,4) If $myNewLine <> $lineCheck Then _ArrayAdd( $mwArray, $line) EndIf If $myNewLine = $lineCheck Then ; MsgBox(0, "New Line", $line) EndIf $x = $x + 1 WEnd $aNewArray = _ArrayTrim( $mwArray, 6, 1) _ArrayDisplay( $aNewArray, "Updated Array" ) FileClose($file) _FileWriteFromArray("test2.txt",$aNewArray,1) which gave me 2300 04 -CLO CLOTHING EA 8.00 8.00 0.00 0.00 777.00 5.18 61.20 20 Sep '06 459.0000 Dec '05 127.0000 Mar '05 184.0000 Jun '04 27.0000 Yr 1 avg : 89.5833 Aug '06 68.0000 Nov '05 58.0000 Feb '05 50.0000 May '04 19.0000 total : 1075.0000 Jul '06 74.0000 Oct '05 50.0000 Jan '05 30.0000 Apr '04 68.0000 Mn 1 stk : 0.09 Jun '06 51.0000 Sep '05 530.0000 Dec '04 146.0000 Mar '04 45.0000 Yr 2 avg : 114.4167 May '06 40.0000 Aug '05 83.0000 Nov '04 78.0000 Feb '04 46.0000 total : 1373.0000 Apr '06 34.0000 Jul '05 42.0000 Oct '04 53.0000 Jan '04 38.0000 Mn 2 stk : 0.07 Mar '06 44.0000 Jun '05 67.0000 Sep '04 38.0000 Yr 3 avg : 42.5556 Feb '06 32.0000 May '05 53.0000 Aug '04 48.0000 total : 383.0000 Jan '06 38.0000 Apr '05 57.0000 Jul '04 54.0000 Mn 3 stk : 0.19 Month stock average 3 : 0.19 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now