String find-replace issue [Solved]

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
antrax13
Posts: 39
Joined: Mon Jun 01, 2015 11:38 am

String find-replace issue [Solved]

Post by antrax13 » Thu Jul 02, 2015 10:16 am

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 "
Last edited by antrax13 on Thu Jul 02, 2015 11:38 am, edited 1 time in total.

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: String find-replace issue

Post by Thierry » Thu Jul 02, 2015 10:28 am

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
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

antrax13
Posts: 39
Joined: Mon Jun 01, 2015 11:38 am

Re: String find-replace issue

Post by antrax13 » Thu Jul 02, 2015 10:41 am

Thierry wrote:
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
Posts: 39
Joined: Mon Jun 01, 2015 11:38 am

Re: String find-replace issue

Post by antrax13 » Thu Jul 02, 2015 11:22 am

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

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
Posts: 39
Joined: Mon Jun 01, 2015 11:38 am

Re: String find-replace issue

Post by antrax13 » Thu Jul 02, 2015 11:38 am

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 :lol:

Klaus
Posts: 14199
Joined: Sat Apr 08, 2006 8:41 am
Contact:

Re: String find-replace issue [Solved]

Post by Klaus » Thu Jul 02, 2015 1:05 pm

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
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: String find-replace issue [Solved]

Post by phaworth » Thu Jul 02, 2015 8:50 pm

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

Post Reply