#128390 - 2004-10-27 01:57 PM
Re: Reordering text file into CSV format
|
Lonkero
KiX Master Guru
Registered: 2001-06-05
Posts: 22346
Loc: OK
|
so, it seems that he would want something like:
Code:
$blob=open(1,"myfile.txt")
$blob=open(2,"output.txt",5)
$blob=writeline(2,'"')
do
$line=readline(1)
select
case instr($line,"FAX:")
$line='","'+split($line,"FAX:")[1]+@crlf+'"'
case instr($line,"Tel:")
$line='","'+split($line,"Tel:")[1]
case 1
$line=' '+$line
endselect
if 1<len($line)
$blob=writeline(2,$line)
endif
until @error
$blob=close(1)
$blob=close(2)
this code has the problem that it produces the last line as empty one (with only single quote on it).
Edited by Jooel (2004-10-27 04:34 PM)
_________________________
!download KiXnet
|
|
Top
|
|
|
|
|
#128391 - 2004-10-27 02:05 PM
Re: Reordering text file into CSV format
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Ok, a trivial change will fix that:
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
; Catenate line, fix speech marks to braindead MS csv style
If $sIn <> "" $sOut=$sOut+',"'+Join(Split($sIn,'"'),'""')+'"' EndIf
If InStr($sIN,"FAX: ")==1 $=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
Now you have CSV records which break whereever there is a FAX line.
However, you have much bigger problems.
The file is not a fixed format, so it is not possible to determine how many lines there are in the address, or where to find the post code, city or other information.
If you can fix the number of lines for each record, or prefix the data with a sentinel string then it can be done otherwise you are stuffed.
It is further complicated because the KiXtart ReadLine() API does not differentiate between carriage returns and CRLF - both are considered to be end of line strings and both are silently dropped. This means that if your "address" is actually a single line with embedded carriage returns it will be parsed as multiple single lines.
|
|
Top
|
|
|
|
|
#128392 - 2004-10-27 03:13 PM
Re: Reordering text file into CSV format
|
Anonymous
Anonymous
Unregistered
|
Richard,
I think you're right... could you explain what you meant by "or prefix the data with a sentinel string"
thanks
|
|
Top
|
|
|
|
|
#128393 - 2004-10-27 03:28 PM
Re: Reordering text file into CSV format
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Quote:
could you explain what you meant by "or prefix the data with a sentinel string"
Keyword.
Your Fax and Telephone details can be determined because the lines start with the FAX: and TEL: keywords respectively. I've used the FAX: keyword in the example script to trigger the write of the output line to the CSV file.
The persons name can be determined (assuming it is always present) because it is the last entry before the telephone number.
The (first line of) the address can be determined because it is the first line after the FAX: line.
However, there is no way of determining what the rest of the stuff is.
There are some heuristic tricks and patterm matching which you can use to guess what a line may be, but that's a substantial task and it won't always get it right.
Can the program you are exporting the data from present it in a different way?
|
|
Top
|
|
|
|
|
#128394 - 2004-10-27 03:46 PM
Re: Reordering text file into CSV format
|
Anonymous
Anonymous
Unregistered
|
Right, ok..
between yours and joels scripts we almost have all we need.
If I run your script with the mod expression taken out, I get one long CSV formatted line.. If joels script can be modified to search for fax then count on until you reach the next comma and replace it with a CRLF ?
I guess with my exteremely little knowledge, it sounds 'easy' and I know it's not, but, is the logic sound ?
|
|
Top
|
|
|
|
|
#128395 - 2004-10-27 04:00 PM
Re: Reordering text file into CSV format
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Quote:
is the logic sound
No.
It really is far more complicated than that.
If you are willing to forego the country / postcode stuff, this will get you pretty much there:
Code:
$sInputFile=".\demo.txt"
$sOutputFile=".\demo.csv"
$sDelim=Chr(10)
$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
Select
Case $sIn=""
; Blank line - ignore
Case InStr($sIn,"FAX:")=1
$sFax=Trim(SubStr($sIn,5))
; Write out record.
@CRLF+"Person ="$sPerson+@CRLF
$=WriteLine($fdOutputFile,SubStr(udfFixField($sPerson),2))
$asAddress=Split(SubStr($sAddress+$sDelim+$sDelim+$sDelim+$sDelim,2),$sDelim)
For $iIndex = 0 to 4
"Address "+($iIndex+1)+" ="$asAddress[$iIndex]+@CRLF
$=WriteLine($fdOutputFile,udfFixField($asAddress[$iIndex]))
Next
"Telephone ="$sTel+@CRLF
$=WriteLine($fdOutputFile,udfFixField($sTel))
"Fax ="$sFax+@CRLF
$=WriteLine($fdOutputFile,udfFixField($sFax))
$=WriteLine($fdOutputFile,@CRLF)
$sLast=""
$sAddress=""
$sTel=""
$sFax=""
Case InStr($sIn,"TEL:")=1
$sPerson=$sLast
$sTel=Trim(SubStr($sIn,5))
Case "Default"
If $sLast<>"" $sAddress=$sAddress+$sDelim+$sLast EndIf
$sLast=$sIn
EndSelect
; Catenate line, fix speech marks to braindead MS csv style
;If $sIn <> "" $sOut=$sOut+',"'+Join(Split($sIn,'"'),'""')+'"' EndIf
;If InStr($sIN,"FAX: ")==1 $=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
Function udfFixField($s)
$udfFixField=',"'+Join(Split($s,'"'),'""')+'"'
EndFunction
This creates the CSV and dumps some debug text to the screen.
The debug text for your sample looks like this:
Quote:
Person =Sarah Tunnadine
Address 1 =BROOK HOTELS
Address 2 =The Coach House
Address 3 =High Street
Address 4 =Sevenoaks
Address 5 =KentTN131HY
Telephone =0800 435165
Fax =01732 741041
Person =Aphrodite Burton
Address 1 =CHOICE HOTELS EUROPE
Address 2 =Premier House
Address 3 =112 Station Road
Address 4 =Edgware
Address 5 =Middx HA8 7BJ
Telephone =020 8233 2001
Fax =020 8233 2075
Person =Marketing Department
Address 1 =CONCORDE HOTELS
Address 2 =Grosvenor Gardens House
Address 3 =35/37 Grosvenor Gardens
Address 4 =London SW1W0BS
Address 5 =
Telephone =0800 028 9881
Fax =020 7630 0391
Person =Alison Gray
Address 1 =DEVERE HOTELS
Address 2 =& LEISURE LTD
Address 3 =2100 Daresbury Park
Address 4 =Daresbury, Warrington
Address 5 =Cheshire WA4 4BP
Telephone =0870 240 0101
Fax =01928 756 440
Person =Greg McCreadv
Address 1 =GRAND HERITAGE HOTELS
Address 2 =Central Booking Agency
Address 3 =1st Floor Warwick House
Address 4 =181-183 Warwick Road
Address 5 =London W14 8PU
Telephone =020 7244 6699
Fax =020 7244 7799
Person =Jan
Address 1 =GRANGE HOTELS
Address 2 =58 Rochester Row
Address 3 =London SW1 1JU
Address 4 =
Address 5 =
Telephone =020 7233 7373
Fax =020 7835 1888
Person =Robert Hughes
Address 1 =IMPNEY HOTELS
Address 2 =The Chateau Impney Hotel
Address 3 =Droitwich Spa
Address 4 =Worcs WR9 0BN
Address 5 =
Telephone =01905 774411
Fax =01905 772371
Person =Stephanie Segouta
Address 1 =INTER-CONTINENTAL
Address 2 =& FORUM HOTELS
Address 3 =Mayfair Inter-Continental Hotel
Address 4 =Stratton Street
Address 5 =London W1A 2AN
Telephone =020 7629 7777
Fax =020 7409 7016
Person =Stephen Crawley
Address 1 =LAYTONA LEISURE GROUP
Address 2 =Mendip Lodge Hotel
Address 3 =Bath Road
Address 4 =Frame
Address 5 =Somerset BA11 2HP
Telephone =01373 454211
Fax =01373 471618
The CSV file looks like this:
Quote:
"Sarah Tunnadine","BROOK HOTELS","The Coach House","High Street","Sevenoaks","KentTN131HY","0800 435165","01732 741041"
"Aphrodite Burton","CHOICE HOTELS EUROPE","Premier House","112 Station Road","Edgware","Middx HA8 7BJ","020 8233 2001","020 8233 2075"
"Marketing Department","CONCORDE HOTELS","Grosvenor Gardens House","35/37 Grosvenor Gardens","London SW1W0BS","","0800 028 9881","020 7630 0391"
"Alison Gray","DEVERE HOTELS","& LEISURE LTD","2100 Daresbury Park","Daresbury, Warrington","Cheshire WA4 4BP","0870 240 0101","01928 756 440"
"Greg McCreadv","GRAND HERITAGE HOTELS","Central Booking Agency","1st Floor Warwick House","181-183 Warwick Road","London W14 8PU","020 7244 6699","020 7244 7799"
"Jan","GRANGE HOTELS","58 Rochester Row","London SW1 1JU","","","020 7233 7373","020 7835 1888"
"Robert Hughes","IMPNEY HOTELS","The Chateau Impney Hotel","Droitwich Spa","Worcs WR9 0BN","","01905 774411","01905 772371"
"Stephanie Segouta","INTER-CONTINENTAL","& FORUM HOTELS","Mayfair Inter-Continental Hotel","Stratton Street","London W1A 2AN","020 7629 7777","020 7409 7016"
"Stephen Crawley","LAYTONA LEISURE GROUP","Mendip Lodge Hotel","Bath Road","Frame","Somerset BA11 2HP","01373 454211","01373 471618"
|
|
Top
|
|
|
|
|
#128397 - 2004-10-27 04:44 PM
Re: Reordering text file into CSV format
|
Lonkero
KiX Master Guru
Registered: 2001-06-05
Posts: 22346
Loc: OK
|
changed it...
anyways, yet another approach...
Code:
$blob=open(1,"myfile.txt")
$blob=open(2,"output.txt",5)
$blob=writeline(2,'"')
do
$line=readline(1)
if instr($line,"FAX:")
$line=$line+chr(10)
endif
$file=$file+" "+$line
until @error
$blob=close(1)
$file=split(substr($file,2),chr(10))
for each $record in $file
$record='"'+join(split(join(split($record,"FAX:"),'","'),"Tel:"),'","')+'"'
$blob=writeline(2,$line)
next
$blob=close(2)
hmm...
realized my previous codes had other [censored] there too...
gonna test this one.
_________________________
!download KiXnet
|
|
Top
|
|
|
|
|
#128398 - 2004-10-27 05:15 PM
Re: Reordering text file into CSV format
|
Lonkero
KiX Master Guru
Registered: 2001-06-05
Posts: 22346
Loc: OK
|
lol.
this was screwed up also!
ok, first tested one, I think I owed you both a test round.
and apology.
Code:
$blob=open(1,@scriptdir+"\txt_test.txt")
$blob=open(2,@scriptdir+"\output.txt",5)
do
$line=readline(1)
if instr($line,"FAX:")
$line=$line+chr(10)
endif
$file=$file+" "+$line
until @error
$blob=close(1)
$file=split(join(split($file," ")),chr(10))
for $r=0 to ubound($file)-1
$file[$r]='"'+join(split(join(split($file[$r],"FAX:"),'","'),"Tel:"),'","')+'"'
$file[$r]=join(split(join(split($file[$r],' "'),'"'),'" '),'"')+@crlf
$blob=writeline(2,$file[$r])
next
$blob=close(2)
produces:
Quote:
"BROOK HOTELS The Coach House High Street Sevenoaks KentTN131HY Sarah Tunnadine","0800 435165","01732 741041"
"CHOICE HOTELS EUROPE Premier House 112 Station Road Edgware Middx HA8 7BJ Aphrodite Burton","020 8233 2001","020 8233 2075"
"CONCORDE HOTELS Grosvenor Gardens House 35/37 Grosvenor Gardens London SW1W0BS Marketing Department","0800 028 9881","020 7630 0391"
"DEVERE HOTELS & LEISURE LTD 2100 Daresbury Park Daresbury, Warrington Cheshire WA4 4BP Alison Gray","0870 240 0101","01928 756 440"
"GRAND HERITAGE HOTELS Central Booking Agency 1st Floor Warwick House 181-183 Warwick Road London W14 8PU Greg McCreadv","020 7244 6699","020 7244 7799"
"GRANGE HOTELS 58 Rochester Row London SW1 1JU Jan","020 7233 7373","020 7835 1888"
"IMPNEY HOTELS The Chateau Impney Hotel Droitwich Spa Worcs WR9 0BN Robert Hughes","01905 774411","01905 772371"
"INTER-CONTINENTAL & FORUM HOTELS Mayfair Inter-Continental Hotel Stratton Street London W1A 2AN Stephanie Segouta","020 7629 7777","020 7409 7016"
"LAYTONA LEISURE GROUP Mendip Lodge Hotel Bath Road Frame Somerset BA11 2HP Stephen Crawley","01373 454211","01373 471618"
_________________________
!download KiXnet
|
|
Top
|
|
|
|
|
#128399 - 2004-10-28 11:47 AM
Re: Reordering text file into CSV format
|
Anonymous
Anonymous
Unregistered
|
Richard,
When I try your code the output is each letter has quotes, rather than each word..
e.g.
Code:
C:\kix>type demo.csv
" ■B","R","O","O","K"," ","H","O","T","E","L","S","T","h","e"," ","C","o","a","c
","h"," ","H","o","u","s","e","H","i","g","h"," ","S","t","r","e","e","t","S","e
","v","e","n","o","a","k","s","K","e","n","t","T","N","1","3","1","H","Y","C","o
I havent changed your script, other than removing the two ;'ed out lines
Code:
; Catenate line, fix speech marks to braindead MS csv style
If $sIn <> "" $sOut=$sOut+',"'+Join(Split($sIn,'"'),'""')+'"' EndIf
If InStr($sIN,"FAX: ")==1 $=WriteLine($fdOutputFile,SubStr($sOut,2)+@CRLF)
If I leave them ;'d then the output file is 0 bytes
|
|
Top
|
|
|
|
|
#128400 - 2004-10-28 12:33 PM
Re: Reordering text file into CSV format
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Are you manually typing this in, or copy'n'pasting it?
It sounds like you have a problem with the udfFixField() function.
Code:
Function udfFixField($s)
$udfFixField=',"'+Join(Split($s,'"'),'""')+'"'
EndFunction
It's a little hard to see on the BB if you are typing in manually, so I'll recolour the quotes to make it obvious:
$udfFixField=',"'+Join(Split($s,'"'),'""')+'"'
I've tested the script locally with your sample input file, so I know it works.
If you are copying via Word to preserve the formatting, make sure it is not doing anything stupid like "smart-quoting" the pasted text.
Which version of KiXtart are you using?
|
|
Top
|
|
|
|
|
#128401 - 2004-10-28 01:12 PM
Re: Reordering text file into CSV format
|
Anonymous
Anonymous
Unregistered
|
I'm using Kix 422
I am copying and pasting in wordpad then into notepad.
remember I had the strange issue when we started that wouldnt open the file for reading... well I got round that by copying kix exe's into the same test folder...
I did get the same results no a laptop at home too...
|
|
Top
|
|
|
|
|
#128402 - 2004-10-28 01:25 PM
Re: Reordering text file into CSV format
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Well, something has been lost in translation 
Email me your script directly as an attachment and I will compare to the original and see where the problem lies.
If your address file does not contain sensitive information then attach that too, and I will run it through my script and ensure that it works in all cases.
|
|
Top
|
|
|
|
|
#128403 - 2004-10-28 02:58 PM
Re: Reordering text file into CSV format
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Ahah! I've received the files and it all suddenly becomes clear.
The exported file is in UNICODE, and KiXtart doesn't handle this very well.
Once I converted it to ASCII the script worked fine.
There are a number of ways to convert to ASCII, but my preferred method is:
Code:
"%COMSPEC%" /A /C "type unicode.txt > ascii.txt"
This will convert a UNICODE file "unicode.txt" to an ASCII file "ascii.txt".
IIRC you can also convert it by opening in Notepad and saving again, but the command line method is guaranteed to work. You can convert from ASCII to UNICODE by replacing the "/A" with "/U".
Convert your file to ASCII and try again - you should have much better luck.
|
|
Top
|
|
|
|
|
#128404 - 2004-10-28 05:43 PM
Re: Reordering text file into CSV format
|
Anonymous
Anonymous
Unregistered
|
SORTED!! 
thanks Richard
|
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 369 anonymous users online.
|
|
|
