Export CSV with carriage returns

[DaannD]

Hello! I need to export CSV from livecode, where in some cells I have data with carriage returns. I have no idea how to do it, for now when I export a CSV, the cell with CRs is splitted into separate columns / rows.

[FourthWorld]

What process will be consuming the data, and does it strictly require CSV specifically?

[dunbarx]

Whatever your reasons for using (the horrible) CSV format, you will have to replace the carriage returns with either a space or empty. At least then the receiving document will not use those returns as record delimiters, which is what you seem to be describing, as if you were pasting into a spreadsheet.

Code: Select all

replace return with "" in yourData

Does this help?

Craig Newman

[FourthWorld]

Removing the carriage returns will break the integrity of the data. They can be replaced with rare characters (I very much prefer the ones FileMaker uses), or escaped. The difficulty with escaping is that there is no single CSV spec, so there's no guarantee that any of the various escape conventions will be supported in the consuming process. And then of course there's the need to escape the escapes, and, depending on the data, possibly also escaping those.

When we learn more about where the data is going and how it will be used we can find a good solution.

[dunbarx]

Yes, what Richard alludes to is that you will likely have to restore those returns one day in order to format your data in its final resting place. So losing them entirely was not a great idea.

Select something in the upper range of the ASCII character set, that is virtually certain not to be found in any conceivable text, and replace with one of those. Then you can easily replace back with returns later on.

Craig

[DaannD]

What process will be consuming the data, and does it strictly require CSV specifically?

My software is a simple storage system for internet shopping, the CSV is for generating auctions in another software, and yes, it strictly require CSV. The CSV must be coded in Windows-1250, columns separator must be ";" and text separator must be ' " ' (double quote).

Here's my code:

Code: Select all

on mouseUp
   local tFileName, tFileContents
   
   ask file "Wybierz folder:" with type "comma Separated Values|csv|TEXT"
   
   if the result is not "cancel" then
      put it into tFileName
      ## Ensure the file extension is csv
      
      set the itemDel to "."
      if item 2 of tFileName is not "csv" then
         put "csv" into item 2 of tFileName
      end if
   end if
   
   
   
   put the dgText of group "magazyn" into tData
   replace tab with ";" in tData
   --set the itemdelimiter to ";"
   
   put tData into URL ("file:" & tFileName)
   
end mouseUp

And it works good for most of the data, where I have everything in one line, but I have one cell which needs to have carriage returns, for example:

---------------------------------------------------
|this is normall cell | this is cell |
-------------------------| where i need CRs |
------------------------|

and here is an image what i get now:

i67.tinypic. com/15zryvb.png (i can't post it as image here, I don't know why. You have to delete space before "com"

As you can see on the image, blue marked items are generated good, but red marked items needs to be in one cell, but livecode generates it as separate rows.

[FourthWorld]

The purpose of adding quotes in CSV is to allow line breaks. A compliant reader will allow CRs in data when that data is enclosed within quotes. Since you already have quotes around field values, you should be fine, no?

[bogs]

i67.tinypic.com/15zryvb.png (i can't post it as image here, I don't know why. You have to delete space before "com"

The reason is because you have (currently) less than 10 posts. At the time of this writing, 8 more should put you over.

In the meantime,

Your file...

Even if you didn't have to get the carriage returns back, I agree with Richard's point of view that you should tokenize them to ensure integrity on retrieval, in all cases, to the best of your abilities at the time.

It is a trivial additon in code that will prevent unforseen problems in the future.

[MaxV]

Hello! I need to export CSV from livecode, where in some cells I have data with carriage returns. I have no idea how to do it, for now when I export a CSV, the cell with CRs is splitted into separate columns / rows.

Export using TAB as separator, then:

Code: Select all

set itemdel to tab
   repeat for each line tLine in myCVS
      replace return with space in tLine
      replace CR with space in tLine
      replace LF with space in tLine
      if the number of items of tLine > 1 then
         put return after desc2
      end if
      put tLine after desc2
   end repeat

Desc2 is now polished data