Page 1 of 2 12>
Topic Options
#128370 - 2004-10-25 03:18 PM Reordering text file into CSV format
Anonymous
Unregistered


Hi I'm rediscovering Kix after a time in the wilderness.
Here's wait I am trying to do and would appreciate any help.

I have a list of names and addresses in a list format.. e.g

Name
Address
Street
City
Phone number
Fax

What I am tryig to do it remove the CRLF at the end of each line (which I have done using the fnStrip function.. I can concatenate a comma after each bit of info, but, what I am not sue how to do is.. count lines and put a LFCR after each fax number. Is this possible using Kix ?

i.e. find a string count on X characters then insert a CRLF

my code so far


Function fnSTRIP($sString,OPTIONAL $sDelimeter)
If VarType($sString)<>8 Exit(1) EndIf
If Not $sDelimeter $sDelimeter = Chr(13) + Chr(10) EndIf
$sString = Split($sString,$sDelimeter)
For Each $sString in $sString
$fnSTRIP=$fnSTRIP+$sString
Next
EndFunction

Function insertline($a, $b, optional $c, optional $d)
Dim $e,$f,$h,$x,$y
If $b<0 Exit -3 EndIf
If $c="" $d=1 EndIf
$f="%temp%\~kixil00.tmp" ; temporary file to use
Del $f
If $d<>1 $d=0 EndIf
$e=FreeFileHandle
$insertline=-2
If $e=0 Exit -2 EndIf
$insertline=Open($e,$a)
If $insertline<>0 Exit @Error EndIf
$h=FreeFileHandle
If $h=0
$insertline=-2
$x=Close($e)
Exit -2
EndIf
$insertline=Open($h,$f,5)
If $insertline<>0
$x=Close($e)
Exit @Error
EndIf
If $b<>0
For $x=0 To $b-1
$y=ReadLine($e)
If @Error<>0
$x=Close($e)
$x=Close($h)
Del $f
$insertline=-3
Exit -3
Else
$insertline=WriteLine($h,$y+@Crlf)
EndIf
Next
EndIf
$insertline=WriteLine($h,$c)
$y=ReadLine($e)
If @Error<>0
$x=Close($e)
$x=Close($h)
Del $f
$insertline=-3
Exit -3
EndIf
If $d=0
$x=WriteLine($h,$y+@Crlf)
EndIf
$y=ReadLine($e)
While @Error=0
$insertline=Writeline($h,$y+@Crlf)
$y=ReadLine($e)
Loop
$x=Close($e)
$x=Close($h)
Copy $f $a
Del $f
$insertline=0
Exit 0
EndFunction

$FileName = Dir("c:\temp\*.csv")
While $FileName <> "" and @ERROR = 0
$fullpath = ("c:\temp\" + $filename)

IF Open(3, $fullpath) = 0
$x = ReadLine(3)
WHILE @ERROR = 0
$y = WriteLine( 3 , ",")

;? "Line read: [" + $x + "]"

;Strip CRLF from document
fnSTRIP($x)

$x = ReadLine(3)
LOOP
Close (3)
ELSE
BEEP
? "Config file not opened, error code: [" + @ERROR + "]"
ENDIF



$FileName = Dir() ; retrieve next file
loop


Kind Regards

Damon

Top
#128371 - 2004-10-25 03:50 PM Re: Reordering text file into CSV format
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
You don't need to strip the CRLF - it's implied by ReadLine(), but is not actually present.

This sort of loop will catenate 5 lines:
Code:

$sIn=ReadLine($fdInputFile)
While Not @ERROR
$iLineCount=$iLineCount+1
$sOut=$sOut+',"'+$sIn+'"'
If Not ($iLineCount mod 5) SubStr($sOut,2)+@CRLF $sOut="" EndIf
$sIn=ReadLine($fdInputFile)
Loop


Top
#128372 - 2004-10-25 03:57 PM Re: Reordering text file into CSV format
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
Here is a fully functioning script to do it:
Code:
$fdInputFile=FreeFileHandle()
$sFilePath=".\demo.txt"

If Open($fdInputFile,$sFilePath)
"Cannot open file for input"
Exit @ERROR
EndIf

$sIn=ReadLine($fdInputFile)
While Not @ERROR
$iLineCount=$iLineCount+1
$sOut=$sOut+',"'+$sIn+'"'
If Not ($iLineCount mod 5) SubStr($sOut,2)+@CRLF $sOut="" EndIf
$sIn=ReadLine($fdInputFile)
Loop

If $sOut <> "" SubStr($sOut,2)+@CRLF $sOut="" EndIf


Top
#128373 - 2004-10-25 04:37 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


Sorry for being thick.. but I am unable to get it to open the file for input
I have change the filepath string
but the code drops out with
Cannot open file for inputScript error: failed to find/open script!

I do not have it open else where.....


thanks for your time/effort

Top
#128374 - 2004-10-25 05:17 PM Re: Reordering text file into CSV format
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
Did you copy'n'paste the sample script to a new file?

I've just tried it to ensure that there were no problems introduced as I uploaded it, and it works fine.

You may need to paste it into Word first to preserve the end of line characters, then copy and paste into notepad or whatever editor you are using for editing the script.

Top
#128375 - 2004-10-25 06:08 PM Re: Reordering text file into CSV format
Les Offline
KiX Master
*****

Registered: 2001-06-11
Posts: 12734
Loc: fortfrances.on.ca
I say he must have foobar'd the code. Obviously it starts cuz he gets "Cannot open file for input" but then later another message "Script error: failed to find/open script!" would indicate there is a bogus CALL further down.
_________________________
Give a man a fish and he will be back for more. Slap him with a fish and he will go away forever.

Top
#128376 - 2004-10-25 08:49 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


Doing it from home, works a treat... weird... and thanks...
not 100% what I am after... could you point me in the right direction to incorporate code that looks for the line with the fax number on it and at th end of that line inserts a LFCR ?

I've looked a Insertline() but it appears to work if you supply a line number... I dont know the line number as it could change.

Thanks again

Damon

Top
#128377 - 2004-10-26 10:09 AM Re: Reordering text file into CSV format
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
Quote:

not 100% what I am after




That's OK, we're 100% with you - we don't know what you are after either.

The code I've provided converts the flat file to a CSV format - all you have to do is write the output to a new file.

Here is the code updated to write the output to a new file. It does the following:
  • Reads input from file DEMO.TXT
  • Wraps each input line in quotes and converts embedded quotes in the data stream into the form that Excel likes.
  • Catenates every five lines into a single line with the fields delimited by commas
  • Writes the catenated line to DEMO.CSV, with CRLF appended.

Code:
 
$sInputFile=".\demo.txt"
$sOutputFile=".\demo.csv"

$fdInputFile=FreeFileHandle()
If Open($fdInputFile,$sInputFile)
"Cannot open file"+$sInputFile+"' for input"+@CRLF
"Reason: ["+@ERROR+"] "+@SERROR+@CRLF
Exit @ERROR
EndIf

If Exist($sOutputFile) Del $sOutputFile EndIf

$fdOutputFile=FreeFileHandle()
If Open($fdOutputFile,$sOutputFile,4+1)
"Cannot open file"+$sOutputFile+"' for output"+@CRLF
"Reason: ["+@ERROR+"] "+@SERROR+@CRLF
Exit @ERROR
EndIf

$sIn=ReadLine($fdInputFile)
While Not @ERROR
$iLineCount=$iLineCount+1
; Catenate line, fix speech marks to braindead MS csv style
$sOut=$sOut+',"'+Join(Split($sIn,'"'),'""')+'"'
If Not ($iLineCount mod 5) $=WriteLine($fdOutputFile,SubStr($sOut,2)+@CRLF) $sOut="" EndIf
$sIn=ReadLine($fdInputFile)
Loop

If $sOut <> "" $=WriteLine($fdOutputFile,SubStr($sOut,2)+@CRLF) $sOut="" EndIf

$=Close($fdInputFile)
$=Close($fdOutputFile)

Exit 0


If this doesn't fulfill your needs, take a deep breath, slow down and think about what you are trying to do. Then ask

You might like to outline the task to us - without context it is hard to determine if what we are suggesting will help or is even appropriate.

If you are having trouble working out what the code is doing, just ask.

Top
#128378 - 2004-10-26 05:29 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


Hey Richard....

I'll try the code tonight...
Here's exactly what I am trying to do..

<inhale.....hold....exhale>

I have a very long list of company names and address, similar to what you would use for a Mail merge.. but I want to import these into Outlook (2003).
The required format is CSV.
Your script neatly puts the document in CSV, but I need the code to put a CRLF at the end of the line that begins FAX, thats always the last line, but it isnt always the 7th (or 8th) line.
Some companys have 5 lines in their address and some could have only 3

Hope that makes my task a little clearer and thank you for you patience

NB: I havent tried your latest script, will do tonight

Damon


Top
#128379 - 2004-10-26 05:52 PM Re: Reordering text file into CSV format
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22333
Loc: OK
oh, so what you are actually wanting is something simple as(?):
Code:
 

$blob=open(1,"myfile.txt")
$blob=open(2,"output.txt",5)
do
$line=readline(1)
$line=$line+";"
if instr($line,"FAX")
$line=$line+@crlf
endif
if 1<len($line)
$blob=writeline(2,$line)
endif
until @error
$blob=close(1)
$blob=close(2)

_________________________
!

download KiXnet

Top
#128380 - 2004-10-26 06:11 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


Again... I'll give it a go tonight.... but it makes sense to me.. <Q jokes>
BTW - could someone explain the mod expression as I couldnt find anything about it...

Top
#128381 - 2004-10-26 07:00 PM Re: Reordering text file into CSV format
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
Take a look here

http://www.kixtart.org/ubbthreads/showflat.php?Cat=&Board=UBB1&Number=122102

Top
#128382 - 2004-10-26 09:18 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


NTDOC...

ahhh got it... cheers


cheers...

Top
#128383 - 2004-10-26 11:28 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


Ok remoing the mod expression from richard code give me one continuous CSV style array..

Joels code then can not break up the single line into several line (all of which end in the fax number)

any ideas ?

Top
#128384 - 2004-10-27 12:07 AM Re: Reordering text file into CSV format
NTDOC Administrator Offline
Administrator
*****

Registered: 2000-07-28
Posts: 11567
Loc: CA
Please post at least 20 lines of the actual file so that we can see what we're working with. You can modify the names to protect identity, but need to see what you have.

Also maybe a small example of what it should look like when done.

Make sure you use the CODE TAGS on the board when posting so that the data maintains it's formatting.

Top
#128385 - 2004-10-27 09:35 AM Re: Reordering text file into CSV format
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4945
Loc: Leatherhead, Surrey, UK
How do you tell which line is the fax line?

Does it start with the word "FAX"?

If the address may be on multiple lines then this will need special handling too.

Upload a small sample of the source file - I know you understand what you have, but we are having a bit of trouble.

Top
#128386 - 2004-10-27 12:50 PM Re: Reordering text file into CSV format
Anonymous
Unregistered


Here's an exaple of the text

Code:
 
BROOK HOTELS
The Coach House
High Street
Sevenoaks
KentTN131HY
Sarah Tunnadine

Tel: 0800 435165
Fax: 01732 741041

CHOICE HOTELS EUROPE
Premier House
112 Station Road
Edgware
Middx HA8 7BJ
Aphrodite Burton

Tel: 020 8233 2001
Fax: 020 8233 2075

CONCORDE HOTELS

Grosvenor Gardens House
35/37 Grosvenor Gardens
London SW1W0BS
Marketing Department

Tel: 0800 028 9881
Fax: 020 7630 0391

DEVERE HOTELS
& LEISURE LTD
2100 Daresbury Park
Daresbury, Warrington
Cheshire WA4 4BP
Alison Gray

Tel: 0870 240 0101
Fax: 01928 756 440

GRAND HERITAGE HOTELS
Central Booking Agency
1st Floor Warwick House
181-183 Warwick Road
London W14 8PU
Greg McCreadv

Tel: 020 7244 6699
Fax: 020 7244 7799

GRANGE HOTELS
58 Rochester Row
London SW1 1JU
Jan
Tel: 020 7233 7373
Fax: 020 7835 1888

IMPNEY HOTELS
The Chateau Impney Hotel
Droitwich Spa
Worcs WR9 0BN
Robert Hughes

Tel: 01905 774411
Fax: 01905 772371

INTER-CONTINENTAL
& FORUM HOTELS
Mayfair Inter-Continental Hotel
Stratton Street
London W1A 2AN
Stephanie Segouta
Tel: 020 7629 7777
Fax: 020 7409 7016

LAYTONA LEISURE GROUP
Mendip Lodge Hotel
Bath Road
Frame
Somerset BA11 2HP
Stephen Crawley
Tel: 01373 454211
Fax: 01373 471618



As you can see the line the begins 'FAX' is always the final line..
I wish to reorder the text so that each company and it's address/phone details are in a format that outlook can import, which is
"company name","add1","addr2","City",County","post code","Phone","fax"


Damon

Top
#128387 - 2004-10-27 01:39 PM Re: Reordering text file into CSV format
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22333
Loc: OK
now, what was wrong with my script then?
_________________________
!

download KiXnet

Top
#128388 - 2004-10-27 01:48 PM Re: Reordering text file into CSV format
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6372
Loc: Stuttgart, Germany
Think he wants the 'Tel:' and 'Fax:' stripped from the text, did ya do that ???

Edited by Jochen (2004-10-27 01:48 PM)
_________________________



Top
#128389 - 2004-10-27 01:50 PM Re: Reordering text file into CSV format
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22333
Loc: OK
he didn't ask for it!
_________________________
!

download KiXnet

Top
Page 1 of 2 12>


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Arend_, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 191 anonymous users online.
Newest Members
thequeen, ameliaryan, shalomsalami, dkdssdk, Vroon
17639 Registered Users

Generated in 0.079 seconds in which 0.031 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org