SuperFletch Posted August 28, 2009 Share Posted August 28, 2009 (edited) Hi folks,I've been having trouble with two things:First - understanding the help file for _FileReadtoArray, and second, understanding some of the existing old threads covering this subject.I'll explain what I want to do and post code.------------I've exported a large number of emails from outlook to CSV, I have 8 fields from each email that I need to get into an array. If it matters - these are the fields:Subject,Body,From:(Name),From:(Address),From:(Type),To:(Name),To:(Address),To:(Type)So far so good - I can get the data into an array but my array lists everything under one column.I want 8 columns instead, one for each field of the message - I think this is called a multi dimensional array??Is there a simple way to tell my code to make the array have 8 fields to begin with and use the commas in the file as the separation?My code so far is:#Include <File.au3> #Include <Array.au3> $File=("C:\My File.csv") $Array=1 _FileReadToArray($File, $Array) sleep(2000) _ArrayDisplay($Array, "Array: ListView Display") exitAny help would be great - I'll probably be back in the forum again very soon because once I have the array playing nicely I'm going to properly delve into String Regular Expression for my first time.TIA Edited August 28, 2009 by SuperFletch Link to comment Share on other sites More sharing options...
Manjish Posted August 28, 2009 Share Posted August 28, 2009 Hi u need to define a multi dimensional array as this: dim $avArray[20][8] This will have 20 rows and 8 columns!! [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
SuperFletch Posted August 28, 2009 Author Share Posted August 28, 2009 (edited) Thanks Manjish, I'll adapt my code as follows: #Include <File.au3> #Include <Array.au3> $File=("C:\My File.csv") ;Use something to work out how many rows (just the number of emails but nicer it its automated) to a variable. dim $Array[$ROWS][8] $Array=1 ;I guess I no longer need this $Array=1 line as the array is already defined above but empty... _FileReadToArray($File, $Array) sleep(2000) _ArrayDisplay($Array, "Array: ListView Display") exit Edited August 28, 2009 by SuperFletch Link to comment Share on other sites More sharing options...
jvanegmond Posted August 28, 2009 Share Posted August 28, 2009 (edited) I think documentation on _FileReadToArray is a bit confusing. It says nothing about the delimiter used, which I am assuming is a new line character. For your script, I don't think you need to use _FileReadToArray. It's a comma separated file, and not a new line separated file. : )A custom approach is probably better. _FileListToArray however is useful for looping through all the available email files.Your script will be something like this:#Include <File.au3> #Include <Array.au3> $FileList = _FileListToArray("C:\", "*.csv", 1) If @Error=1 Then MsgBox (0,"","No Files\Folders Found.") Exit EndIf $num = $FileList[0] Dim $Result[$num][8] ; make a new array, one row for each file and one column for the 8 data fields from the file For $i = 1 to $num ; read file and split it by comma character. then put the data into the array $data = StringSplit(FileRead($FileList[$i]), ",") $Result[$num][0] = $data[1] ; subject $Result[$num][1] = $data[2] ; body ;; etc etc, ofcourse this can be done in another array.. ( two dimensional array, and two for-loops = winsauce ) Next _ArrayDisplay($Result) ExitEdit: Ah, I see you have them all in the same file. That was a little confusing. Only get out the middle part of the script. Edited August 28, 2009 by Manadar github.com/jvanegmond Link to comment Share on other sites More sharing options...
99ojo Posted August 28, 2009 Share Posted August 28, 2009 Hi, maybe like this: #include <file.au3> #include <array.au3> $mycsv = "C:\Truro Netop.csv" $count = _FileCountLines ($mycsv) Dim $arArray [$count] [8] $file = FileOpen ($mycsv, 0) For $i = 0 To $count - 1 $line = FileReadLine ($file) $split = StringSplit ($line, ",") ; For $k = 1 To UBound ($split) - 1 $arArray [$i] [$k - 1] = $split [$k] Next Next FileClose ($file) _ArrayDisplay ($arArray) ;-)) Stefan Link to comment Share on other sites More sharing options...
SuperFletch Posted August 28, 2009 Author Share Posted August 28, 2009 Thanks for the quick replies everyone, a bit too quick for me to take them all in: I adapted my code as below, this creates an array with 8 columns and 20 rows but then when I use _FileReadtoArray it changes the Array back to a 160 row, single column array. I tried putting the data into the array first and then setting the number of rows and columns, but that wiped the data out of it. Should I use _filelisttoarray instead? Thanks for help everyone. #Include <File.au3> #Include <Array.au3> $File=("C:\My File.csv") ;Use something to work out how many rows (just the number of emails but nicer it its automated) to a variable. $ROWS=20 dim $Array[$ROWS][8] _FileReadToArray($File, $Array) sleep(2000) _ArrayDisplay($Array, "Array: ListView Display") exit Link to comment Share on other sites More sharing options...
jvanegmond Posted August 28, 2009 Share Posted August 28, 2009 Can you provide a part of the csv file? github.com/jvanegmond Link to comment Share on other sites More sharing options...
SuperFletch Posted August 28, 2009 Author Share Posted August 28, 2009 Yeah - no worries - it's attached. (You need to change it to a CSV extension - those aren't allowed by the forum) What Stefan suggested keeps the format of the array in 8 columns but it doesn't display properly. This is because because the delimiter is a comma and there are a lot of comma's in use within the individual sections (particularly in the body section of the email). I guess I need a way to change the delimiter to something different before anything else. Then I need to display the array nicely based on the new delimiter. My File.TXT Link to comment Share on other sites More sharing options...
jvanegmond Posted August 28, 2009 Share Posted August 28, 2009 .... Sorry for askin, but are there any other formats you can export to? I'm in a bit of a sweat here. github.com/jvanegmond Link to comment Share on other sites More sharing options...
SuperFletch Posted August 28, 2009 Author Share Posted August 28, 2009 It's from outlook (2003) - I'm looking at plugins at the moment. It also lets me export to Access, Excel (xls), Notepad (txt as a Tab Separated Value). The XLS isn't much different to the CSV if you open them in Excel (I guess the CSV has a delimiter and XLS doesn't) Link to comment Share on other sites More sharing options...
SuperFletch Posted August 28, 2009 Author Share Posted August 28, 2009 Just out of interest - I know it's off topic but does anyone know any decent plugins for Outlook I could use to help me with exporting the data nicely? TIA Link to comment Share on other sites More sharing options...
jvanegmond Posted August 31, 2009 Share Posted August 31, 2009 (edited) I spent almost the entire weekend thinking about it and a few hours writing this.. But I finally managed to successfully parse your csv file!! I've completely commented out the code and I hope that you have no more questions after reading this. expandcollapse popup#include <Array.au3> ; read in the email csv $data = FileRead("test.csv") ; gets a unique string that has no occurances in the data $unq = _GetUniqueString($data) ; replaces all double quotes with a unique string (because I can't figure out the regular expression) _ReplaceDoubleQuotes($data, $unq) ; i couldnt find a way to exclude a double "" in here from matching with the start of the string, so i replaced it with a unique string $regexp = StringRegExp($data, '(?s)(?:"(.*?)")', 3) If @error Then MsgBox(0, "", @error) ; just in case it goes wrong ; sets back all the double quotes _RestoreDoubleQuotes($regexp, $unq) ; if you wish you can see the full array here ; _ArrayDisplay($regexp) ; Lets parse through the newly created array with a while loop $n = 0 While 1 Dim $email[8] ; create a new array to hold the email information ; this iteration we start going through the regexp array on: $base = ($n * 7) ; if this is larger then the actual regexp array, then we have all the data and we can stop if ($base + 7 > UBound($regexp)) Then ExitLoop EndIf ; copy all the data into a new array For $i = $base to $base + 7 $email[$i - $base] = $regexp[$i] Next ; process the email somehow _ProcessEmail($email) ; go on with the next iteration $n += 1 WEnd ; All done for, notify user MsgBox(0,"","The email conversion script is done") Func _ProcessEmail($email) ;_ArrayDisplay($email) EndFunc Func _ReplaceDoubleQuotes(ByRef $data, $unq) ; replace any double "" with a unique string, so i can reset that later $data = StringReplace($data, '""', $unq) EndFunc Func _RestoreDoubleQuotes(ByRef $arraydata, $unq) ; restore all the unique strings with double "" :D For $i = 0 to UBound($arraydata)-1 $arraydata[$i] = StringReplace($arraydata[$i], $unq, '""') Next EndFunc Func _GetUniqueString($s) ; gets a unique string not available in $s While 1 ; tries to generate a random string and find it in the data $r = _GetRandomString() If (Not StringInStr($s, $r)) Then ; it doesn't occur anywhere else so it's unique Return $r EndIf Wend EndFunc Func _GetRandomString() ; a simple function to generate a random string. This will never include any " characters so its ideal for our csv. $r = "" For $i = 0 to Random(10, 20, 1) $r &= Chr(Random(35, 126, 1)) Next Return $r EndFunc Edited February 16, 2012 by Manadar github.com/jvanegmond Link to comment Share on other sites More sharing options...
SuperFletch Posted September 1, 2009 Author Share Posted September 1, 2009 (edited) Wow - that's amazing, I had kind of given up on this thread, thank you... I was looking for help, not to buy an answer but this is really appreciated. That is awesome. Edited September 1, 2009 by SuperFletch Link to comment Share on other sites More sharing options...
Jfish Posted September 1, 2009 Share Posted September 1, 2009 If you save your text file with a *.csv extension you could do this: #Include <Excel.au3> #include<Array.au3> dim $oExcel = _ExcelBookOpen(@ScriptDir&"\text.csv") dim $myArray = _ExcelReadSheetToArray($oExcel) _ArrayDisplay($myArray) Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt 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