Excel imports chr(8232) as "
 ". Using whatever technique it was to evaluate control-return, can you find a chr( value for control-option-enter?
vs.
michael
"
 "
That sounds like an added mystery of different binary text encodings. I suspect these these ‘solutions’ are getting more complicated than necessary, even for dealing with the Microsoft chaos demon.
Is there ANY pattern of csv or tsv data which Excel (or whatever problematic destination) will successfully import with the desired multi-line cells? Can multi-line cells created in the problematic destination be exported by it into a text format and then imported right back by it unchanged? If not, then shame on its programmers!!! If it can, analyze the successful text file’s format with suitable tools (potentially PanX’s, perhaps BBEdit or a hex editor.)
PanX’s great text manipulation tools should be able to produce ANY desired text format. If you can’t get the PanX in-cell line breaks past PanX’s csv or tsv export parsers directly into the desired format, then do it in TWO PanxX steps:
First, export it with the in-cell line breaks converted to some arbitrary, suitable string (such as “#$#$”.)
Second, fileload( that exported text file back into a PanX variable, use replace( to change your arbitrary string to whatever the desired text format needs and filesave it back to disk. If necessary add appropriate binarytotext( and/or texttobinary( functions to deal with non-UTF-8 encodings.
Size shouldn’t be an issue. I recently used replace( on 6MB text variables, with both chr(0) and chr(1) as arbitrary intermediate strings, without problems and quite quickly. Thus doing all within a friendly, re-usable, PanX procedure.
After extensive testing, I believe the simple answers are NO and NO.
I recall many years ago, using Panorama 6.0 and Excel in Windows, that I could do a direct import but I have no idea how I did it.
An interesting side issue is that an Excel in-cell CR in Windows is alt-enter, whereas in macOS it is control-option-enter.
My current solution now has a cmd-opt-x keystroke triggering a macro which imports the text file, restores the CRs and saves the file. So the whole process involves just running a Panorama X procedure, opening the Excel file and keying cmd-opt-x. I don’t think it can get much simpler than that.
I had high hopes for the filesave statement for a while but it doesn’t do the job.
Using my Excel for Mac 16 I created such a cell. On my keyboard that took control-optic on-return. Oddly Excel for Mac’s help provided the Window’s direction of alt-enter. No ‘enter’ key on my keyboard; its equivalent would be fn-return, but I didn’t need to hold down fn to get the desired result. I exported my creation. Hex Edit showed crlf() between rows and lf() alone within the quoted multi-line cell output. Importing back the file via Excel’s File>Open split the cell across multiple rows; ie. it didn’t work.
I then tried Jim’s favorite, when all else fails, trick, I google’d the problem. And found the odd news reported that no the import couldn’t work via File>Open, BUT it would work if the csv file were opened via double-clicking it in Window’s Explorer! Of course my Mac doesn’t have that and I didn’t want to configure it to automatically open all csv files in Excel. So instead I tried right-clicking it, choosing Open With and then Excel from the submenu. Voila, it imported with multi-lined cells as first created. Weird that it works differently that way, but it does work and is easy to do, if perhaps not to remember to do!
I’m still using MS Office 2011 on a 2018 iMac and your solution doesn’t work for me - every cell in each record gets stuffed into Column A in the Excel spreadsheet.
Excel and VBA (especially VBA!) can vary wildly from one version to another in what they do and how they work. It makes me very grateful for Jim’s commitment to legacy support - I don’t think Microsoft know what the words mean.
1 Like
It may not work on all systems, but the google hit that I first saw on this was dated 2007 so it at least worked on some systems prior to yours. I gave up trying to use visual basic once I figured out how to use Panorama to accomplish the same things… back around Pan3. Much easier to code. Although I still use Excel for non-programmed spreadsheet purposes.
But the important question is whether this trick works on Janet’s system! If it would a routine PanX csv export would give her what she wants, no other trick needed. If it doesn’t work than your visual basic code may work for her system. My dim recollection of using visual basic was that it was difficult to code, but worked well when it worked.
Another option would be finding an intermediate program that could successfully import the PanX csv and import it as an .xls or .xlsx file which could then be further imported into Excel. Mac’s Numbers.app claims to import csv and export .xlsx. There are multiple freeware office suites which claim such. She’d have to experiment to find what worked, but the process, although tedious, would be straightforward. If such a working process could be found it might even be possible to automate it using Automator, AppleScript, Keyboard Maestro, etc.
Microsoft may not be consistent on the details over the years, but it at least used to understand legacy support. A DOS program running under Windows emulation ran my office and insurance billing for 17 years before the government finally mandated changes requiring a 13 version at once upgrade to it. Although I couldn’t have done that without Panorama. I could import the raw DOS data files into Panorama, see every field and do analysis the newest versions still don’t allow. And I could export valid versions of the data files that worked in the DOS version if desired. I can still import and analyze from the newer Windows version, but alas no longer can export back to it.
Panorama X exports multi-paragraph text correctly by replacing the carriage return chr(13) with the vertical tab chr(11) automatically. So the text of one Panorama X cell gets correctly imported into only one Excel cell.
In that Excel cell the vertical tab appears as a framed question mark character, and the visible text in the sheet is not wrapped into paragraphs. But if you try to edit this cell, you might have to expand the editing line to a multi-line area, and the text appears wrapped in paragraphs now: In editing mode, Excel is interpreting the vertical tabs correctly.
Now the problem remains how to replace chr(11) with chr(10) (Line Feed) or chr(13) (Carriage Return). That should be possible with Visual Basic, but I have not been successful trying that. Michael perhaps?
On my machine control-option-return produces a chr(3) character that looks like a standard carriage return but is actually the ETX (end of text) character. Don’t know if that is able to pass through Excel or not. Control-option-enter does not produce anything in my testing.
I think Michael question is about keystrokes in Excel. You can use ctrl-opt-return and get a line break in a cell. When I copy that cell contents into BBEdit, I see that Excel uses chr(10) for the line breaks.
I found a way to replace VT characters in an Excel worksheet.
The multi-paragraph cell text, exported out of Pan X as a CSV file and therefore containing vertical tabs, imported into Excel as UTF8-coded text, is e.g. in cell A1. Cell B1 contains this formula:
=SUBSTITUTE(A1;CHAR(11);CHAR(10))
If cell B1 is formatted to allow line breaks, you will now see a multi paragraph text cell in your Excel worksheet.
Possibly he was but I made it clear I did not have Excel and could not test these odd characters in Excel myself. I generated these in Panorama in a blank text cell and then used message str(asc(«»[1;1])) to show the chr( #.
Copying the cell could very easily convert the character to a line break which the clipboard does normally to return characters. Oh well, I’m definitely out of my element here since I’m not an Excel user so I guess I’ll step to the side.
I also got chr(3) which does nothing in Excel.
I’m using vbCr as the replacement.
Well actually, it does. That involves loading a procedure into the database to be exported which is irritating and time-consuming.
The current iteration consists of a stand-alone folder, containing two Panorama X files and an Excel file, which can live anywhere you like. An alias on the desktop (or wherever you like) opens one of the Panorama X databases. Its .Initialize procedure opens a Find & Open dialog in which the user navigates to the database to be exported. When a file is chosen the procedure opens it, exports to a text file all visible records, with carriage returns encoded, and opens the Excel file. If the Cancel button is chosen in the dialog box, the file closes.
The Excel file has an Auto_Open macro which automatically imports the text file, restores the carriage returns in cells, copies the contents of its sheet to the clipboard and closes itself.
The Panorama X procedure deletes the text file and closes both the source file and itself. The user then opens a new Excel file and pastes the contents of the clipboard. It all takes longer to describe than to implement.
And the second Panorama X file? Its .Initialize procedure executes an openplain statement and closes itself. The openplain allows the user to access the first Panorama X file without triggering the .Initialize procedure.
My next step is to investigate bypassing the interim text file by using the clipboard. I don’t know whether this will be a worthwhile step - it may be slower for very large files.
After lots of distractions by grandchildren and others, I’ve got back to this and put together a workable package. Before I put into the public arena, I’d like to test it on a later version of Excel than my 2011. And, given that this is all in response to a request from Janet, it should be tested on whatever version she has.
So, it can be downloaded from Dropbox - File Deleted for testing.
It is reasonably well documented and should be simple to test and I’ve even included a small test file.
Looking forward to feedback.
I am getting a #404 error on Dropbox.
The link doesn’t work for me either.
Ditto on the #404 error.
OK, try this:
… and the PDF of the documentation is here:
Michael, first of all: Thank you for sharing your solution. So I learned something: Your “vbCr” was a good hint. I did my tests in Excel 16.20 (that is using VB 7.1) and found a quite simple solution.
As Jim pointed out, Panorama X is converting all kind of LF or CR characters to vertical tabs when it exports databases as CSV or TSV files. So there is no need anymore to replace them with anything else.
Export the Panorama X database with the multi-paragraph text cells. Use the File menu command to export it as a CSV file.
Import (File menu command) the exported CSV file into an Excel worksheet. Make sure you select UTF-8 as source in the Import … dialog.
Then select the imported text cells and run the following VB code:
Selection.Replace What:=vbVerticalTab, Replacement:=vbCr, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
The text cells should be formatted to allow line breaks.
In my test I had to click in and out of the cells. Then they appeared in the worksheet showing the text in paragraphs.