How to reformat text

[Docfxit]

I have a csv file that I need to format and write out a txt file. After I format it I plan to print it in booklet format with another program.

This is one record of the file:

LastName,FirstName,AddressLineOne,City,State,ZipCode,,,,,,,,,,,,"CompanyName, Inc.",,BusinessPhone#,Business,CellPhone#,Cellular,docfxit@theoffice.la,E-mail,,Fax,,Home,,Web Site,Anniversary 11/29/2006 FirstLastName;Birthday 03/24/1950 FirstLastName;,,,,"1

2

3

4

5

6

7

8

9

10

11

12"

I'd like the output txt file to look like:

FirstName LastName

AddressLineOne

City, State ZipCode

CompanyName

Business: WorkPhone

Celular: CellPhone

Email: EmailAddress

Fax:

Home:

Web Site:

Anniversary 11/29/2006 FirstLastName

Birthday 03/15/1985 FirstLastName

1

2

3

4

5

6

7

8

9

10

11

12

If someone could just get me started on how to do this I can handle the details.

Thank you,

Docfxit

Edited June 7, 2007 by docfxit

[ResNullius]

Start with FileRead() and StringReplace().

HINT: StringReplace() to replace your commas with @CRLF

HINT-HINT: Watch out for double commas where you have empty fields.

See what you can do, and if you need more help, post what you've got so we can see.

[MadBoy]

I would do this diffrent way.

So basically you have a file with multiple entries divided with coma and have a multiple lines with information? And you want to convert it to one file (all ppl in one file just in diffrent format) or to multiple files (one per person?)

Anyway:

1. _FileReadToArray --> use this to read whole .csv file to array

2. Go in a loop thru Array and pick one line from array

3. StringSplit --> use this to split lines from an array with coma as delimeter which will create new array but easy to parse

4. Then use _DisplayArray to see what you have

This is example code:

#include <file.au3>
#include <array.au3>

Dim $aRecords
If Not _FileReadToArray("C:\file.txt",$aRecords) Then
   MsgBox(4096,"Error", " Error reading log to Array     error:" & @error)
   Exit
EndIf
_ArrayDisplay($aRecords) ; just so you can see what you have
For $x = 1 to $aRecords[0]
    $new_array = StringSplit($your_array[$x], ",",1) ; to split each line from an array into another array
    _ArrayDisplay($new_array) ; just so you can see what you have in new array
    ; Here you have to add code that will put this into a file in a format you want it to using FileWrite, FileWriteLine or similar ;)
Next

[MadBoy]

Mmm, i added the writelines so you can have easier go on this. However i haven't tested this at all since i don't have any files like this But you should be able to work from that.

#include <file.au3>
#include <array.au3>

Dim $aRecords
If Not _FileReadToArray("C:\file.txt", $aRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf
_ArrayDisplay($aRecords) ; just so you can see what you have
For $x = 1 To $aRecords[0]
    $new_array = StringSplit($your_array[$x], ",", 1) ; to split each line from an array into another array
    _ArrayDisplay($new_array) ; just so you can see what you have in new array
    ; Here you have to add code that will put this into a file in a format you want it to using FileWrite, FileWriteLine or similar ;)
    ; Something like this maybe:
    $file = FileOpen("output.txt", 1)
    ; Check if file opened for writing OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file.")
        Exit
    EndIf
    FileWriteLine($file, "FirstName: " & $new_array[1] & @CRLF)
    FileWriteLine($file, "LastName:" & $new_array[2] & @CRLF)
    ; and so on (make sure to add some check for amount of elements in array
    FileClose($file)
Next

[Docfxit]

Mmm, i added the writelines so you can have easier go on this. However i haven't tested this at all since i don't have any files like this But you should be able to work from that.

#include <file.au3>
#include <array.au3>

Dim $aRecords
If Not _FileReadToArray("C:\file.txt", $aRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf
_ArrayDisplay($aRecords) ; just so you can see what you have
For $x = 1 To $aRecords[0]
    $new_array = StringSplit($your_array[$x], ",", 1) ; to split each line from an array into another array
    _ArrayDisplay($new_array) ; just so you can see what you have in new array
    ; Here you have to add code that will put this into a file in a format you want it to using FileWrite, FileWriteLine or similar ;)
    ; Something like this maybe:
    $file = FileOpen("output.txt", 1)
    ; Check if file opened for writing OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open file.")
        Exit
    EndIf
    FileWriteLine($file, "FirstName: " & $new_array[1] & @CRLF)
    FileWriteLine($file, "LastName:" & $new_array[2] & @CRLF)
    ; and so on (make sure to add some check for amount of elements in array
    FileClose($file)
Next

This is really sooper. This will be a perfect start to get me going. I'm working on it now.

Thank you very much,

Docfxit

Edited June 7, 2007 by docfxit

[Docfxit]

I am running into a problem. The CSV file that is written has fields that contain CRLF. When I use the code:

#include <file.au3>
#include <array.au3>

Dim $aAllRecords
If Not _FileReadToArray("C:\Program Files\AnyTime Deluxe\Data\address 07-06-08.csv", $aAllRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf
Dim $aRecordOut[_ArrayMaxIndex( $aAllRecords, 0, 1)]
_ArrayDisplay($aAllRecords, "Record Input") ; just so you can see what you have
For $x = 1 To $aAllRecords[0]

    $aRecordOut = StringSplit($aAllRecords[$x], ",", 1) ; to split each line from an array into another array
    _ArrayDisplay($aRecordOut, "Record To Re-Format") ; just so you can see what you have in new array
    ; Here you have to add code that will put this into a file in a format you want it to using FileWrite, FileWriteLine or similar wink.gif
    ; Something like this maybe:
    $file = FileOpen("AnyTimeoutput.txt", 1)
    ; Check if file opened for writing OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open AnyTimeoutput.txt file.")
        Exit
    EndIf
    FileWriteLine($file, "FirstName: " & $aRecordOut[2] & @CRLF)
    FileWriteLine($file, "LastName:" & $aRecordOut[1] & @CRLF)
    If $aRecordOut[3] <> "" Then FileWriteLine($file, $aRecordOut[3] & @CRLF) ;Address Line 1
    ; and so on (make sure to add some check for amount of elements in array
    FileClose($file)
Next

It puts part of the record up to the first CRLF into one array element and the next part into a separate array element. I don't know how to load the entire record into one array so I can process it.

I don't know how to show the data in hex here.

This is all one record.

LastName,%-FirstName,"Address1

Address2",City,State,ZipCode,Country,,,,,,,,,,,,,Home,Home,Work,Work,Cell1,Cellular,Fax,Fax,Email1,E-mail,Email2,E-mail,,,,,"Divorced

Died"

But the CRLF's put it in multiple lines.

This is what the array looks like:

[0]|273

[1]|LastName,%-FirstName,"Address1

[2]|Address2",City,State,ZipCode,Country,,,,,,,,,,,,,Home,Home,Work,Work,Cell1,Cellular,Fax,Fax,Email1,E-mail,Email2,E-mail,,,,,"Divorced

[3]|Died"

Since the above is all one record all the info should be in one array element. When I read the CSV file into Excel it goes into one record. I don't know how Excel can tell the end of the record but reading this into the array it thinks a CRLF is the end of the record.

Thank you,

Docfxit

Edited June 19, 2007 by docfxit

[PsaltyDS]

Since the above is all one record all the info should be in one array element. When I read the CSV file into Excel it goes into one record. I don't know how Excel can tell the end of the record but reading this into the array it thinks a CRLF is the end of the record.

It is ignoring the @CRLF between paired quotes. To emulate that in AutoIt, you will have to substitute a token for @CRLF, but only when inside quotes, before doing the _FileReadToArray(). Then replace the token with @CRLF in the array.

P.S. Probably safer to use two tokens, one for @CR and one for @LF, since the DOS combo @CRLF may not always be used.

Edited June 19, 2007 by PsaltyDS

[Docfxit]

It is ignoring the @CRLF between paired quotes. To emulate that in AutoIt, you will have to substitute a token for @CRLF, but only when inside quotes, before doing the _FileReadToArray(). Then replace the token with @CRLF in the array.

P.S. Probably safer to use two tokens, one for @CR and one for @LF, since the DOS combo @CRLF may not always be used.

Thank you for the suggestion. I'm glad to hear there is a solution.

I have been trying to figure out how to write the code and I can't figure out how to do it. Does anyone maybe have a sample of how to do this?

Thank you,

Docfxit

[PsaltyDS]

I have been trying to figure out how to write the code and I can't figure out how to do it. Does anyone maybe have a sample of how to do this?

What you need is a clever StringRegExpReplace() that will find @CR or @LF, but only inside quotes, and replace them with something like '<:@CR:>' or '<:@LF:>'. Now if only we new somebody clever to write the RegExp pattern...

[Docfxit]

What you need is a clever StringRegExpReplace() that will find @CR or @LF, but only inside quotes, and replace them with something like '<:@CR:>' or '<:@LF:>'. Now if only we new somebody clever to write the RegExp pattern...

I'm trying to figure it out. This is what I have so far.

expandcollapse popup

#include <file.au3>
#include <array.au3>

Dim $aAllRecords

Global $file_array[1]
$CSVfile = FileOpen("C:\Program Files\AnyTime Deluxe\Data\address 07-06-08.csv", 0)

; Check if file opened for reading OK
If $CSVfile = -1 Then
    MsgBox(0, "Error", "Unable to open C:\Program Files\AnyTime Deluxe\Data\address 07-06-08.csv file.")
    Exit
EndIf

; Read in lines of text until the EOF is reached
While 1
    $RawLine = FileReadLine($CSVfile)
    If @error = -1 Then
        $file_array[0] = Ubound($file_array)-1
        ExitLoop
    EndIf
    ; Put $RawLine into $BuildRecord 
    ; If " is found look for second quote
        ; If second quote is not found read $RawLine
            ; Replace CR with something
            ; Replace LF with something
            ; Add $RawLine to the end of $BuildRecord
        ; Continue looking for second quote
    _ArrayAdd($file_array, $line)
Wend

FileClose($CSVfile)

_ArrayDisplay($file_array) ;Use this array

;Change this to use the array called $file_array
If Not _FileReadToArray("C:\Program Files\AnyTime Deluxe\Data\address 07-06-08.csv", $aAllRecords) Then
    MsgBox(4096, "Error", " Error reading log to Array     error:" & @error)
    Exit
EndIf
Dim $aRecordOut[_ArrayMaxIndex( $aAllRecords, 0, 1)]
_ArrayDisplay($aAllRecords, "Record Input") ; just so you can see what you have
For $x = 1 To $aAllRecords[0]
    $aRecordOut = StringSplit($aAllRecords[$x], ",", 1) ; to split each line from an array into another array
    _ArrayDisplay($aRecordOut, "Record To Re-Format") ; just so you can see what you have in new array
    ; Here you have to add code that will put this into a file in a format you want it to using FileWrite, FileWriteLine or similar wink.gif
    ; Something like this maybe:
    $file = FileOpen("AnyTimeoutput.txt", 1)
    ; Check if file opened for writing OK
    If $file = -1 Then
        MsgBox(0, "Error", "Unable to open AnyTimeoutput.txt file.")
        Exit
    EndIf
    FileWriteLine($file, "FirstName: " & $aRecordOut[2] & @CRLF)
    FileWriteLine($file, "LastName:" & $aRecordOut[1] & @CRLF)
    If $aRecordOut[3] <> "" Then FileWriteLine($file, $aRecordOut[3] & @CRLF) ;Address Line 1
    ; and so on (make sure to add some check for amount of elements in array
    FileClose($file)
Next

Any help would be appreciated (and welcome)

Thank you,

Docfxit

[PsaltyDS]

I'm trying to figure it out. This is what I have so far.

In the file read portion, you are reading the file line to $RawLine, but adding it to the array with just $Line.

Does FileReadLine give you the whole line where there was a @CRLF in quotes, or break it into two lines?

P.S. Still waiting for someone clever to step in an explain how easy it would be with RegExp's...

[Docfxit]

In the file read portion, you are reading the file line to $RawLine, but adding it to the array with just $Line.

That should read $BuildRecord instead of $Line. I tried editing it but the edit function isn't working. They just changed to a new version of the foum software this morning. Hopefully they know of the problem.

Does FileReadLine give you the whole line where there was a @CRLF in quotes, or break it into two lines?

It doesn't give me the whole line.

Thank you,

Docfxit

Edited June 19, 2007 by docfxit