change special chars in memo for use with Json

[Marc Venken]

I need to convert data inside memo fields to upload for a webshop. Therefore I put the data inside a Json string. This works but more than once it seems that there are
special chars inside the memo field that will confuse the Json string so that the upload is not possible.
I try to convert with StrTran, but is this the correct way ?
Mayby there are functions to do it for all special chars ?

Code: Select all  Expand view  RUN

     cData = STRTRAN(artikel->foldertxt, chr(13)+chr(10), "<BR>") //  memofield
     cData = STRTRAN(cData, 'Â', '')
     cData = STRTRAN(cData, 'ú', '')
     cData = STRTRAN(cData, 'ë', '&#235')
     cData = STRTRAN(cData, ';', ' ')
     cData = STRTRAN(cData, '"', '')
     cData = STRTRAN(cData, 'é', '&#233;')
     cData = STRTRAN(cData, 'è', '&#232;')
     cData = STRTRAN(cData, '®', '')

//  process cData inside Json = ok
 

[Otto]

Hello Marc,
my daughter sent me these lines of code a few days ago, saying that they would solve the code page problem.
Unfortunately, I haven’t tried it yet. Could you please test it?
Best regards,
Otto

Code: Select all  Expand view  RUN

#include "hbextcdp.ch"  

cVData := IIF( ! isUtf8( offert1->bezeichnun ), hb_StrToUtf8( ALLTRIM( offert1->bezeichnun ) ), ALLTRIM( offert1->bezeichnun ) )  
 

[Marc Venken]

Otto,

This code will change data to UTF8-coded data, but will not change a chr(10) to something else as far as I read.

[Ruth]

Dear Marc,

I am very interested in this topic, because those things happen to me a lot
how do you build the json string?
I use hb_JsonEncode() and usually special characters like " and \ are treated very well inside of it.

kind regards,
Ruth

[Marc Venken]

Ruth,

I build my Json the old way... There will be functions I suppose but this works for me.

aVelden = array with selected fields to update the online shop.

Code: Select all  Expand view  RUN

   cBuffer := '{'
   cBuffer = cBuffer + '"model":"'+cCode+'",'  //  Geen ""

   IF AScan( aVelden, "NAAM") > 0  //  Ben ik actief gezet als status
      cData = change_memotxt(alltrim(webshop->naamshop))
      cBuffer = cBuffer + '"name":"'+cData+'",'  //  Geen ""
   endif
   IF AScan( aVelden, "FABRIKANT") > 0
     cBuffer = cBuffer + '"manufacturer":{"id":"'+alltrim(webshop->fabrikan)+'"},'
   endif
   IF AScan( aVelden, "MEMOTXT") > 0
     cLayover = alltrim(webshop->memotxt)  //  At this point I try to remove chars that are not OK
     cLayover = STRTRAN(cLayover, chr(13)+chr(10) , "<br>")  // \n
     cLayover = STRTRAN(cLayover, chr(10) , "<br>")  // \n
     cLayover = STRTRAN(cLayover, chr(13) , "<br>")  // \r
     cLayover = STRTRAN(cLayover, chr(12) , "\f") // Formfeed
     if empty(cLayover)
       cLayover = ""
     endif
     if !IsJson(cLayover)
       aadd(aFouten,alltrim(webshop->id)+" : Jason Code 'MEMO' is niet conform")
     else
       cBuffer = cBuffer + '"description":"'+cLayover+'",'  //  Geen ""
     endif
   endif

   // .....  For every needed field a extra IF

   cBuffer = substr(cBuffer,1,len(cBuffer)-1)
   cBuffer = cBuffer + '}'

   //JsonView( cBuffer )

   cUrl="https://YourShop/api/v2/products/"+alltrim(cOnlineCode)+"?token=...."

   oHttp := FWGetOleObject( "WINHTTP.WinHttpRequest.5.1" )

      WITH OBJECT oHttp
         :SetTimeouts(0, 60000, 30000, 120000)
         :Open( "PATCH", cUrl, .f. )      
         :SetRequestHeader( "Accept",        "application/json" )
         :SetRequestHeader( "Content-Type",  "application/json" )
         :Send( cBuffer )
         :WaitForResponse()
         if :status <> 200
            aadd(aFouten,"Fout !! : "+webshop->ID+"  "+:Statustext)
         else
            aadd(aFouten,"Artikel : "+webshop->ID+" Updated : "+uResponse)
         endif
      END
      xbrowser(aFouten) title "Fouten"


 

[Marc Venken]

I see the function.... Maybe I should better look at it and start using it.

hb_jsonEncode( { "key1" => "value1", "key2" => "value2", "key3" => "value3" } )

In my memofield there are chr(10) and sometimes other. Will that function change these automatic ?

The way I do it is also handy in my case because I change the char(10) to <br> in order to have a html document.


I also have to look at this sample.

cJson:=HB_JsonEncode(hHash)

Making a Hash from the data will be a easier way that my way .... Learning allready ))))

[nageswaragunupudi]

Code: Select all  Expand view  RUN

function jsonTest()

   local c, h, j

   HB_CDPSELECT( "DEWIN" ) // or any

   c := "abc" + CRLF + "d ÄÖÜ"  // ANSI
   h := { "key1"=> If( IsUtf8( c ), c, hb_strtoutf8( c ) ) }

   XBROWSER h SETUP oBrw:nDataLines := 3
   j := hb_jsonEncode( h )
   msginfo( j )

return nil
 

Code: Select all  Expand view  RUN

{"key1":"abc\r\nd ÄÖÜ"}

[Ruth]

Dear Mr. Rao,
thank you so much for providing the example. Seeing it in this compact and clear way is really beautiful.

Dear Marc, I admit that for me as a newbie pre-made functions are simply great and convenient.
But your approach, building from scratch is very educational for me and I understand much more through it now .... thank you.

I would have a follow-up question please:
is there a slimmer way of handling the different elements in cVData? Or is it correct to go through it element by element sanitazing with hb_strtoutf8()

kind regards to you all and have a nice weekend
Ruth

Code: Select all  Expand view  RUN

cVData :=   STRTRAN(ALLTRIM(oKunden:anrede),  ";","," ) +";"+;   // 1 ANREDE
      STRTRAN(ALLTRIM(oKunden:name),             ";","," ) +";"+;   // 2 NAME
      STRTRAN(ALLTRIM(oKunden:vorname),          ";","," ) +";"+;   // 3 VORNAME
      STRTRAN(ALLTRIM(oKunden:strasse),          ";","," ) +";"+;   // 4 STRASSE
      STRTRAN(ALLTRIM(oKunden:lkz),              ";","," ) +";"+;   // 5 LKZ
      STRTRAN(ALLTRIM(oKunden:plz),              ";","," ) +";"+;   // 6 PLZ
      STRTRAN(ALLTRIM(oKunden:ort),              ";","," )    // 7 ORT
 

[Marc Venken]

Here is a code the reads CVS. Maybe it will help.
Do know that others can do this probably with just 10% of this code

Code: Select all  Expand view  RUN

function readcsv()
LOCAL cFileCSV := ""
LOCAL aData, nTel

if Empty( cFileCSV := cGetFile(   "DataFile (*.CSV)|*.csv|",          ;
                                    "Select CVS File to convert",1,     ;
                                    "c:\data\mailchimp\" ) )
   return
endif
cdeli = MsgSelect( { ";",","} )


Ntel = 1
oTxtFile := TTxtFile():New( cFileCSV, 0 )
//oTxtFile:Skip() //skip the first line of the header

while !oTxtFile:lEoF()

//  Will fill a array as you like to use arrays more.  (I think the best way)
aData := HB_ATokens( oTxtFile:cLine, cdeli )  
//  Process the Array

//  Will use direct Item pro Item
?StrToken( oTxtFile:cLine, 1, cdeli )
?StrToken( oTxtFile:cLine, 2, cdeli )
?StrToken( oTxtFile:cLine, 3, cdeli )

oTxtFile:Skip()
Ntel++

?StrToken( oTxtFile:cLine, 1, cdeli )
?StrToken( oTxtFile:cLine, 2, cdeli )
?StrToken( oTxtFile:cLine, 3, cdeli )


if Ntel > 10  //  To stop the loop for testing... remove it !
  exit
endif

ENDDO

oTxtFile:Close()

return NIL

[Ruth]

Dear Marc,

thank you very much. This is exactly what I was looking for. From what I understand so far I now can break the string apart into tokens within an array and handle them according to my needs.
For me this is very useful - since the csv is build from different sources like inis, dbfs etc all encoded differently.

looking forward to trying this out in my code. Thanks you again

and kind regards,
Ruth