I said I would post this...and well, this is really ugly - but then
one-off stuff...you know..
We get a line from the file and we want to clean up that embedded comma,
while were at it handle double quotes and single quotes
so some constants firsts
const dblQt = """"
const sglQt = "'"
const Comma = ","
const strDblQt = "[dblquote]"
const strSglQt = "[sglquote]"
const strComma = "[comma]"
the function to sanitize our line
function prepInput( aLine as string, aSepChr as string ) as string
dim tmpStr as string
dim tmpStr2 as string
dim tmpStr3 as string
dim tmpStr4 as string
dim start as integer
start = 1
do
tmpStr = FindPartString(aLine, aSepChr & dblQt, dblQt & aSepChr, start)
tmpStr2 = ReplaceString(tmpStr, strComma, Comma)
aLine = ReplaceString(aLine, tmpStr2, tmpStr)
tmpStr3 = ReplaceString(tmpStr2, strDblQt, dblQt)
aLine = ReplaceString(aLine, tmpStr3, tmpStr2)
tmpStr4 = ReplaceString(tmpStr3, strsglQt, sglQt)
aLine = ReplaceString(aLine, tmpStr4, tmpStr3)
loop until tmpStr = ""
prepInput = aLine
end function
and a function to put the actual characters back before we send it to
the new database field
function prepStringField( aField as string ) as string
dim tmpStr
tmpStr = ReplaceString( aField, Comma, strComma )
tmpStr = ReplaceString( tmpStr, dblQt & dblQt, strDblQt )
tmpstr = ReplaceString( tmpStr, dblQt & sqlQt & dblQt, strSglQt )
prepStringField = tmpStr
end function
Now that really is ugly isn't it. Besides we immediately see that for
double quotes and single quotes we could of just dropped the
intermediate step and fixed them properly in the function prepInput -
but I didn't...left it very very ugly..and then we just call it with
function burstLine( aLine as string, aSepChr as string ) as variant
dim tmpArray as variant
dim cntr as integer
tmpArray = ArrayOutOfString( prepInput( aLine, aSepChr ), aSepChr )
for cntr = lBound( tmpArray ) to UBound( tmpArray )
tmpArray( cntr ) = prepStringField( tmpArray( cntr ) )
next
burstLine = tmpArray
end function
And we are good to go, to push all this into the table. I handle the
boolean and date conversions later, when know which fields have what types.
So - now..I think that a more generic approach is called for and for
that ...a trip to the wiki.
Till then
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
