Sign in to follow this  
Followers 0
wutzke

CSV Reader

6 posts in this topic

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:

invstkst.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!

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

so does the top part matter really you just want the numbers?

Edited by thatsgreat2345

Share this post


Link to post
Share on other sites

Actually just these lines...

| Months of sales history.......: 33  Status codes.................:           |,,,,,,

| Run: 10/16/2006, 15:56:04  Selected printer:  Pitch:                       |,,,,,

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

then I did

#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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0