Resolved Removing New Lines From Csv Preserving Rows

JoseKreif

Member
I got a CSV I am importing using

Code:
 import stream s-in unformatted v-data.


Thing is there is a gift_message column where users say stuff like

"From: Me
To: You

I hope you enjoy"

That one column will be read as 4 rows

1: "From: Me
2: To: You
3:
4: I hope you enjoy"

I tried

Code:
Assign
v-data = replace(v-data,chr(10),"... ")
v-data = replace(v-data,chr(13),"... ").

no luck
 

Stefan

Well-Known Member
If you define a temp-table that roughly matches your csv you can use import without the unformatted option and with delimiter "," - I am pretty sure that this will handle line breaks within fields.
 

LarryD

Active Member
Or alternately you can define temporary variables and do this
Code:
import stream s-in ttfld1 ttfld2 ttgift_message ...
 

Cringer

ProgressTalk.com Moderator
Staff member
Or, depending on your version, and the size of the file, you could copy the whole thing into a LONGCHAR using COPY-LOB and then iterate through the file as you would with any other character variable.
 

JoseKreif

Member
Thank you all for the replies. Unfortunately I am maintaining a program that was written a while ago. So adding the temp table would require rewritting a lot of the program. I guess the person who originally created the program didn't realize in the future the gift message column would be populated.

The users did 2 things wrong.
They used commas in their messages. - This throws off entry()
They used line breaks. This throws off the stream.

I have a fix for this. It's not working properly yet, but a little more time and I'll have it.
Code:
/** Skipping DEFS**/

/** This Will Remove linebreaks **/
Unix(value("awk -F'"' '$NF""{printf("%s ", $0);next}1' newdata.csv > newdata2.csv")).

/** Skipping import **/
/** this next block of code will remove all unwanted commas from the data. **/
    if v-line-cnt > 1 then /** we won't run this against the headers. Otherwise thhey will get deleted **/
      assign
        v-data = replace(v-data,'","',"\t")  /** Here is a special technique to replace the comma delim wiht a tab **/
        v-data = replace(v-data,','," ")     /** now that we removed the comma delim above, we can remove all nuisance commas **/
        v-data = replace(v-data,"\t",'","'). /** all nuisance commas are gone, we turn the tabs back to commas. **/

EDIT: Found the reason it's not working properly yet, my "awk" command did some work, but not perfectly
 
Last edited:

Cringer

ProgressTalk.com Moderator
Staff member
Ah yes. Good old line feeds and commas in user entered csv data.
Would it be easier to force them to go to a pipe (|) delimited file instead?
 

JoseKreif

Member
Ah yes. Good old line feeds and commas in user entered csv data.
Would it be easier to force them to go to a pipe (|) delimited file instead?

We get the CSV from our customer. I could turn them to a "|" delimiter on my own this way.
Code:
      assign
        v-data = replace(v-data,'","','"|"')
        v-data = replace(v-data,','," ") .

I'm at the point to say I think I finally got a solution. I was looking at the raw data and the idea hit me. CONCATENATION

Code:
/** this next block of code will remove all unwanted commas from the data. **/
    if v-line-cnt > 1 then /** we won't run this against the headers. **/
      assign
        v-data = replace(v-data,'","',"\t")   /** Here is a special technique to replace the comma delim wiht a tab **/
        v-data = replace(v-data,',',"..")     /** now that we removed the comma delim above, we can remove all nuisance commas **/
        v-data = replace(v-data,"\t",'","').   /** all nuisance commas are gone, we turn the tabs back to commas. **/
   

   v-max = num-entries(v-data,v-delim). /** count the entries after we fix them **/



   if buffed then do: /** we will now concatenate the last line with the current line **/
     assign
       v-data = buff + ".LF." + v-data
       buff = ""
       buffed = false.
    
     v-max = num-entries(v-data,v-delim). /** re count the entries  **/
   end.
 

   /** Save a copy of the line **/  
  if v-max <= 54 then do: /** this will happen if the line ended pre-maturely **/
    assign
      buff = v-data.
      buffed = true.
    next.
  end.

It's working great. I'll remove "..LF.." before compiling. Just threw it there to stand out where line breaks use to be.
 
Top