Sign in to follow this  
Followers 0
SuperFletch

Old Question - Filereadtoarray.

14 posts in this topic

#1 ·  Posted (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")

exit

Any 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 by SuperFletch

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

#3 ·  Posted (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 by SuperFletch

Share this post


Link to post
Share on other sites

#4 ·  Posted (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)

Exit

Edit: 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 by Manadar

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

:D

My File.TXT

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#12 ·  Posted (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.

#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 by Manadar

Share this post


Link to post
Share on other sites

#13 ·  Posted (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 by SuperFletch

Share this post


Link to post
Share on other sites

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

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