Search the Community

I have a table that'd I'd like to lookup things in my script based on the input. Searching this forum I found some old posts about _ExcelReadSheetToArray(). After not getting it to work, I realized that is no longer in the UDF and _Excel_Range_Read is to be used instead. I also read the _Excel_RangeFind is another method to lookup data in a table. This method seems to use Excel to run these functions, whereas _Excel_Range_Read loads the entire range as an array and autoit does the work. I'm looking at loading a 30k row csv with 3 columns. Does anyone know if either this methods are better with this amount of data? Pros/cons? I'm leaning towards the _Excel_RangeFind so Excel can just run in the background and be the "database" vs. my script holding all that data in a massive array. Or maybe there's a completely different method? Let me know your thoughts!

Hi, I am looking for the possibility to extract a xls (not xlsx)-Excelfile to a csv . My problem: I do NOT have any Appliaction from MS-OFFICE. All searchings (since 3 days now) allways wants me to install ms-office or excel. I CAN'T DO THIS !!! Any help?! Ths'x alot

Hello Smart People! I have a multi-column .CSV that I would like to draw information from, in order to populate (send) that info to some fields in an Oracle form. This would be for account-creation in Oracle. I have the basic script to navigate (tab) through the fields in the Oracle form, but I am SUPER-new to AutoIT and don’t quite see how to get the script to import and use variables from a .CSV For example: My script just waits for the page to be active, then enters data and tabs between fields like this: ; Wait for the window to be active WinWaitActive("Oracle is Cool - E-Business is the Best") ; User Name Send("jsmith") Send("{TAB}") ; Password Send("Password1") Send("{TAB}") Send("Password1") Send("{TAB}") ; Description Send("John Smith") Send("{TAB}") (etc. etc. etc.…) The winning solution would take one row at a time, feed it into fields on the Oracle form, save, and then start in on the next row It looks like I’d need a combination of “FileReadToArray” ( or“FileReadLine”?) and “StringSplit” to loop through the values and set them to variables, which would then replace my current "hard-coded" values to be typed them into the form? I found the following example in the “Help” snippets for stringsplit()… this seems like it’s in the ballpark but I’m having some trouble wrapping this around what I’ve put together, since each entry in the array would have multiple elements per line instead of just “Day”: Func Example() Local $aDays = StringSplit("Mon,Tues,Wed,Thur,Fri,Sat,Sun", ",") ;Split the string of days using the delimiter "," and the default flag value. #cs The array returned will contain the following values: $aDays[1] = "Mon" $aDays[2] = "Tues" $aDays[3] = "Wed" ... $aDays[7] = "Sun" #ce For $i = 1 To $aDays[0] ; Loop through the array returned by StringSplit to display the individual values. MsgBox($MB_SYSTEMMODAL, "", "$aDays[" & $i & "] - " & $aDays[$i]) Next EndFunc ;==>Example This post also seems like a similar example, but I don’t need it to be so selective RE: one specific column: https://www.autoitscript.com/forum/topic/166261-how-to-read-csv-specific-row-and-columns Any advice would be appreciated! Thanks for taking a minute to look!

Hi all, I have a csv file as below, I wand to find srno from csv and send corresponding ip and pass to commend cmd prompt. Please guide me to create script . srno,name,ip,pass 1,name1,ip1,pass1 2,name2,ip2,pass2

hi all, reviewing the forum, this thread is applicable: I wanted to know if there is now a better way to do this? In essence, I load a tab delimited txt file into an array (works well). I used tab, as some fields in the original csv contains commas. However, I needed autoit to manipulate this array, and output it as a csv. IF my array contains items with a comma, without double quotes around the field, then how best do I get a csv out of this? My current workaround is to filewritefromarray tab delimited, then open it in excel and save as a csv. I will need to check this to see how the address fields behave that contain a comma. Any thoughts would be appreciated.

Dear all, Can someone show me how to en hance the below function to write in CSV into column and rows the input values ? I am getting this result: I would like the result to be as this From A1:C1 is for headers From A2:C2 is for input Data Global Const $GUI_EVENT_CLOSE = -3 $sDataFilePath = @ScriptDir & "\Records.csv" #region ### START Koda GUI section ### Form= $Form1 = GUICreate("Demo1: New Record", 580, 115) $Input1 = GUICtrlCreateInput("", 10, 30, 270, 21) $Input2 = GUICtrlCreateInput("", 300, 30, 270, 21) $Input3 = GUICtrlCreateInput("", 10, 80, 270, 21) $Label1 = GUICtrlCreateLabel("Name:", 10, 10, 35, 17) $Label2 = GUICtrlCreateLabel("ID:", 300, 10, 18, 17) $Label3 = GUICtrlCreateLabel("Phone No:", 10, 60, 55, 17) $Button1 = GUICtrlCreateButton("Save to CSV", 450, 70, 120, 30) GUISetState(@SW_SHOW) #endregion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $Button1 _ExportData() MsgBox(64, @ScriptName, "Record Saved.") EndSwitch WEnd Func _ExportData() If Not FileExists($sDataFilePath) Then FileWriteLine($sDataFilePath, "Name;ID;Phone No.;") EndIf For $i = $Input1 To $Input3 FileWrite($sDataFilePath, GUICtrlRead($i) & ";") Next FileWriteLine($sDataFilePath, "") EndFunc ;==>_ExportData May be Excel UDF has be to be added but I can manage that my self Thank you in advance

I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct. This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc. If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted. I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file. I've included a test excel file with a single line and test script to create a csv demonstrating the problem. test.xlsx test.au3

Good morning guys I'd like to know if there is a way to convert a PDF in CSV or, eventually, in TXT, in order to read from it, like a database... I have a PDF and I think ( I dind't search a lot on the forum ) with AutoIt, but I'd like work with Excel styles... Does anyone know a good program which convert PDF to CSV? PS: the PDF file is 5 MB, and it contains 439 pages... Thanks everyone for the help

I have a csv file with delimiters "," and @CRLF After trying several different examples (simple ones!) I still haven't resolved an answer #include <Array.au3> #include <File.au3> ;#include "ArrayMultiColSort.au3" Global $BatchDir = "C:\ncat\" FileChangeDir($BatchDir) $sFile = "mod1.csv" ; Read file into a 2D array Global $aArray _FileReadToArray($sFile, $aArray, $FRTA_NOCOUNT, ",") ; And here it is _ArrayDisplay($aArray, "Original", Default, 8) Firstly it throws a MsgBox error "No array variable passed to function" _ArrayDisplay(), so no displayed data Second and probably more important how do I get _FileReadToArray() to split the imported array[][] into rows and columns? I tried "," & @CRLF without success.

Hi guys, I'd like to write a piece of tool that would allow me to update a certain field in our Active Directory from a comma separated csv file composed like this: This file, automatically generated, can hold more than 10k lines. Thus, I need column A to be in one variable, column B in a second one and column C in a third one. I'm really missing this part as updating the AD is fairly easy once the 3 variable are populated. I see things like this: Here's my attempts at the moment: #include <File.au3> #include <Array.au3> Global $csv_file = @DesktopDir & "\Book1.csv" Global $aRecords If Not _FileReadToArray($csv_file,$aRecords) Then MsgBox(4096,"Error", " Error reading log to Array error:" & @error) Exit EndIf For $x = 1 to $aRecords[0] Msgbox(0,'Record:' & $x, $aRecords[$x]) ; Shows the line that was read from file $csv_line_values = StringSplit($aRecords[$x], ",",1) ; Splits the line into 2 or more variables and puts them in an array ; _ArrayDisplay($csv_line_values) ; Shows what's in the array you just created. ; $csv_line_values[0] holds the number of elements in array ; $csv_line_values[1] holds the value ; $csv_line_values[2] holds the value ; etc Msgbox(0, 0, $csv_line_values[1]) Next Any help on this please? Thanks in advance -31290-

I've had issues in the past dealing with excel so I decided to cut out the middle man and build a script that would take any file that opens in excel (csv, xml, xls, etc) and convert it into an array so I can handle the raw data in a cleaner way. I used czardas' CSV parser to do this and added a simple save in excel to save it as a csv to parse. Func _CreateCSV($fnImportFile) $oExcel = ObjCreate("Excel.Application") $oExcel.Visible=False $oBook= $oExcel.Workbooks.Open($fnImportFile) $oSheet=$oBook.ActiveSheet $fnMaster=@TempDir&"\"&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&".csv" ConsoleWrite($fnMaster&@CRLF) $oSheet.SaveAs($fnMaster, 6) $oBook.Close(False) $oExcel.Quit $aReturnArray=_CSVSplit(FileRead($fnMaster)) FileDelete($fnMaster) If not @error Then Return $aReturnArray Else Return -1 EndIf EndFunc ; #FUNCTION# ==================================================================================================================== ; Name...........: _CSVSplit ; Description ...: Converts a string in CSV format to a two dimensional array (see comments) ; Syntax.........: CSVSplit ( $aArray [, $sDelim ] ) ; Parameters ....: $aArray - The array to convert ; $sDelim - Optional - Delimiter set to comma by default (see 2nd comment) ; Return values .: Success - Returns a two dimensional array or a one dimensional array (see 1st comment) ; Failure - Sets @error to: ; |@error = 1 - First parameter is not a valid string ; |@error = 2 - Second parameter is not a valid string ; |@error = 3 - Could not find suitable delimiter replacements ; Author ........: czardas ; Comments ......; Returns a one dimensional array if the input string does not contain the delimiter string ; ; Some CSV formats use semicolon as a delimiter instead of a comma ; ; Set the second parameter to @TAB To convert to TSV ; =============================================================================================================================== Func _CSVSplit($string, $sDelim = ",") ; Parses csv string input and returns a one or two dimensional array If Not IsString($string) Or $string = "" Then Return SetError(1, 0, 0) ; Invalid string If Not IsString($sDelim) Or $sDelim = "" Then Return SetError(2, 0, 0) ; Invalid string $string = StringRegExpReplace($string, "[\r\n]+\z", "") ; [Line Added] Remove training breaks Local $iOverride = 63743, $asDelim[3] ; $asDelim => replacements for comma, new line and double quote For $i = 0 To 2 $asDelim[$i] = __GetSubstitute($string, $iOverride) ; Choose a suitable substitution character If @error Then Return SetError(3, 0, 0) ; String contains too many unsuitable characters Next $iOverride = 0 Local $aArray = StringRegExp($string, '\A[^"]+|("+[^"]+)|"+\z', 3) ; Split string using double quotes delim - largest match $string = "" Local $iBound = UBound($aArray) For $i = 0 To $iBound -1 $iOverride += StringInStr($aArray[$i], '"', 0, -1) ; Increment by the number of adjacent double quotes per element If Mod ($iOverride +2, 2) = 0 Then ; Acts as an on/off switch $aArray[$i] = StringReplace($aArray[$i], $sDelim, $asDelim[0]) ; Replace comma delimeters $aArray[$i] = StringRegExpReplace($aArray[$i], "(\r\n)|[\r\n]", $asDelim[1]) ; Replace new line delimeters EndIf $aArray[$i] = StringReplace($aArray[$i], '""', $asDelim[2]) ; Replace double quote pairs $aArray[$i] = StringReplace($aArray[$i], '"', '') ; Delete enclosing double quotes - not paired $aArray[$i] = StringReplace($aArray[$i], $asDelim[2], '"') ; Reintroduce double quote pairs as single characters $string &= $aArray[$i] ; Rebuild the string, which includes two different delimiters Next $iOverride = 0 $aArray = StringSplit($string, $asDelim[1], 2) ; Split to get rows $iBound = UBound($aArray) Local $aCSV[$iBound][2], $aTemp For $i = 0 To $iBound -1 $aTemp = StringSplit($aArray[$i], $asDelim[0]) ; Split to get row items If Not @error Then If $aTemp[0] > $iOverride Then $iOverride = $aTemp[0] ReDim $aCSV[$iBound][$iOverride] ; Add columns to accomodate more items EndIf EndIf For $j = 1 To $aTemp[0] If StringLen($aTemp[$j]) Then If Not StringRegExp($aTemp[$j], '[^"]') Then ; Field only contains double quotes $aTemp[$j] = StringTrimLeft($aTemp[$j], 1) ; Delete enclosing double quote single char EndIf $aCSV[$i][$j -1] = $aTemp[$j] ; Populate each row EndIf Next Next If $iOverride > 1 Then Return $aCSV ; Multiple Columns Else For $i = 0 To $iBound -1 If StringLen($aArray[$i]) And (Not StringRegExp($aArray[$i], '[^"]')) Then ; Only contains double quotes $aArray[$i] = StringTrimLeft($aArray[$i], 1) ; Delete enclosing double quote single char EndIf Next Return $aArray ; Single column EndIf EndFunc ;==> _CSVSplit ; #INTERNAL_USE_ONLY# =========================================================================================================== ; Name...........: __GetSubstitute ; Description ...: Searches for a character to be used for substitution, ie one not contained within the input string ; Syntax.........: __GetSubstitute($string, ByRef $iCountdown) ; Parameters ....: $string - The string of characters to avoid ; $iCountdown - The first code point to begin checking ; Return values .: Success - Returns a suitable substitution character not found within the first parameter ; Failure - Sets @error to 1 => No substitution character available ; Author ........: czardas ; Comments ......; This function is connected to the function _CSVSplit and was not intended for general use ; $iCountdown is returned ByRef to avoid selecting the same character on subsequent calls to this function ; Initially $iCountown should be passed with a value = 63743 ; =============================================================================================================================== Func __GetSubstitute($string, ByRef $iCountdown) If $iCountdown < 57344 Then Return SetError(1, 0, "") ; Out of options Local $sTestChar For $i = $iCountdown To 57344 Step -1 $sTestChar = ChrW($i) $iCountdown -= 1 If Not StringInStr($string, $sTestChar) Then Return $sTestChar EndIf Next Return SetError(1, 0, "") ; Out of options EndFunc ;==> __GetSubstitute Edit: The code above is pretty junk (my part at least) so I wanted to improve it... This will return an array of arrays based on the excel file #include <Array.au3> Func _GetExcelArrays($fnImportFile) $oExcel = ObjCreate("Excel.Application") $oExcel.Visible=False $oBook= $oExcel.Workbooks.Open($fnImportFile) $sheetCount=$oBook.Worksheets.Count Local $aReturnArray[$sheetCount] For $x=1 to $sheetCount $oSheet=$oBook.Worksheets($x) $oSheet.Activate $fnMaster=@TempDir&"\"&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&Random(0,24)&Chr(Round(Random(0,24),0)+64)&".csv" $oSheet.SaveAs($fnMaster, 6) $aReturnArray[$x-1]=_CSVSplit(FileRead($fnMaster)) FileDelete($fnMaster) Next $oBook.Close(False) $oExcel.Quit Return $aReturnArray EndFunc

Hi there I have searched and found many posts on READING CSV. What I need to do is WRITE CSV. Typically I have a Listview, I use @guinness's excellent _GUICtrlListView_CreateArray to read that LV to Array. Currently I use _FileWriteFromArray to write the output. It is fast and it is easy. The benefits of using these Array functions are their inherit flexibility. The code is portable. Plug it into LV report and you can write to file instantly. No formatting issues. To be sure, my problem is not with the Listview or Array. My problem is with the file write to CSV: it should be fast, accurate and efficient. Part of the problem is Microsoft Excel's insistence that my CSVs are not what they appear to be... I am inspired by the simplicity of SQLITE3.EXE's dot command options: .headers on .mode csv .once output.csv Problem is that I want to export the text packed in characters, such as ' " ' with fields delimited by another character, such as ' , '. Current method using _FileWriteFromArray outputs data like this 12-04-2016| 0.00| 131.00|131|Description 12-04-2016| 0.00| 132.00|132|Description 132 rece 12-04-2016| 998.00| 0.00|998|Description Receipt 12-04-2016| 0.00| 900.00|900|Description What I want to achieve is this: "12-04-2016"," 0.00"," 131.00","131","Description" "12-04-2016"," 0.00"," 132.00","132","Description 132 rece" "12-04-2016"," 998.00"," 0.00","998","Description Receipt" "12-04-2016"," 0.00"," 900.00","900","Description" I have done this. The long way. I have written the output one $aResult[$i][1] item at a time, spaced with the $text & $field markers. I have placed this in an array with additional columns just to fit in the formatting. I have also played with the idea of inserting the array into a sqlite db, then run sqlite3's dot commands on that. Is there a different method? Is there an efficient method to specify FileWriteFromArraytoCSV? Does anyone have ideas? I am sure I will know how to code this, I just need advice on the most efficient way of going about it. If I missed any threads, please enlighten me. Thanks for reading

Hi all, I have been playing around with AutoIt for a couple weeks now, just making random things for fun. There is one thing I cannot wrap my head around for the life of me.. Here is what I am wanting to do: I have a csv file with this following data/columns: Hey,Hey1,Hey2,Hey3 Hello,Hello1,Hello2,Hello3 Hi,Hi1,Hi2,Hi3 (let's say this csv file is located C:\Test Folder\Test.csv) So the csv is three rows(for this example) and has four columns. I cannot figure out how to get this data out and store it into an array that I can use to type each column into 4 different word docs that are open. I do not need help with moving the mouse to each word doc, but i just don't know how to get the data out, put it into an array. Once I have the data in an array, I know I can figure out how to loop through, but getting to that point is my problem. Any help would be much appreciated.

hello im trying to get info from a csv file into a 2d array. im not sure exactly where I am going wrong. I can get it into a 1d array but the 2d part is really troubling for some reason. can anyone pint me in the right direction. I provided my code. Inventory (test).au3

Dim $oneDarray $oneDarray=StringSplit($Rawfile, @CRLF, 1) $columnsCounter = stringsplit($oneDarray[2],",") GUICtrlSetData ( $Output,"*Group columns Detected from csv:"& $columnsCounter[0] &@CRLF, @CR) ConsoleWrite("*Group columns Detected from csv:"& $columnsCounter[0] &@CRLF) Dim $twoDarray[$oneDarray[0] + 1][$columnsCounter[0] + 1] For $x = 1 to ($oneDarray[0]) $oneRow = stringsplit($oneDarray[$x],",") For $y = 1 to ($oneRow[0]) $twoDarray[$x][$y] = $oneRow[$y] Next Next Log Output Array variable has incorrect number of subscripts or subscript dimension range exceeded.: $twoDarray[$x][$y] = $oneRow[$y] ^ ERROR So I have a csv file that I'm breaking up to do string parsing for information from and I've run into a problem I'm not sure how to solve. In the code shown above I am creating the master 2d array that holds all the values from the csv. The problem the code runs into starts on this line: $oneRow = stringsplit($oneDarray[$x],",") It worked great until I ran into a csv file that had commas in a txt field that were not related to the csv format.. example: "this is some text, and some more text", filedir, ipaddress, hostname,mmmbeer as CSV format this is 5 fields, however the stringsplit counts the comma in the text and identifies this as 6 fields. Any ideas how I can somehow not include the comma in quotes in stringsplit? Thanks, Bob

Good morning, Apologies if this thread already exists or I've completely missed the answer in a similar thread. I'm new to Autoit since yesterday, albeit that I've had an account for 3 years, I'm only starting to get into it. I am trying to create a script which will read my CSV row by row and create a variable per cell on that row. In my job I'm going to need to fill out a webform 100 times over so I would like to input each variable into my chosen field on this webform and repeat. I've been playing with AutoIT and this is what I've done so far, am I on the right track? I was thinking of using the StringSplit functions but I am a bit clueless to be honest. Any help would be Great to get me started. #include <file.au3> #include <FileConstants.au3> #include <array.au3> _BrowseForFile() Global $aOutput, $FilePath _FileReadToArray ($FilePath,$aOutput,4,",") _ArrayDisplay($aOutput) Func _BrowseForFile() ;Sets $FilePath global $FilePath=FileOpenDialog("Browse for CSV",@DesktopDir,"csv files (*.csv)",$FD_MULTISELECT + $FD_FILEMUSTEXIST) EndFunc

Good Morning All, I have an application that takes csv files - sorts, finds, and tries to make it's own individual csv files... Everything runs smooth until I get to the "_myCSV2DCreator" on extremely large CSV files... like 200+ MB with 100,000 + lines. I get an "out of memory" error from AutoIT when I convert the 2D array to a CSV file using this function "_myCSV2DCreator". I get an "out of memory" error when running the _ArrayInsert($aNewArray, "0", $columnheaders, "", ","). I believe it takes whatever array is in memory and doubles it's size as it writes just one row of data. Any ideas on how to get around this? I'm stumped... Func GEN_SeparateOutCSV03() SendAndLog("GEN_SeparateOutCSV03", $tempzipdir & '\' & $LogFileName01, True) SplashTextOn($ProgramTitle, 'Separating CSV files from data...', 400, 60, (@DesktopWidth / 2) - 200, 10, "", "") Sleep ($sleeptime) $array01 = $twoDarray $aUniqueHostname = _ArrayUnique ($array01, 1) ;_ArrayDisplay ($aUniqueHostname, "UniqueHostname ") For $i01 = Ubound($aUniqueHostname) - 1 to 0 Step - 1 For $j01 = Ubound($array01) - 1 to 0 Step - 1 If $array01[$j01][1] == $aUniqueHostname[$i01] and StringRegExp($array01[$j01][5], "MY_VALUE.txt") then ;MsgBox(0, "Computer and MY_VALUE.txt", $aUniqueHostname[$i01] & " : " & $array01[$j01][5]) $FileName01 = $tempzipdir & "\" & $array01[$j01][3] & "_" & $aUniqueHostname[$i01] & "_" & $array01[$j01][2] & ".csv" ; MsgBox(0, "File Name", $FileName01) Local $avResult = _ArrayFindAll($array01, $aUniqueHostname[$i01], 0, 0, 0, 0, 1) ;_ArrayDisplay($avResult, "$avResult") Local $aNewArray = "" Local $aNewArray[UBound($avResult)][UBound($array01, 2)] SplashTextOn($ProgramTitle, 'Loop - Array search for unique hostname', 400, 60, (@DesktopWidth / 2) - 200, 10, "", "") Sleep ($sleeptime) For $i = 0 To UBound($avResult) -1 ; Loop through the returned index numbers. For $j = 0 To UBound($array01, 2) -1 ; Loop through each of the columns. $aNewArray[$i][$j] = $array01 [$avResult[$i]] [$j] ; Populate the new array. Next Next SplashTextOn($ProgramTitle, 'Loop - Column header modification', 400, 60, (@DesktopWidth / 2) - 200, 10, "", "") Sleep ($sleeptime) ;_ArrayDisplay($twoDarray, "Removed 'User-defined Rules:' in column 3") ;_ArrayInsert($aNewArray, "0", $columnheaders, "", ",") _ArrayInsert($aNewArray, "0", $columnheaders, "", ",") ;_ArrayDisplay($aNewArray, "Inserted 0") ;MsgBox (0, "Out of the loop", "Out of the loop - File Write From Array") _myCSV2DCreator($tempzipdir & "\beta_" & $array01[$j01][3] & "_" & $aUniqueHostname[$i01] & "_" & $array01[$j01][2] & ".csv", $aNewArray, True) ;_FileWriteFromArray($FileName01, $aNewArray) ExitLoop EndIf Next Next SplashTextOn($ProgramTitle, 'Please wait a moment...', 400, 60, (@DesktopWidth / 2) - 200, 10, "", "") Sleep ($sleeptime) SplashOff() SendAndLog("GEN_SeparateOutCSV03 - Okay", $tempzipdir & '\' & $LogFileName01, True) MsgBox(262192, $ProgramTitle, "All files stored here:" & @CR & @CR & $tempzipdir) SendAndLog("Final Message Box - Exit Okay", $tempzipdir & '\' & $LogFileName01, True) Exit EndFunc Func _myCSV2DCreator($hFile, $avArray, $bEraseCreate = True) SplashTextOn($ProgramTitle, '2D to CSV file create', 400, 60, (@DesktopWidth / 2) - 200, 10, "", "") Sleep ($sleeptime) If $bEraseCreate Then FileClose(FileOpen($hFile, 2)) Local $sHoldString = "" For $x = 0 To UBound($avArray) - 1 For $i = 0 To UBound($avArray, 2) - 1 $sHoldString &= $avArray[$x][$i] & "," Next $sHoldString &= @CRLF Next Return FileWrite($hFile, StringTrimRight($sHoldString, 3)) EndFunc (out of memory) Is there a better method / solution that I should be using? Thanks Everyone!

I'm trying to do a few queries and have them record the results into a CSV file but I'm running into an unusual error. When I had my code write a line at the end of all the queries, I got a ton of duplicated data, so I tried to tweak when it writes to the file to eliminate redundancies. I changed the code so now it doesn't do a @CRLF until the end of the set of queries and it looks perfect on the console output, but when I open the file itself, it has all sorts of spacing. Here is the code: $sqlRs2.open ($Query2, $sqlCon) While Not $sqlRs2.EOF $Field1= $sqlRs2.Fields ('MPR').Value $Field2 = $sqlRs2.Fields ('ENT').Value $Field3 = $sqlRs2.Fields ('ROD').Value $Field4 = $sqlRs2.Fields ('ONTR').Value $Field5 = $sqlRs2.Fields ('OCAL').Value $EndDate = $sqlRs2.Fields ('End').Value $StartDate = $sqlRs2.Fields ('Start').Value ConsoleWrite($Field1 & "|" & $Field2 & "|" & $Field3 & "|" & $Field4 & "|" & $Field5 & "|" & DTFormat($StartDate) & "|" & DTFormat($EndDate) & "|") ; Write results to file FileWriteLine($fOutFile, $Field1 & "|" & $Field2 & "|" & $Field3 & "|" & $Field4 & "|" & $Field5 & "|" & DTFormat($StartDate) & "|" & DTFormat($EndDate) & "|") ; Write results to file $sqlRs6.open ($Query6, $sqlCon) While Not $sqlRs6.EOF $ID = $sqlRs6.Fields ('ERS').Value $Type = $sqlRs6.Fields ('ERS1').Value $DBRelationship = $sqlRs6.Fields ('ERT').Value DBRelationship() If $Type = 'F' Then $sqlRs7.open ($query7, $sqlCon) $Value1 = StringReplace($sqlRs7.Fields ('RIN' ).Value," ","") $Value2 = StringReplace($sqlRs7.Fields ('R70' ).Value," ","") $Value3 = StringReplace($sqlRs7.Fields ('OM' ).Value," ","") $Other = $Value & " " & $Value2 & " " & $Value3 ConsoleWrite($Other & " " & $DBRelationship & "; ") ; Write results to file FileWriteLine($fOutFile, $Other & " " & $DBRelationship & "; ") ; Write results to file $sqlRs7.close EndIf $sqlRs6.MoveNext WEnd ConsoleWrite(@CRLF) ; Write results to file FileWriteLine($fOutFile, @CRLF) $sqlRs6.close $sqlRs2.MoveNext The console out looks like this: and in the file itself, this is what I get: I tried opening it in notepad++ and I can confirm that there is a @CRLF at the end of each of the lines. Does filewriteline only create a new line? How would I get my file to look like the console output?

I'm working with two csv files that I'm parsing into two arrays. I'm then comparing them to find the duplication and remove them from the first array. This works great on 100 or so records, but I'm trying to compare arrays with more than 70,000 records so I wanted to add in a loading bar so I can tell how far/how much longer it will take. This is my code: ProgressSet(0,0&"%","Checking already searched") $aProcess = _ParseCSV($oOutfile,"|","",0) $aAlreadyChecked = _ParseCSV($AlreadyProcessed,"|","",0) For $a = UBound($aProcess) -1 to 0 Step -1 for $b = 0 to UBound($aAlreadyChecked) -1 if $aProcess[$a][0] = $aAlreadyChecked[$b][0] Then _ArrayDelete($aProcess, $a) MsgBox(0,"",($a-UBound($aProcess)) & @TAB & $b) ProgressSet(($b/$a),Round($b/$a)&"%","Cleaning up") ExitLoop EndIf Next Next I cannot get the percentage logic to show anything that seems rational or accurate. Does anyone know of a more efficient way of doing this or how to fix the progressset to actually show how far in the process it already is?

Hi guys, I'm getting some issues with timing: it's taking ages What I have: 46 .csv files circa (number may vary) Each of those has 6100 rows What I have to do: Compare a certain column (number) and if it's higher than the current one, update it I'm obviously doing this with 2 For cycles, one into each other but it's really slow. It takes about 65/70 seconds every 1000 records. Is there something else I can try to speed up the process? This is the For part I have: For $i=1 To $ListOfCSVs[0] Local $tempArray= 0 _FileReadToArray($tmpFiles&'\'&$ListOfCSVs[$i],$tempArray) ;MsgBox(0,$i,$tmpFiles&'\'&$ListOfCSVs[$i],$tempArray) $d=0 For $d=1 To $CompleteArray[0][0] ;SplashTextOn($i, "ID:"&$d, -1, -1, -1, -1, 4, "", 24) $searchSAM=_ArraySearch($tempArray, $CompleteArray[$d][0],2,0,0,1) If $searchSAM > 0 Then ;If found $searchSAM holds the position of the item just found $tmpItem=StringSplit($tempArray[$searchSAM],",") ;We're interested on the last row of $tmpItem >> $tmpItem[$tmpItem[0]] If $tmpItem[$tmpItem[0]] <> '' Then If $tmpItem[$tmpItem[0]] > $CompleteArray[$d][2] Then $CompleteArray[$d][2] = $tmpItem[$tmpItem[0]] EndIf EndIf EndIf ;Splashoff() Next Next I used the SplashTextOn to help me checking the speed of the cycle.. Cheers

Pleas help me , I am converting HTML to csv using the command stringreg exp. In the example belot, the field Help is not detected. How to change my regexp ? #include <Array.au3> $sString = "<td NOWRAP>cel1</td><td NOWRAP>cel2</td><td NOWRAP>cel3</td><td>Help</td><td NOWRAP>cel4</td>" $aReturn = StringRegExp($sString, '(?s)(?i)<td NOWRAP>(.*?)</td>', 3) _ArrayDisplay($aReturn) thnx.

Hi all, I need to convert a .csv file that after conversion will fill an array for futher usage. function used/not used: stringsplit(), stringregexp(),stringinstr(), stringreplace() I know the best way is to use stringsplit given a delimiter (,). But this is not possible here since: Here is some lines fo the .csv Game Title,Quantity,For Sale,Platform,Rating,Number,New After Life,5,2,PC,PG1,23332,Yes Max Payne,4,0,Wii,PG3,109,No Fifa 2009,11,2,DS,PG2,976,Yes "Hellgate, London",3,2,PC,PG2,112,No Logitech Mouse,12,4,mouse, ,No Pinball 2010,1,1,PS3,PG1,065,No As you can see: "Hellgate, London",3,2,PC,PG2,112,No will not work with stringsplit() since the "," it's inside the name of the object Logitech Mouse,12,4,mouse, ,No Here I have blank spaces between "," and the field "Rating" is missing. This is due to the .csv format. When Platform <> PC,Wii,DS,PS1,PS2,PS3,XB,X3 then I need to set Platform = Hardware and Rating = the value found in place of platform (in this case Platform = N, Rating = Hardware) Once this is finished and I have my array built, I need to add 2 more columns to it. I get the columns _arraysearch()ing between the just created array and another file (.xlm). If i found the item I need I add the prices, if not, skip. So now I use this code (working): $exportfile = @ScriptDir & "\filelist.csv" Dim $mydb Global $file2ftp = @TempDir & "\temp2db.txt" Dim $csv_list[1][7] Global $rows Func _exp2db_x() Local $iCounter = 0, $array2db[10000] Local $user2db Local $k = 0 Dim $csv_array _FileReadToArray($exportfile, $csv_array) _FileReadToArray($price_file, $mydb) _ArraySort($mydb) $begin = TimerInit() For $i = 1 To UBound($csv_array) - 1 If StringInStr($csv_array[$i], ", ,") Then ; i'm looking for a ", ," in lines $string = StringReplace($csv_array[$i], ", ,", ",@,", 1) ; if found I change it Else $string = $csv_array[$i] EndIf If StringInStr($string, ", ") Then $string = StringReplace($string, ", ", "^", 1); If I find "," inside the name I need to change it $name_s = _StringBetween($string, "", ",") $name = _cleanfordb($name_s[0]) ; Need to reconvert name with "," and remove " if present $string = StringReplace($string, ",", "*", 1) ; since I can't use stringsplit() i need different separators $string = StringReplace($string, ",", "#", 1) ; since I can't use stringsplit() i need different separators $forsale = StringRegExp($string, "#(.*?),", 1) ; for sale If $forsale[0] > 0 Then $k +=1 $string = StringReplace($string, ",", "_", 1) ; since I can't use stringsplit() i need different separators $platform = StringRegExp($string, "_(.*?),", 1) ;platform $rating = StringRegExp($string, ",(.*?),", 1) ;rating If $platform[0] <> "PC" And $platform[0] <> "DS" And $platform[0] <> "Wii" And $platform[0] <> "XB" And $platform[0] <> "X3" And $platform[0] <> "Junk" Then $rating[0] = "Hardware" ; fixed value $platform[0] = "N" ; fixed value EndIf If $platform[0] = "Junk" Then $rating[0] = $platform[0] $platform[0] = "None" EndIf $new = StringRight($string, 2) ; just lasr 2 chars for last item in row If $new = "es" Then $new = "Yes" ReDim $csv_list[$k + 1][7] $csv_list[$k][0] = $name $csv_list[$k][1] = $platform[0] If $rating[0] <> "@" Then $csv_list[$k][2] = $rating[0] $csv_list[$k][3] = $new $csv_list[$k][4] = $forsale[0] ; now I build the string to check vs the main db (I need a partial search) $string = "<z:row c0='" & _normalize_db($csv_list[$k][0]) & "' Rating='" & $csv_list[$k][2] & "' New='" & $csv_list[$k][3] & "' Platform='" & $csv_list[$k][1] ; _normalize_db just change exotic chars into xml_readable sequence $check_string = _ArraySearch($mydb, $string, 39, UBound($mydb), 0, 1) ; first 38 lines is the xml header so I can skip If $check_string <> -1 Then $csv_list[$k][5] = _sellp($check_string) ; I can have 2 possible prices in main DB, best price and street price. If bestprice = 0 I'll use StreetPrice EndIf Next ConsoleWrite("Time to complete: " & TimerDiff($begin) & @CRLF) EndFunc ;==>_exp2db_x Func _cleanfordb($k) If StringInStr($k, "^") Then $k = StringReplace($k, "^", ", ") ; ^ If StringInStr($k, '"') Then $k = StringMid($k, 2, StringLen($k) - 2) Return $k EndFunc ;==>_cleanfordb Func _sellp($cc) $p = StringRegExp($mydb[$cc], "c6='(.*?)' c7",1) If $p[0] = 0 Then $p = StringRegExp($mydb[$cc], "c7='(.*?)'/>",1) Return $p[0] EndFunc ;==>_sellp Time to complete is 137,000ms, 2'17'', for 1,243 lines of csv (and 30,000 lines of main DB) I need to work, during normal phases, with 10,000 to 30,000 lines so time to complete will be really too high. I ask you, great programmers, if there is a way to optimize this routine (i'm not able to modify the csv since I get it this way from the Store Application Thanks! M.