Page 1 of 3
Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 5:03 pm
by glenn9
Dear All,
Hmmm....I'm having more 'Keys' problems.
I'm wanting to export a multiline LC field (created by carriage returns) and import it into Excel maintaining the CRs that were in the field.
I'm doing the overall export successfully as a CSV file as per LC lesson:
https://lessons.livecode.com/m/4071/l/1 ... a-csv-file
Excel is of course then transcribing the carriage returns that were in the LC field as new rows instead of keeping the multiline field as a multline Excel cell!
However, I don't think this will happen in Excel if I can 'replace' the CR in the field with 'Alt + CR' - faced with Alt+CR separating lines, Excel will create a multiline cell (if this is done manually)
I've tried to do this by code with various permutations of
Code: Select all
...
replace CR with AltKey & CR in field"fExportAnswer"
...
but to no avail!
Just wanted to check if there was a way of achieving this??
Thanks,
Glenn
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 5:16 pm
by richmond62
Probably time to mess around with numToCodePoint . . .
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 5:20 pm
by dunbarx
I am no expert in this, but I do not think that a key "combination" that includes one of the control keys will be seen by Excel, or anything else, as a legitimate string.
In other words, I can replace the string "AB" with the string "XY", but I cannot replace the string "AB" with the string ("X" & optionKey). That is not a string at all. I see that in order to create a multi-line Excel field the optionKey (on Mac) has to be pressed along with the first character of the information on that second line. I just do not think you can do that by send the key combination as a string.
I believe this is true, because the handful of "control" keys do not have an ASCii value, nor, I believe, even a unicode value. I could be wrong about that last, though.
Craig
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 7:41 pm
by richmond62
Use my stack to find what the rawKeyDown/Up code is for Alt + CR:
-
-
And then do something a bit like this:
Code: Select all
. . .
replace CR with numToCodePoint(XYZ) in field"fExportAnswer"
. . .
where XYZ is the rawKeyDown/Up code for Alt + CR.
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 8:34 pm
by richmond62
Whoops, as you cannot work an altKeyDown and a CR at the same time
that's a b*mmer.
Mind you, this might be worth a try:
Code: Select all
...
replace CR with( numToCodePoint(65513) & numToCodePoint(13)) in field"fExportAnswer"
...
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 10:08 pm
by dunbarx
How does using the codePointToNumber function help find a returned value for, say, the optionKey? In other words, how does one characterize that key at all, apart from having LC itself determine simply whether that key is depressed or not, either with the "keysDown" function, the "optionKey" function or the "optionKeyDown" message.
Craig
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 10:17 pm
by dunbarx
I guess what I am really asking is this.
Excel can determine whether the optionKey is down, just as LC can. And if Excel sees that it is in fact down when a return in pressed, it creates a new line within an existing numbered row, what is called a a multi-line row. LC could do that as well.
But how does one send a string to Excel that will invoke that action within Excel? What string, however generated in LC, will tell Excel that the optionKey as well as a return char(ASCII 13) comprise that string, and so create a multiLine row?
I only ask because I am interested in such a process just academically.
Craig
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 10:24 pm
by dunbarx
Richmond
replace CR with( numToCodePoint(65513) & numToCodePoint(13)) in field"fExportAnswer"
numToCodePoint(65513) is a left pointing small arrow. What we need is a value for the optionKey, and I cannot find one, nor for any of the other "control" keys.
Craig
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Mon Feb 14, 2022 11:26 pm
by SparkOut
I don't know, and I can't test, but I *THOUGHT* ON WINDOWS that Excel recorded a line break (enter) as ASCII(13) and a soft return (alt+enter) as ASCII(10).
On Mac or Linux, no idea.
It could be nothing like that in truth though.
Maybe tomorrow night I might be able to try.
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Tue Feb 15, 2022 12:42 am
by dunbarx
Sparkout.
If I set the clipBoardData to ASCII 10, click on a cell in Excel and then paste, I get nothing I can see, and Excel simply focuses on the next cell down.
But maybe there is a character (or string) that will force Excel to "open" a multiLine cell. That is what the OP was originally asking for, long, long ago.
Craig
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Tue Feb 15, 2022 1:17 am
by stam
glenn9 wrote: ↑Mon Feb 14, 2022 5:03 pm
I'm wanting to export a multiline LC field (created by carriage returns) and import it into Excel maintaining the CRs that were in the field.
Hi Glenn,
If i've understood correctly you want to export a CSV where some cells have carriage returns and import them properly in Excel?
Untested, but i'm moderately certain that enclosing the text in double quotes will help Excel read a cell with returns as one cell instead of many records for each return.
Give that a try!
Stam
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Tue Feb 15, 2022 1:30 am
by stam
On a quick test this
does work (beware that if testing from a text editor DO NOT use 'smart' quotes - the default for many apps - or it won't work. Just barn door ascii(34) )
in a text editor the field names are separated by TAB; there is a TAB before the opening quote and after the closing quote for
Field 3:
drag and drop the text file onto excel and you get:
Many horrors of both TSV and CSV exports/imports would be significantly ameliorated if we surround cell data in quotes. I.e. your starting delimiter would either be <new line> & quote or tab & quote (or, for CSV, comma & quote) and your termination delimiter would be quote & tab (or comma) or quote & return. At least for keys that contain the record or field delimiters...
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Tue Feb 15, 2022 9:03 am
by richmond62
Out of interest, have you tried exporting an Excel document with multiline cells to a CSV file and
then imported it into a field of some sort inwith LiveCode?
Were you to do that you could examine the contents of a multiline cell and see what it contained.
I have just been messing around with a LibreOffice Spreadsheet, and thinking a bit, was very careful to export
it to CSV using the Unicode UTF-16 settings (the default setting uses UTF-8) . . .
-
-
I would suppose that a CSV file exported FROM LiveCode also is Unicode UTF-16.
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Tue Feb 15, 2022 9:11 am
by glenn9
richmond62 wrote: ↑Tue Feb 15, 2022 9:03 am
Out of interest, have you tried exporting an Excel document with multiline cells to a CSV file and
then imported it into a field of some sort inwith LiveCode?
Were you to do that you could examine the contents of a multiline cell and see what it contained.
I have just been messing around with a LibreOffice Spreadsheet, and thinking a bit, was very careful to export
it to CSV using the Unicode UTF-16 settings (the default setting uses UTF-18) . . .
-
SShot 2022-02-15 at 9.59.22.png
-
I would suppose that a CSV file exported FROM LiveCode also is Unicode UTF-16.
Thanks for the tip Richmond - I'll give that a go.
the background to my headache is that I'm wanting to import the csv file into a flashcard program, I'm currently using AnkiApp, but this, like Excel, turns CRs into new discrete records, unhelpfully!
Re: Excel CSV import from LC - maintaining carriage returns
Posted: Tue Feb 15, 2022 9:22 am
by glenn9
stam wrote: ↑Tue Feb 15, 2022 1:30 am
On a quick test this
does work (beware that if testing from a text editor DO NOT use 'smart' quotes - the default for many apps - or it won't work. Just barn door ascii(34) )
in a text editor the field names are separated by TAB; there is a TAB before the opening quote and after the closing quote for
Field 3:
Screenshot 2022-02-15 at 00.24.01.png
drag and drop the text file onto excel and you get:
Screenshot 2022-02-15 at 00.23.30.png
Many horrors of both TSV and CSV exports/imports would be significantly ameliorated if we surround cell data in quotes. I.e. your starting delimiter would either be <new line> & quote or tab & quote (or, for CSV, comma & quote) and your termination delimiter would be quote & tab (or comma) or quote & return. At least for keys that contain the record or field delimiters...
Hi Stam,
Thanks for the suggestion.
I've tried to replicate this but unfortunately no success, I'm assuming its a Windows thing. Excel just seems to strip everything after the first line, despite the double quotes!
I'll keep on tinkering....
Glenn