Excel CSV import from LC - maintaining carriage returns

[richmond62]

Ouch:

https://www.fourthworld.com/embassy/art ... t-die.html

And here is Whackypedia:

https://en.wikipedia.org/wiki/Comma-separated_values

"Separating fields with commas is the foundation, but commas in the data or embedded line breaks have to be handled specially."

emphasis added.

[richmond62]

And, while I am being a complete pain . . .

I wonder what Excel will make of LF (Line Feed) chars (Unicode: 0x0A / 10)?

And FourthWorld states this:

"any in-data returns are escaped with ASCII 11"

What VT means escapes me: but it does produce a Line Feed.
-

-
AND:

viewtopic.php?f=7&t=31922&p=174436&hili ... SV#p174436

[glenn9]

And, while I am being a complete pain . . .

I wonder what Excel will make of LF (Line Feed) chars (Unicode: 0x0A / 10)?

And FourthWorld states this:

"any in-data returns are escaped with ASCII 11"

What VT means escapes me: but it does produce a Line Feed.
-
SShot 2022-02-15 at 12.01.27.png
-
AND:

viewtopic.php?f=7&t=31922&p=174436&hili ... SV#p174436

Ooooh... that looks interesting... can't wait to give the above code a try... Thanks for digging out that post! Glenn

[glenn9]

Ouch:

https://www.fourthworld.com/embassy/art ... t-die.html

And here is Whackypedia:

https://en.wikipedia.org/wiki/Comma-separated_values

"Separating fields with commas is the foundation, but commas in the data or embedded line breaks have to be handled specially."

emphasis added.

yep, agree re the CSV issue.

The flashcard program is equally at home with importing TSVs as is LC in exporting TSVs so I guess easy enough to switch to Tab separated data instead of CSVs...

[stam]

I'm assuming its a Windows thing. Excel just seems to strip everything after the first line, despite the double quotes!

You're right - it works on Mac Excel but not Windows Excel. One more reason to switch to Mac

Joking aside, if this s viable for you, you may also want to look at the commercial plugins for Excel and maybe export as a native excel file rather than text file - you may have more success that way. I don't have this but perhaps if another user here does they can test for you?

Stam

[dunbarx]

I did this in a new Excel spreadSheet. I typed "A", then option-return, then "B", and got this:

Excel.png (5.46 KiB) Viewed 5882 times

Then I copied that cell, went to LC and ran:

Code: Select all

on mouseup
   put the clipBoardData into temp
   repeat with y = 1 to the number of chars of temp
      put char y of temp && charToNum(char y of temp) into line y of accum
   end repeat
end mouseup

"Temp", pasted back into Excel, is:

temp.png (6.29 KiB) Viewed 5882 times

"accum", pasted back into Excel is"

accum.png (3.8 KiB) Viewed 5882 times

Excel does its own thing with its data and its control options in order to work. But so does LC, and I do not see a way to make the two work together, that is, to create a string in LC that will paste into Excel and produce the "simple" multi-line cell.

Craig

[glenn9]

I did this in a new Excel spreadSheet. I typed "A", then option-return, then "B", and got this:
Excel.png
Then I copied that cell, went to LC and ran:

Code: Select all

on mouseup
   put the clipBoardData into temp
   repeat with y = 1 to the number of chars of temp
      put char y of temp && charToNum(char y of temp) into line y of accum
   end repeat
end mouseup

"Temp", pasted back into Excel, is:
temp.png
"accum", pasted back into Excel is"
accum.png

Excel does its own thing with its data and its control options in order to work. But so does LC, and I do not see a way to make the two work together, that is, to create a string in LC that will paste into Excel and produce the "simple" multi-line cell.

Craig

Thanks for looking at this Craig,

I agree, I've trialled and errored so many permutations without success that I think you are right.

I guess for the flashcard program I'll just need to work in a spreadsheet rather than in, what I was hoping for, LC!

Regards,

Glenn

[dunbarx]

Nobody has chimed in with anything yet, so maybe it is true.

You can stay in LC if you make your own "spreadsheet". This might be a table field, a dataGrid, or something you build from whole cloth. How "large" a spreadsheet will you need?

Craig

[bobcole]

Here is a good website that provides explanations of the control characters.
Scroll down about half way to see details about each code, especially the LF code and its representation as: \n
Hope this helps,
Bob
https://www.aivosto.com/articles/contro ... cters.html

[glenn9]

Hi Craig,

yes, I was thinking along those lines as well and I'd even got as far as setting up a simple sqlite database table with question and answer columns that was easy to populate with LC.

Getting the information out of Sqlite with LC is straightforward as well but that's where it all ground to a halt as the flashcard program needs to import a CSV file, but not being able to export multiline answer fields as part of the CSV is a showstopper for me...

I guess I could try and 'roll my own' flashcard' functionality within LC... if only I could code a Leitner like spaced repetition recall system in LC.... but this seems to be no mean feat... viewtopic.php?t=28095!

Regards,

Glenn

[jacque]

LC uses ascii 11 as a soft break and apparently csv does too, from Richard's article. You might try that.

[stam]

This is not straightforward and not an LC issue as such - it's Excel on Windows that's a pain.
The solution i've found that works on windows so far involves formatting the field with line breaks as a concatenation and the cells in Excel formatted to allow wrap text.

the cell data with line breaks needs to be formatted as a concatenation formula, where each <end of line> is an LF character (ascii 10). The cell data

Code: Select all

do i hate windows? 
or just excel for windows?

would be formatted as:

Code: Select all

=("do i hate windows?"&char(10)&"or just excel for windows?")

Note: starts with '=' to denote formula surrounded by parentheses; no spaces between the elements (ie outside of quoted text).


AND you you need to format the cell alignment to allow for wrap text (right click -> format cells -> alignment -> wrap text)

you can just copy/paste the formula above into a cell in excel. Then format the cell so that wrap text is ticked - then you'll have a multiline cell (tested on Mac and Win).

[dunbarx]

Jacque.

LC uses ascii 11 as a soft break and apparently csv does too, from Richard's article. You might try that.

Exactly the sort of LC-generated string we would need. Unfortunately this:

Code: Select all

set the clipBoardData to "A" & numToChar(11) & "B"

just puts "AB" into a single cell in Excel.

Stam may have found a way to do actual work for himself. I am just playing around with the concept.

Craig

EDIT: Same thing happens with ASCII 10

[stam]

No worries Craig - when even StackOverflow struggles to answer the question reliably you know you're in strange waters...

The faff to format this in liveCode is:

- the carriage return inside the key/cell should be replaced with "&char(10)&"
- each line of text needs to be enclosed in quotes, ie: quote & line1 & quote & "&char(10)&" & quote & line2 & quote
- the whole key/cell needs to be wrapped in a function =(), i.e.:

Code: Select all

"=(" & quote & line1 & quote & "&char(10)&" & quote & line2 & quote & ")"

And then as mentioned you need to format the cells inside Excel to allow wrap text as well....
Not cool Microsoft, not cool...


-------------
EDIT:
fairly simple to write a function to do the above:

Code: Select all

function convertMultiLineText pInput
    local tOutput
    repeat with x = 1 to the number of lines in pInput
        put quote & line x of pInput & quote into line x of tOutput
    end repeat
    replace return with "&char(10)&" in tOutput
    return "=(" & tOutput & ")" 
end convertMultiLineText

if the input (i.e. cell) is

Code: Select all

line1
line2
line3

this returns

Code: Select all

=("line1"&char(10)&"line2"&char(10)&"line3")

which you can just slot into your tab or comma separated values as a field/column.

Not sure there is any way to automate the wrap text property inside Excel though. Some kind of macro? On Mac this is probably appleScript-able, but then again this isn't needed on mac as mentioned above (although this approach will work). Not sure what'd you'd do on Windows Excel other than set this manually.

[dunbarx]

Well, here is an important and likely useless piece of information.

I create a multi-line cell in Excel, say "A", a soft return and a "B". Works just fine. Copy and take over to LC, and:

Code: Select all

on mouseUp
   put the clipBoardData into temp
   set the clipBoardData to temp
end mouseUp

Char 3 of the clipBoardData is ASCII 10

Now back to excel and paste into a new cell. I get only the "A". But if I then double click on that cell, the original multi-line pair appears perfectly.

So Excel has the right data, with all the quotes it added as well as the right soft return, but has to be kicked in order to re-display it.

Craig