Page 1 of 1 1
Topic Options
#35891 - 2003-01-31 11:40 AM Trim Speech Marks
Darren_W Offline
Hey THIS is FUN
*****

Registered: 2001-10-10
Posts: 208
Loc: Bristol, England
Hi,

I would like to clear speech marks out of varable, how can this be done (Sure it is quite simple)

Example:

I have a csv file with a line like this

"firstname","surname"

I import it in to a String and it looks like this :

"firstname" and I would like to take away the " to give me this

firstname

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
#35892 - 2003-01-31 11:46 AM Re: Trim Speech Marks
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
hmm ... sure thing : get rid of the Quotations in the csv file !

Possible ?
_________________________



Top
#35893 - 2003-02-01 12:18 AM Re: Trim Speech Marks
Richard H. Administrator Offline
Administrator
*****

Registered: 2000-01-24
Posts: 4946
Loc: Leatherhead, Surrey, UK
Sure, the simple solution is just to use InStr() and SubStr() if you can be sure of what is in the fields.

Be aware if you are trying to parse MicroSoft format CSV files with unknown data that it can get pretty messy.

You will need to cater for strings which have commas in them, and worse, strings which have speech marks in them.

Try creating a spread sheet in Excel which has cells containing the (,) character and some containing the (") character. Now save it as a CSV file.

It's not impossible to decode it, but you will go cross-eyed looking at all the speech marks.

Top
#35894 - 2003-01-31 01:34 PM Re: Trim Speech Marks
Jochen Administrator Offline
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 [Big Grin]
(Richard is damn RIGHT !!!)

{edit#2} Oh yeah , please feel free to play Golf with this snippet above [Big Grin] {/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 Offline
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
#35896 - 2003-01-31 02:22 PM Re: Trim Speech Marks
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
errrggg .... *Jochen flees with fear !
_________________________



Top
#35897 - 2003-01-31 02:24 PM Re: Trim Speech Marks
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Hey ,

who needs to work with csv anyway ?

Why don't you have that as Excel and simply read the contents via the beautiful and easy to understand 'Office COM Interface' [Razz]
_________________________



Top
#35898 - 2003-01-31 03:08 PM Re: Trim Speech Marks
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
Or use an ODBC connection to the Excel file?
_________________________
There are two types of vessels, submarines and targets.

Top
#35899 - 2003-01-31 03:09 PM Re: Trim Speech Marks
Richard H. Administrator Offline
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 Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
errg ... not speaking of the UDF Richard (which is of course great as always)

but the colors [Eek!]

Nice thing : On my LCD I didn't see the backColor(Light Yellow) but on my CRT [Razz]
{edit: Now also visible on LCD ... damn contrats eh ? [Wink] {/edit}

Think that table stuff is an Option to add for PostPrep 1.3 ... JOOOOOEEEL !!!

[ 31. January 2003, 16:07: Message edited by: jpols ]
_________________________



Top
#35901 - 2003-02-03 11:36 AM Re: Trim Speech Marks
Darren_W Offline
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
#35902 - 2003-02-03 11:46 AM Re: Trim Speech Marks
Lonkero Administrator Offline
KiX Master Guru
*****

Registered: 2001-06-05
Posts: 22346
Loc: OK
reading your script shows that the source file has already:
"first last,username,group"
form...
you probably would call it then as:
join(fnCSV2Array ($sourceline),",")

basically what I say is that I have no idea where you would use it as you have already code that does it all the way around...

[ 03. February 2003, 11:47: Message edited by: Lonkero ]
_________________________
!

download KiXnet

Top
#35903 - 2003-02-04 12:02 AM Re: Trim Speech Marks
Darren_W Offline
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 Offline
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
#35905 - 2003-02-04 12:33 AM Re: Trim Speech Marks
Darren_W Offline
Hey THIS is FUN
*****

Registered: 2001-10-10
Posts: 208
Loc: Bristol, England
Thanks.. All fine now

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
Page 1 of 1 1


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

Who's Online
1 registered (Allen) and 382 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.086 seconds in which 0.027 seconds were spent on a total of 12 queries. Zlib compression enabled.

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