Jump to content
Sign in to follow this  
Docfxit

How to reformat text

Recommended Posts

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 by docfxit

Share this post


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

Share this post


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

My little company: Evotec (PL version: Evotec)

Share this post


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

My little company: Evotec (PL version: Evotec)

Share this post


Link to post
Share on other sites
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 by docfxit

Share this post


Link to post
Share on other sites
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 by docfxit

Share this post


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

:rolleyes:

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 by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


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

:rolleyes:

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

Share this post


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

:rolleyes:


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


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

:rolleyes:

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

#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

Share this post


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

:rolleyes:

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


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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 by docfxit

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  

×