CSV import/export issues with Excel

Panorama X
1

I need to be able to move back and forth between Excel and Panorama. (I’d love to do the whole thing in Panorama but I have to have something that will work on an iMac, a MacBook Pro and iPhone and iPad.)

The Excel table includes a cell with text in it that includes returns. (typed in Excel with an Option-Return keystroke). After saving to CSV in Excel, the PanX command File>New Database From Text… command pulls the table into Panorama seamlessly. But after editing the file in PanX and exporting to CSV file, when I import that file, the resulting cells (that originally had returns in them) have some other character that Excel flags as a box with a question mark in it. I haven’t been able to identify the character nor can I copy it into a search/replace dialog in Excel. I’m just stuck.

Can anyone help me?

2

It seems, Pan X has converted those Excel in-cell-carriage returns during the import to other line-breaking characters like LF or vertical tabs.
You can identify that character in apps like TextWrangler or BBEdit.
If you can’t copy, search, and replace that character in Excel, you better replace it already during the export (with an export formula in Panorama’s Text Export wizard).
There are functions in Pan X that do those conversions e.g. vtabtocr( .
Note: Carriage returns will be interpreted as separators between records, if your multi-line text is not quoted.

3

It appears that when Excel exports, it is just using a carriage return. The surrounding quotes identify it as a line break within the cell. When Panorama exports, it is using a vertical tab, chr(11).

4

It isn’t happening during import. It’s happening during export. This is documented in the 0.1.025 release notes.

http://forum.provue.com/t/panorama-x-0-1-025-2011-release-notes/251

5

Thanks all. If PanX is using a vertical tab (and it looks like it is, then there is no way I can write a formula in Panorama to do the job, since Excel is expecting a plain old carriage return. Nor can I program Excel to do it (well maybe VBasic…Yuck). It looks like my only option is to use a 3rd app like a global search and replace in TextWrangler. But at least I have a work around if I’m willing to use it.

I understand the priorities and limitations of programming time, but It’s disappointing that I can’t do this seamlessly now. 20 years ago I tried to persuade my office to purchase copies of Panorama to use but Filemaker won out then because it was really seamless to move data back and forth with Excel, and saving to a csv file first was just a bridge too far compared with dragging the excel file onto the Filemaker app icon, plus you could very easily script both export and import on the FileMaker side.

6

So Excel doesn’t support a vertical tab as a line break in imported text? I thought that was a standard. I know that FileMaker will import a vertical tab as a line break.

7

Not sure yet what’s going on. I have the latest version of Excel for Mac. The vertical line break character is not inserted on export. When I export a cvs from Excel I get plain returns inside double quotes for that cell. But when I import that same file back into Excel without doctoring at all, it interprets all returns as end of record markers and when I reach a multiline cell only the first line goes in the record. Subsequent lines end up on their on record in the first field. So Excel isn’t even handing it’s own files in the same manner.

I"m investigating now what would happen if I change the vertical line breaks in the PanX export into new lines \n in a third party tool, rather than \r\n which is what is what the Excel export does. I’ll let you know what work around I come up with, (if I ever succeed that is). Microsoft succeeds in frustrating me again!!

8

And I forgot to actually answer your question. When I import the PanX exported csv. The vertical line breaks do come into correct cells. But they show as just a control character symbol rather than being interpreted as an in cell return. I may actually be grateful for that if I can’t get \n to work. I could probably then write a quick macro to search and replace the char(11) with the one Excel uses, if I could ever find out what that is. I get it by typing option-return on my keyboard. But I"m not sure what Excel actually puts in the file.

9

Nope replacing char(11) with char(10) does not work. searching Excel forums now. Surely I’m not the first one with this problem.

10

Thanks to all of you for your help.

Current final solution, until I figure something better out:
I have a PanX procedure that:
morphs the column replacing all embedded \r with a tag (I chose
).

11

Sorry hit return and it sent the message before I was ready.

My PanX procedure:

replaces all instances of \r with

Exports the file
replaces all instances of
with \r

Then on the Excel side I wrote a VB script (recorded it mostly) to import the file and then replace
with chr(13). FWIW vertical line space chr(11) didn’t work nor did chr(10). I have no idea what would work on the Windows side of the earth (I’m guessing chr(13)&chr(10) which is the Windows standard for carriage return within a text object.

12

Sorry everybody. I’m obviously blog editor challenged. I replaced char(13) with
on export and then replaced
on the Excel side with char(13)

13

You would have to replace chr(11) with chr(13), that is VT with CR.

Excel has several replace functions: one for replacing a certain number of characters once per cell, and a second one that replaces multiple occurrences of text in a cell. You would need the second one.

If I translate my German Excel code correctly then the english code might look like this:

=CHANGE(A1;CHAR(11);CHAR(13))

where cell A1 contains the multiple line text.

14

Dang, I get it now. The tag I used in my replacement is the html tag for break so it never shows up in my attempt to tell you what I did. Instead it’s just interpreted as a break in my post.

15

Thanks KJM, Actually I want to do an entire column at once, not just a replace in a single cell. So I ended up with this VBS script:

'Create the argument
arg = "FORMULA.REPLACE(""" & Chr(11) & """,""" & Chr(13) & """,2,1,FALSE,FALSE,,FALSE,FALSE,FALSE,FALSE)"

'Execute an XLM macro
ExecuteExcel4Macro (arg)
16

I’m not an Excel user, and don’t have a copy handy at the moment. What does a multi-line cell look like in Excel? Does the whole row get taller?

17

I just tried Numbers, and found that I could press Option-Return to insert a carriage return. Is Excel similar?

18

I’ve discovered that I can transfer data with returns in the cells from Numbers to Panorama X then back to Numbers again w/o problems. So an adjustment is needed for Excel.

Direct import/export of Excel files is something I want to add to Panorama X, so that would hopefully solve the problem as well.

19

It is similar, but not the same in Excel: You have to press ctrl-cmd-return for a line break in a cell.

If the cell is formatted for text wrapping, then the line height will grow.

20

I’m not an Excel user either, but I do have access to a computer with Excel installed. Here is a picture of a test I did using option-return for in cell line breaks.

Excel has more than one CSV format.

Screen Shot 2017-08-26 at 8.59.04 PM.png440×566 62.7 KB

I couldn’t see any difference between Windows and MS-DOS. Both the UTF-8 and Windows formats used a simple carriage return for in cell line breaks. UTF-8 also used a carriage return for line breaks between rows, but Windows and MS-DOS used crlf between rows. UTF-8 also has a UTF-8 byte order mark at the beginning of the file.

Here is a hex dump of the UTF-8 file.