Bry Posted April 3, 2009 Share Posted April 3, 2009 (edited) 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,,,,FeeP02862 ,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,,,,FeeP02862 ,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,,,,FeeP02862 ,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,2300P02862,03/02/2009,720P02862,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 Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted April 3, 2009 Moderators Share Posted April 3, 2009 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 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Bry Posted April 3, 2009 Author Share Posted April 3, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted April 4, 2009 Moderators Share Posted April 4, 2009 (edited) 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 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Moderators SmOke_N Posted April 4, 2009 Moderators Share Posted April 4, 2009 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)) EndFuncShould be pretty self explanatory. Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted April 4, 2009 Moderators Share Posted April 4, 2009 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 Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Bry Posted April 6, 2009 Author Share Posted April 6, 2009 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. 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