对于一个SR的小结 （三）

Ok, I just realized how awful my previous design is, ya, really awful.

   Regarding the comma issue -- the CSV file will take the comma as a separtor automatically, so if you extract the data and separate it with comma, it seems like everything is OK, in fact, some of the data like description, contains the stupid comma, so it will separate the descritption into different column,which seems like a tricky issue.

   Another issue we talked about previsouly is that the special character like  line break. it will also cause a problem, whenever there is a line break, all over again，the data will be populated to another line,beginning from the very first column.

Last time I said I didn’t find any solution to solve this problem but just replace those special characher with blank or something like that, in fact there is a method.

What we need to do is just add the double quote to quote the fields, let’s say I have a descrption, as belows.

        This is just a test, whats’ up?

         this is the second row…..

OK, obviously， there is a comma in the first row and there is a line break between the two rows.

this descrption is stored in one field , when we extract it and export to a CSV file, normally, the first row will be stored into two cells, the second row will be store in the first cell of the second line. But in fact  the whole description should be just stored in one cell.

  So ,here is what we gonna do,

“This is just a test, whats’ up?

this is the second row…..”

 

the double quote hightlights in red, with this double quote, when we export the data into the CSV file, the format will be exactly the same as showing above.

What? are you asking what if there is a double quote in the description ? OK ,don’t worry, we can escape the double quote (I am not sure whether I should call this as escape characher) by adding another double quote to escape it.

Below is the piece of code:

 

'     

let $all_fields = ''''||$quote||''''||'||'||'REPLACE('||&sub1.fieldname||','||''''||$quote||''''||','||''''||$quote2||''''||')'||'||'||''''||$quote||''''
     

Later I will use  $all_fields as a field to select data from one table, the purpose of the replace here is replace the “ 

to “”, so that the double quote won’t take effect.

So now the problem has been solved , no need to replace any character anymore as it’s not a correct cause in fact you changed data although your intention is just remove the weired, special character.

Ok,untill next time……

TJ