String find-replace issue [Solved]

[antrax13]

Hi,
I am trying to populate DB from CSV file.
My issue is that sometimes I might have quote and comma in spreadsheet and this will break my import.
So imagine you have in excel spreadsheet these values that I need to import into DB:

Code: Select all

Test 
Te,st
Te"st 
Te,"st 
Test"
"Test
"Test,

when you save this file as CSV you will get these values that I do not want to import:

Code: Select all

Test 
"Te,st"
"Te""st"
"Te,""st"
"Test"""
"""Test"
"""Test,"

So what I need is:
if string contains only one " remove them
if string contains two or three " one after the other replace them with one "

[Thierry]

if string contains only one " remove them
if string contains two "" replace them with one "

what if 3 as in the lastest lines of your sample data?

Thierry

[antrax13]

if string contains only one " remove them
if string contains two "" replace them with one "

what if 3 as in the lastest lines of your sample data?

Thierry

Question has been updated before you have answered

[antrax13]

I tried this but then I realized it wont work because It will remove all quotes

Code: Select all

 repeat for each line x in fld "testfld"
      if x contains quote then
         if x contains quote&quote&quote then
            replace quote&quote&quote with quote in x
         end if
         if x contains quote&quote then
            replace quote&quote with quote in x
         end if
-- etc
         answer "contains"
      else
         answer "not contains"
      end if
   end repeat

So probably the best option for me is this approach
step 1: check if string contains quotes if yes then
step 2: if the first character of string is " then remove it
step 3: if the last character of string is " then remove it
step 4: if string contains "" then replace them with "

that should work or is there a better way?
The reason why I am asking is that imagine that import file contains about 4k lines and 50 columns per row and to do check on every single field is 200k checks so every better option with less conditions will be better for me

That

[antrax13]

Code: Select all

   repeat for each line x in fld "testfld"
      if x contains quote then
         if char 1 of x is quote and last char of x is quote  then
            delete char 1 in x
            delete last char in x
         end if
         replace quote&quote with quote in x
      end if
   end repeat

Final solution and big thanks to myself for sorting this out

[Klaus]

HI antrax13,

"repeat for each XXX" is insanely fast, but also READ ONLY!
That mean in your script you cannot modify X!

Do something like this:

Code: Select all

...
  ## Store fields content in variable:
   put fld "testfld" into tData
   repeat for each line X tData
      
      ## Use another var to modify!
      put X into Y
      if Y contains quote then
         if char 1 of Y = quote and last char of Y = quote  then
            put char 2 to -2 of Y into Y
         end if
         replace quote & quote with quote in Y
      end if
    
     ## Collect new modified data in new variable
    put Y & CR after tNewData
   end repeat

  ## get rid of trailing CR
  delete char -1 of tNewData

  ## Now write modified data back to field
  put tNewData into fdl "testfld"
...

Best

Klaus

[phaworth]

Save yourself a lot of time and trouble and get Alex Tweedly's csv omport hamdler. It's really fast and deals with a lot of corner cases.
Pete