#35894 - 2003-01-31 01:34 PM
Re: Trim Speech Marks
|
Jochen
KiX Supporter
Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
|
heres the hard but idiotic way of doing it (assuming that you have seperated it by commas)
script:
break on
dim $out[0], $c $in = @scriptdir+"\test.csv" $out[0] = "" $c=0 $=open(1,$in,2) if not @error $l=readline(1) while not @error redim preserve $out[$c] $l=substr($l,2,len($l)-2) $out[$c] = split($l,'","') $c=$c+1 $l=readline(1) loop $=close(1) endif
;test array for $i=0 to ubound($out) for $j=0 to ubound($out[$i]) at($i,$j*10) $out[$i][$j] next next
get $
csv file : code:
"John","Doe" "Jane","Doe"
it was really hard to create this csv by other editors than Notepad (Richard is damn RIGHT !!!)
{edit#2} Oh yeah , please feel free to play Golf with this snippet above {/edit} [ 31. January 2003, 13:41: Message edited by: jpols ]
_________________________
|
Top
|
|
|
|
#35895 - 2003-01-31 02:20 PM
Re: Trim Speech Marks
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
How does it get on with this: code:
The following field contains a comma,"Doe, John" The following field contains a quote,"He said ""Don't quote me on that""" The following field contains three quote characters in a row,"""""""" The following field contains quote-comma-quote-quote-comma-quote,""","""","""
Horrible, huh?
This is an example generated from Excel. Each row contains only two fields, the second fiels which when unpacked should read: quote: Doe, John "Don't quote me on that" """ ","","
[ 31. January 2003, 14:21: Message edited by: Richard H. ]
|
Top
|
|
|
|
#35899 - 2003-01-31 03:09 PM
Re: Trim Speech Marks
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Here is a UDF which should work in all situations. It returns an array containing each field from the line:
Function fnCSV2Array($s) Dim $iIndex,$iLength,$cChar,$iMode,$iArraySize Redim Preserve $fnCSV2Array[0] $iArraySize=0 $iMode=0 ; 1 = in quoted field
$iLength=Len($s) For $iIndex = 1 To $iLength $cChar=SubStr($s,$iIndex,1) If $iMode If $cChar='"' If SubStr($s,$iIndex+1,1)='"' $fnCSV2Array[$iArraySize]=""+$fnCSV2Array[$iArraySize]+$cChar $iIndex=$iIndex+1 Else $iMode=0 EndIf Else $fnCSV2Array[$iArraySize]=""+$fnCSV2Array[$iArraySize]+$cChar EndIf Else Select Case $cChar=',' $iArraySize=$iArraySize+1 Redim Preserve $fnCSV2Array[$iArraySize] Case $cChar='"' $iMode=1 Case "true" $fnCSV2Array[$iArraySize]=""+$fnCSV2Array[$iArraySize]+$cChar EndSelect EndIf Next EndFunction
|
Here is an example of how to use it:
For Each $sElement In fnCSV2Array('The following field contains a comma,"Doe, John"') "<" $sElement "> " Next ?
|
[ 31. January 2003, 15:23: Message edited by: Richard H. ]
|
Top
|
|
|
|
#35900 - 2003-01-31 03:15 PM
Re: Trim Speech Marks
|
Jochen
KiX Supporter
Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
|
|
Top
|
|
|
|
#35901 - 2003-02-03 11:36 AM
Re: Trim Speech Marks
|
Darren_W
Hey THIS is FUN
Registered: 2001-10-10
Posts: 208
Loc: Bristol, England
|
Hi,
Not so sure how I should use this UDF?
Using this text in a csv : "whittington","darren"
Hope to get somthing like this: Darren Whittington,dwhittington,group
Darren
code:
Break on At(11,12) "Please enter your source file name (eg. a:\text.csv)?" gets $source At(14,12) "Please enter your Group Name?" gets $group If open (1, $source) = 0 $sourceline = Readline(1) WHILE @ERROR = 0 ;??? fnCSV2Array ($sourceline)
;Split File
$sourcearray = split ($sourceline,",") Put in array For Each $itemofname in $sourcearray Next ;Arrange output $fullname= $sourcearray[1]+ " " + $sourcearray[0] $Username= LEFT($sourcearray[1], 1) + $sourcearray[0] $lower = Lcase($username) $username = $lower IF Open( 3 , "output.txt" , 5 ) = 0 $x = WriteLine( 3 , $fullname + "," + $username + "," + $group + @CRLF) close (3) endif $sourceline = Readline(1) LOOP Close (1) endif
Function fnCSV2Array($s) Dim $iIndex,$iLength,$cChar,$iMode,$iArraySize Redim Preserve $fnCSV2Array[0] $iArraySize=0 $iMode=0 ; 1 = in quoted field
$iLength=Len($s) For $iIndex = 1 To $iLength $cChar=SubStr($s,$iIndex,1) If $iMode If $cChar='"' If SubStr($s,$iIndex+1,1)='"' $fnCSV2Array[$iArraySize]=""+$fnCSV2Array[$iArraySize]+$cChar $iIndex=$iIndex+1 Else $iMode=0 EndIf Else $fnCSV2Array[$iArraySize]=""+$fnCSV2Array[$iArraySize]+$cChar EndIf Else Select Case $cChar=',' $iArraySize=$iArraySize+1 Redim Preserve $fnCSV2Array[$iArraySize] Case $cChar='"' $iMode=1 Case "true" $fnCSV2Array[$iArraySize]=""+$fnCSV2Array[$iArraySize]+$cChar EndSelect EndIf Next EndFunction
_________________________
I want to share something with you - the three sentences that will get you through life. Number 1, 'cover for me.' Number 2, 'oh, good idea, boss.' Number 3, 'it was like that when I got here'.
|
Top
|
|
|
|
#35903 - 2003-02-04 12:02 AM
Re: Trim Speech Marks
|
Darren_W
Hey THIS is FUN
Registered: 2001-10-10
Posts: 208
Loc: Bristol, England
|
Hi Lonkero,
My source file is "surname","firstname"?
Running as sugested with join(fnCSV2Array ($sourceline),",") just before the ;Split File comment I get this as output:
"firstname" "surname",""surname",GROUP
Darren
_________________________
I want to share something with you - the three sentences that will get you through life. Number 1, 'cover for me.' Number 2, 'oh, good idea, boss.' Number 3, 'it was like that when I got here'.
|
Top
|
|
|
|
#35904 - 2003-02-04 12:17 AM
Re: Trim Speech Marks
|
Richard H.
Administrator
Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
|
Darren,
The UDF splits the line automatically into an array, you don't need to do it again.
If you have a csv file that contains
code:
Apple,Pear,"A bunch of bananas"
then
code:
$sourcearray=fnCSV2Array($sourceline)
Will give you an array that you can manipulate directly containing:
$sourcearray[0] | = | Apple |
$sourcearray[1] | = | Pear |
$sourcearray[2] | = | A bunch of bananas |
[ 03. February 2003, 12:27: Message edited by: Richard H. ]
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
1 registered
(Allen)
and 382 anonymous users online.
|
|
|