problem with export CR or LF characters in Database column

[mrcoollion]

I have a problem with getting column data from a database that contains CR or LF characters and export this data to a CSV file excel import.

I have records in my DB with two columns that contains user entered text. That text contains line endings (CR or LF).
I need to export all columns of the selected records into a CSV file which is tab and CR delimited (tab for cells and CR for record endings) so I can import this file into excel.
In excel a linefeed in a cell is a char(10) code (ALT-Enter).

using

Code: Select all

put revDataFromQuery(,,DatabaseID,tCmd) into tRes

results into a tab and CR delimited 'file' which make no destiction between record endings and user line endings.
And even if I do

Code: Select all

put revDataFromQuery(,"~",DatabaseID,tCmd) into tRes

as a test I still cannot replace the user line endings with CHAR(10) for excel to understand.


Maybe I am on completely the wrong path ?
How can I solve this problem?

Regards,

Paul

[dunbarx]

Hi.

I have never used a DB in LC, but is your question just about translating "tab delimited field/return delimited record" to CSV? CSV is comma/return delimited data.

The instabilities of CSV notwithstanding (that commas are oftentimes part and parcel of the dataset, and not isolated uniquely as "field" delimiters), wouldn't this snippet do:

Code: Select all

put yourData into temp
replace tab with comma in temp

Craig Newman

[mrcoollion]

Hi Craig ,

Did exactly that.

To be complete for others who want to know how i managed this see below.

used <put revDataFromQuery(,"|",DatabaseID,tCmd) into tRes > with a pipeline to get the data with pipeline as a record delimiter.
Then I replace the CR with a code of my own linefeed identifier and afterwards replaced the pipeline record delimiter for a CR record delimiter.

Code: Select all

        replace CR with "@LF@" in tRes // Replace the users line feeds in the text that the use typed to make use it does not conflict with the csv file type
        replace "|" with CR in tRes 

After export i reminded the user how 'when necessary' they can replace my own linefeed identifier in an aditional column with the excel char(10) linefeed.

Code: Select all

   answer "Data has been saved as a tab delimited csv file at location and filename: " & tPathAndFilename&CR&"Please note that the users entered text line delimiters have been replaced with @LF@ to enable the use of a csv file-format!"&CR&"To have line feeds in those cells in excel you can create an additional column and use the substitute function '=SUBSTITUTE(A1,"&q(@LF@)&",CHAR(10))'"&CR&" Be aware that in each language the function and the CHAR() command and item deviders can be different. E.g. in dutch it looks like this  '=SUBSTITUEREN(C2;"&q(@LF@)&";TEKEN(10))'"

Regards,

Paul