Jump to content

Old Question - Filereadtoarray.


Recommended Posts

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
Link to comment
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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
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

Link to comment
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
Link to comment
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

Link to comment
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)

Link to comment
Share on other sites

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
Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...