Excel CSV import from LC - maintaining carriage returns

[stam]

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

I think Richard's most excellent article CSV Must Die was alluding to that he uses VT (vertical tab - ascii 11) to escape in-text returns in his own exports, rather than this being a CSV standard or indeed anything recognised by Excel. He accurately and quite entertainingly summarises the CSV 'standard':

When you add up all the programmer- and end-user-hours lost to dealing with the uniquely stupid collection of mish-mashed ad-hoc formats that is CSV, it amounts to nothing less than a crime against humanity.

He goes on to propose a saner export format the he uses:

For my delimited exports in Fourth World products I've adopted the convention used in FileMaker Pro and others, with escapes that are unlikely to be in the data:
• records are delimited with returns
• fields delimited with tabs
• quotes are never added and are included only when they are part of the data
• any in-data returns are escaped with ASCII 11
• any in-data tabs escaped with ASCII 29

Where by i'd guess by escape he probably means replace (but i may have misunderstood). But this doesn't solve the issue the OP poses...

As far as i can tell, Excel uses LF (ascii 10) rather than CR (ascii 13) or CRLF to denote 'soft' returns within a cell - but it seems Excel requires some kicking to get it to do this properly... more so on Windows that on Mac (none of this is needed in Mac Excel, it will respect the double quote as a text delimiter even if this contains CR, LF or CRLF. I can only assume it has a better parser than the windows version).

If you paste:

Code: Select all

set the clipboardData to ("A" & numToNativeChar(10) & "B")

inside the text of a a cell (ie after you've clicked on it to activate the cursor in the cell) produces the exact result requested - a cell with 2 lines. But on my limited testing on Windows, Excel is a lot more stubborn and needs kicking with steel-tipped boots.

This problem not limited to LiveCode - there is a lengthy discussion on StackOverflow here: https://stackoverflow.com/questions/124 ... de-a-value
It's telling that many solutions are proposed by people still struggling... but my take-away is that Line Feed (LF - ascii 10) is key to this...

[dunbarx]

Stam

It's telling that many solutions are proposed by people still struggling... but my take-away is that Line Feed (LF - ascii 10) is key to this...

The problem/issue as I see it is entirely in Excel. The fact that one has to "kick" (as both you and I use the term now ) that program shows me that this has nothing to do with LC at all.

Excel simply will not automatically re-format itself with the string "("A" & numToNativeChar(10) & "B")" directly pasted into a cell. It expects, in general, the user to hit cmd-return by hand. Without that keypress combo, it pouts and just sits there. The fact that it "rights" itself after being kicked ought to be embarrassing for it.

Craig

[richmond62]

Without that keypress combo, it pouts and just sits there.

The LibreOffice people should be embarrassed as their Excel equivalent
behaves in exactly the same way.

[stam]

As Richard points out all these big names encouraging CSV ought to hang their heads in shame…

[dunbarx]

Cummon.

To be fair to those large corporations, with their thousands of talented software engineers, they simply must have considered the comma as an arcane and little used character, unlikely to appear in any formatted dataset. You know, like ASCII 222

Craig

[stam]

Excel simply will not automatically re-format itself with the string "("A" & numToNativeChar(10) & "B")" directly pasted into a cell.

well it kinda will - if expressed as a concatenation formula in Excel format, and if the cells are formatted to allow wrapping.
But yeh you gotta do *something* to excel to make it work... the parser on the Windows version of Excel seems lacking in that respect, compared to it's Mac counterpart...

Agreed - 'kicking' is a good description of it

[glenn9]

Dear All,

Thank you all for your advice, hints, tips and suggestions on this problem, I feel that I've learnt a lot and it led to an eventual solution...

After having spent more hours then I'd like to admit on this problem I've now found a 'workaround' that solves the problem...

Just as a recap, my original intention was to be able to make simple flashcards (2 fields, Question and Answer) with LC and then export these as a CSV into a commercial flashcard app (AnkiApp, Anki) as they have all the spaced repetition code built in etc.

Before LC I was creating the cards in Excel, and for multiline cards I had to enter 'Return+tab' in Excel to create a new line within an Excel cell.

I'd then save the Excel spreadsheet as a CSV and import it into the commercial Flashcard app.

There were two issues with this, first of all it was counter intuitive to press Enter+Tab to create new lines and so I constantly forgot to do this, and more annoyingly, sometimes Excel would produce characters that prevented an import by the Flashcard App and so I'd have to hunt these out by trial and error to allow a successful import. Very time consuming and annoying!

I therefore thought I could bypass the direct Excel text entry by importing a LC created CSV file into the Flashcard import... and that's when I discovered the CR issue and my headache started.

Solution: simply through chance I discovered that one of the commercial flashcard apps gives the option to import html as part of a CSV so I simply used LC to replace the CRs with <br>, wrote a function to delete any unwanted <br>s and bingo, I could now import multiline CSV fields without a new record being created. It seems that the <br> acts as a 'soft' CR?

I now no longer need to use excel to create flashcard cards but can use LC instead - much easier

Thank you all again.

Glenn

[dunbarx]

So, LC 1, Excel 0.

Craig

[glenn9]

Hi Craig,

I couldn't agree more...

Off topic, but I wanted to learn how to generate charts with LC using ChartMaker - for very simple charts at my level, I think LC + CM is much easier than using a spreadsheet oddly enough...

...another win for LC!

Regards,

Glenn