This should be pretty easy XLS or CSV get data and save to another CSV file

[Bry]

I have been trying to figure this out for acouple of days. I need to get some data from a .XLS that has then been saved as a .CSV file. The data isn't all on the same line as see in the supplied sample data below. What i am trying to do is open a .CSV file then get 3 pieces of information that are on different lines put them together seperated by a comma and write that information to another .CSV file which another programs takes and imports into a database. My thinking was either to use Excel or a Array and also a INI file (to store settings), but I am not sure how to cycle through each line to get the information and put it together when the data I need is not on the same line. I am currently able to use some of the Excel Management Functions and go so many rows and columns and get the cell value, but so far down the file it changes by adding 2 more rows per Item and throws off the row count. I hope this make some sense fo what I am trying to do. It should be pretty easy, I just haven't figured it out so it works. I was trying to use a array, but not sure how to set that up either. I ahve used AutoIt for a while but never done anything like this.

Here is what the sample data looks like if opened in notepad:

----Sample Data Starts Here-----

Report By Customer,,,,,,

ABC #: ********* Item #: *****9 ,,,,,,

From 3/1/2009 to 3/31/2009,,,,,,

Date: 3/1/2009,,,,,Detail Date: 03/01/2009,

Terminal ID,Description,Display,,,,Fee

P02862 ,Funds ,"$2,300.00 ",,,,$0.00

,Total:,"$2,300.00 ",,,,$0.00

,,,,,"Cost for 3/1/2009 - $2,300.00",

Date: 3/2/2009,,,,,Detail Date: 03/02/2009,

Terminal ID,Description,Display,,,,Fee

P02862 ,Funds ,$720.00 ,,,,$0.00

,Total:,$720.00 ,,,,$0.00

,,,,,Cost for 3/2/2009 - $720.00,

Date: 3/3/2009,,,,,Detail Date: 03/03/2009,

Terminal ID,Description,Display,,,,Fee

P02862 ,Funds ,"$1,320.00 ",,,,$0.00

,Total:,"$1,320.00 ",,,,$0.00

,,,,,"Cost for 3/3/2009 - $1,320.00",

The Bold Text is the data I just need so in the final .CSV file it would look like this:

P02862,03/01/2009,2300

P02862,03/02/2009,720

P02862,03/03/2009,1320

-----Sample Data Ends Here-----

Sorry for the long post just wanted to get as much detail in the 1st post as possible.

Any insight or assistance would greatly be appreciated.

Edited April 3, 2009 by Bry

[Melba23]

Bry,

First, welcome to the AutoIt forums.

When you post here it always helps if you have had a go at solving your problems beforehand. Having some code to work on is a great help - and no-one here is too keen to help the "code it for me" brigade.

Reading the Help file (at least the first few sections - Using AutoIt, Tutorials and the first couple of References) will help you enormously. You should also look at the excellent tutorials that you will find here and here. Using the Search facility is also a good tip as there is a pretty good chance your question, or something like it, has been asked before. Look for the "Search" button to the right in the title bar.

Anyway, you were right - your solution is pretty easy. :-) So.....

#include <Array.au3>

$sFull_File = FileRead("path to the original file")
$hFile = FileOpen("Path to the new file", 2)

$aArray_Sections = StringSplit($sFull_File, "Detail Date:", 1)

For $i = 2 To $aArray_Sections[0]

    $aArray_Details = StringSplit($aArray_Sections[$i], "Fee", 1)
    _ArrayDisplay($aArray_Details)
    $aArray_Details = StringSplit($aArray_Details[2], ",")
    _ArrayDisplay($aArray_Details)
    $sResult = StringTrimRight($aArray_Details[1], 1) & ","
    $sResult &= StringMid($aArray_Sections[$i], 2, 10) & ","
    $aArray_Details = StringSplit($aArray_Sections[$i], "$")
    _ArrayDisplay($aArray_Details)
    $sResult &= StringReplace(StringLeft($aArray_Details[2], StringInStr($aArray_Details[2], ".") - 1), ",", ""); I am sure you will have fun with this line!
    FileWrite($hFile, $sResult)
    
Next

FileClose($hFile)

The #include <Array.au3> and _ArrayDisplay(...) lines are only there so you can follow what is going on, you do not need to keep them.

Ask if anything is unclear.

M23

[Bry]

Thank you Melba23

Here is my testing code that I have been messing with for the pass few days. I have been using AutoIt for years, but never for anything like this. I didn't post code as it was pretty ugly. I always do search, but didn't find anything that explains how to really use Arrays. I look for tutorials and such. I had found a few samples of code from the forum, but nothing that was close to doing what I was trying to do. I don't what others to write the code, otherwise I won't learn the systax and flow of logic...etc. I do thank you for the sample code you posted, but I was looking for was more explation of code or how to verses rather then just having somebody code it. I think the only reason I finally posted as I am under a timing issue to get this done.

Here is my ugly code I have been testing with:

#include <Excel.au3>

#include <Date.au3>

#include <File.au3>

#include <Array.au3>

If FileExists(@WorkingDir & "\My.ini") Then

$AppTitle = IniRead(@WorkingDir & "\My.Ini", "Settings", "Title", "")

$TIDOffset= IniRead(@WorkingDir & "\My.ini", "TID", "RowOffset", "Data Not Found In The INI For TID")

$DATEOffset= IniRead(@WorkingDir & "\My.ini", "Date", "RowOffset", "Data Not Found In The INI For DATE")

$AMOUNTOffset= IniRead(@workingDir & "\My.ini", "Amount", "RowOffset", "Data Not Found In The INI For AMOUNT")

;MsgBox(4096, $AppTitle, "TID Offset: " & $TIDOffset & @CR & "Date Offset: " & $DATEOffset & @CR & "Amount Offset: " & $AMOUNTOffset)

;Calculate Date for current import day

$sImportdate = _DateAdd( 'd', -1, _NowCalcDate())

$sMonth = StringMid($sImportdate,6,2)

$sDay = StringMid($sImportdate,9,2)

$sYear = StringMid($sImportdate,1,4)

$filedate = $sMonth & $sDay & $sYear

If FileExists(@WorkingDir & "\Arch March.csv") Then

$sFilePath1 = @ScriptDir & "\Arch March.csv" ;This file should already exist

$oExcel = _ExcelBookOpen($sFilePath1)

$sTIDRowCounter = 6

Run("notepad.exe")

WinWaitActive("Untitled - Notepad")

Do

;For $i = 1 To 5

;Local $avArray[1]

$sTIDCellValue = StringStripWS(_ExcelReadCell($oExcel,$sTIDRowCounter + 2 ,1),8)

$sDateCellValue = StringMid(_ExcelReadCell($oExcel,$sTIDRowCounter,1),11,9)

$sAmountCellValue = _ExcelReadCell($oExcel,$sTIDRowCounter + 2,3)

If $sAmountCellValue = 0 Then

;Skip wirtting to Notepad

Else

WinActive("Untitled - Notepad")

Send($sTIDCellValue & "," & $sDateCellValue & "," & $sAmountCellValue)

Send(@CR)

;_ArrayAdd($avArray,$sTIDCellValue)

;MsgBox(4096, "Information", "TID: " & $sTIDCellValue & @CR & "Date: " & $sDateCellValue & @CR & "Amount: " & $sAmountCellValue)

;MsgBox(4096, "Information", "TID: " & $sTIDCellValue & " " & $sTIDRowCounter)

$sTIDRowCounter = $sTIDRowCounter + $TIDOffset

;_ArrayDisplay($avArray, "test")

EndIf

;Next

Until $sTIDCellValue = ""

Sleep(5000)

_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

Else

MsgBox(4096, "Information", "File: Arch March .csv" @CR "Could Not Be Found, Please Check For The File" @CR "or" @CR "Chcek the File Name.")

EndIf

Else

MsgBox(4096, "Information", "Ini File Is Missing.")

EndIf

Some of this was so I could see what the code was doing. Pretty rough ugh!

Thank you again.

Edited April 3, 2009 by Bry

[Melba23]

Bry,

Congratulations on wanting to do it yourself - and apologies if I came over as a bit condescending.

Here is a commented version of the code to help you understand it better:

expandcollapse popup

#include <Array.au3>

; Read in the original CSV file
$sFull_File = FileRead("path to the original file")
; Create the output file
$hFile = FileOpen("Path to the new file", 2)

; Split the original file using "Detail Date:" as the split point
$aArray_Sections = StringSplit($sFull_File, "Detail Date:", 1)
; Show the split file
_ArrayDisplay($aArray_Sections)

; Take each of the useful sections of the array in turn
For $i = 2 To $aArray_Sections[0]

; Split each of the sections on "Fee"
    $aArray_Details = StringSplit($aArray_Sections[$i], "Fee", 1)
; Show the result
    _ArrayDisplay($aArray_Details)
; Now split the second element again using ","
    $aArray_Details = StringSplit($aArray_Details[2], ",")
; Show the result
    _ArrayDisplay($aArray_Details)
; Create a string with the date followed bya comma
    $sResult = StringTrimRight($aArray_Details[1], 1) & ","

; Go back to original array and extract the date - add to result string followed by a comma
    $sResult &= StringMid($aArray_Sections[$i], 2, 10) & ","
    
; Split the original array on "$"
    $aArray_Details = StringSplit($aArray_Sections[$i], "$")
; Show the result
    _ArrayDisplay($aArray_Details)

; Let us break this line down
    $sResult &= StringReplace(StringLeft($aArray_Details[2], StringInStr($aArray_Details[2], ".") - 1), ",", "")
#cs
; Find the decimal point in this element
    StringInStr($aArray_Details[2], ".")
; Extract the figures preceding the decimal point
    StringLeft($aArray_Details[2], (What we just did) - 1)
; Remove the thousands comma and add the result to the string
    $sResult &= StringReplace((What we just did), ",", "")
#ce
; Write result string to the output file
    FileWrite($hFile, $sResult)
    
Next

; Close output file
FileClose($hFile)

I hope that is clearer. Please ask if anything is unclear.

M23

Edit: Speeling

Edited April 4, 2009 by Melba23

[SmOke_N]

Ya'll are making it way harder than need be. It's really just two lines of RegEx code and then concat the array to the specified string.

Here's a full function for it:

_Write_MyCSVData("testing.csv")

Func _Write_MyCSVData($s_file_to_read, $s_out_file = "", $s_read_delim = ",", $s_out_delim = ",")
    
    If FileExists($s_file_to_read) = 0 Then Return SetError(1, 0, 0)
    Local $s_data = FileRead($s_file_to_read)
    
    ; If no out file is specified, use file to read as the write to file
    If $s_out_file = "" Then $s_out_file = $s_file_to_read
    
    Local $s_pattern = "(?i)(?s)(?:\A|\n)Date:\s+(\d+/\d+/\d+).+?" & $s_read_delim & _
                        "Fee\s+(\w+)\s+" & $s_read_delim & _
                        "Funds\s+" & $s_read_delim & "\x22?\$(.+?)\s\x22?"
                        
    Local $a_sre = StringRegExp($s_data, $s_pattern, 3)
    If @error Then Return SetError(2, 0, 0)
    Local $s_out_string = ""
    
    ; Concat strings in specific order
    For $i = 0 To UBound($a_sre) - 1 Step 3
        $s_out_string &= $a_sre[1] & $s_out_delim & $a_sre[0] & $s_out_delim & $a_sre[2] & @CRLF
    Next
    
    ; Make sure you have a clean file to write to, or there is a cr/lf at the end of the file
    If StringRegExp($s_out_file, "^\z|[\r\n]+\z") = 0 Then $s_out_string = @CRLF & $s_out_string
    
    Return FileWrite($s_out_file, StringTrimRight($s_out_string, 2))
EndFunc

Should be pretty self explanatory.

[Melba23]

SmOke_N,

I was waiting for a RegEx coder to pop up! I freely admit that nothing involving that function is "pretty self explanatory" to me - but I will keep trying to get my head around it.

M23

[Bry]

Melba23 and SmOke_N,

Just wanted to say thank you both for the information and the example code. I never used the stringregexp before. I have been reading up on regular expression patterns an WOW! Open my eyes to a new way at to look for data in files...etc. I going to look at using it for future projects.

Thank you to both of you again.