Trouble importing Spreadsheet data in Accounting format

Panorama yields an unexpected result when importing data from Excel which has been formatted in Excel’s Accounting format. Excel’s Accounting format displays a field containing a zero value with a simple dash (”-“). Exporting Excel data in this format to a CSV file retains the dash in the CSV file. After importing the CSV file into PanX, any cell that had a dash is filled with the value of the first cell to the left that contains a non-zero value (the first cell to the left that does not contain a simple dash.) The solution is to first format the Excel data to Excel’s Number format, which replaces the dash with a zero. Then export the data to a CSV file followed by a PanX import of the CSV file. Note that this same problem occurs with the Panorama X ImportText command. One might assume that Excel’s export function would change a dash to zero when exporting to CSV files, but it doesn’t. The problem that I have is amplified because much of my original Excel data has been imported into Apple Numbers, and apparently there is no easy or obvious way to removed the dash’s once the data is in Numbers. It would be good if PanX could replace the dash with a zero value on import or at least leave any dash filled cell blank on import.

  1. You mentioned the best solution already: Format the Excel cells simply to decimal numbers before exporting them as a TSV or CSV file.
  2. Numbers has a global Find & Replace function that you can use to replace the dash with zero. Or you create a another column with a formula that calculates this substitution.
  3. Panorama — 6 as well as X — has the ability to do substitutions like this on the fly during the import. See importusing for Panorama 6 and importtext and its options for Pan X.
  4. In my experience, the Accounting number format (with a leading currency symbol) is not recognized as a number format, when I copy data from Excel to Numbers, and I am afraid Panorama will import this format as text only, too. So my export advice goes back to point 1: Format those cells in Excel as Standard or as decimal numbers.

Much thanks for your response. I agree that anyone wishing to export data from Excel into Panorama X should make sure that they have first formatted the data in Excel to Excel’s Numbers format, but I also believe that Panorama X should be enabled to properly import the CSV file when a user inadvertently exports Excel data into a CSV file without following the strict Numbers formatting protocol. By definition, the CSV file is a text file, and one should expect Panorama X to read the CSV file in a similar manner as other number crunching programs like Apple Numbers. The unexpected behavior of filling cells containing a “dash” with non-zero numerical values found somewhere to the left of the subject cell does not building user confidence in Panorama X. Panorama X is a fantastic program, and it is my humble opinion that every reasonable opportunity to increase user usability and user confidence is important. I know Jim has a zillion things to do, but I also believe this issue should be added to his ToDo list.

Before two days ago I had never heard of Excel’s “Accounting” format, and I still don’t really know what it is. I am not an Excel user, and before Thursday, no customer had ever mentioned this format or problems with this format.

Though I don’t know about Accounting format, I do know how Panorama imports text, and the scenario you have described sounds highly unlikely. Panorama is never going to use the value of a cell to the left, each column in the text file is placed in the same column in Panorama.

If you are importing into a new file, all columns will be text, and if a column contains a dash, that is what will be imported into that column. In other words, Panorama just imports text “as-is”.

If you are importing into an existing database file, the column being imported into may be a numeric field already, so Panorama has to convert the text to a numeric value. In that case, I would think that a dash would import as zero, though I haven’t checked it just now. But there should not be any way that it would import the value of the field to the left. If you have a combination of files that does that, I would be very interested in a copy of the files for testing. According to Kurt’s reply the Accounting format involves currency symbols, not dashes, so I’m not really sure what the problem data looks like.

Ultimately, the best solution here is most likely to allow Panorama X to read Excel files directly. That is on my to-do list for future consideration, but obviously is not a trivial project.

Ok, Kurt says that Panorama X does do the same thing as Numbers with this data. I don’t know, but I do know that Kurt has been a very reliable source over the years.


Bottom line, at this point I have no idea what the issue really is, or if there even is an issue that Panorama X can resolve, so I’m not putting this into Bitbucket at this time.

I can prepare and send a combination of files to demonstrate the issue. I have only tested this and found the issue while trying to import data into an existing PanX database with PanX fields formatted as floating point numbers. How is the best way to get files to you?

There is a support email address on the web site. Compress the files into a .zip file before sending, and include instructions on how to duplicate the problem. Keep in mind that I don’t have Excel, so you’ll need to send the exported text file. But a screen shot of what it looks like in Excel might be helpful.

An old problem as surfaced again. PanoramaX has a problem with the ImportText statement if the source data (the data being imported) contains a dash ("-") instead of a numeric value. The problem is only manifested when copying data from a spreadsheet (either Excel or Numbers) and then importing that data into a PanX worksheet using the ImportText Z, “ExistingData”, “Replace”. There are two additional conditions for this to cause false data. 1) The PanX worksheet receiving the data must be formatted in numeric type to receive the incoming text numbers. 2) The data cell left of the dash in the source spreadsheet must contain a numeric value.

When those conditions are met false data appears in the PanX worksheet receiving the data. What would have been dash just to the right of a numeric value will be replaced by the numeric value to the left of the dash.

I tested this with a PanX worksheet formatted in text, and there was no false data. False data only appears when a PanX worksheet fields receiving numeric data are formatted to numeric type.

Yes, I know this is strange and hard to explain, but quite easy to replicate. However, it can be a major problem when importing data from spreadsheets via ImportText.

I am not a frequent poster on this discussion group, but I think this topic needs to be renamed. Sorry, but I can’t see how to rename the topic.

There are basically two ways to handle your problem:

  1. Use a different number format in the source application (Numbers or Excel).
  2. Or you replace the dashes using the REARRANGE option during the import. So you are able to replace the dashes with zeros.

Thanks for your suggestion. I am still struggling with this problem. Dashes are not easily replaced or rearranged since a dash is used for a minus sign. In spreadsheets there may be two kinds of dashes. There are dashes that mean the cell is empty or has a zero value, and there are dashes that the spreadsheet treats as text. A text dash can appear anywhere in a spreadsheet, and a spreadsheet can contain many fields. So it is not trivial to capture and fix.

There must be some hidden code in the spreadsheet that differentiates the two dashes and that ultimately causes PanX to give false data. I exported the table containing the suspect dash into a tab delimited text file. While Apple Numbers opens the Text file data correctly, PanX opens with that mysterious false data. Strangely the false data does not occur when PanX opens the data in a file with all text fields.

Rather than trying to describe this, can you please supply an example of the text that you are trying to import that causes this problem? It sounds like you are saying that the spreadsheet is exporting a zero value as a dash. I’ve never heard of that before, and certainly Panorama is not expecting that.

Are you importing these values into integer or floating point fields?

I just tried an experiment to try to duplicate what you are describing. In an integer field, importing just a - worked, it came in as zero. In a floating point field, it came in as a very small value, but not zero.

Ok, I just have no idea what you are talking about. Why would a dash be to the right of a numeric value? Is this some weird way to represent a negative number? Please supply an example of the data you are trying to import. Since it is text you should be able to just paste it into this forum.

James is referring to Excel’s or Number’s “Accounting” format:
Bildschirmfoto 2020-04-15 um 11.08.20
Well, my current Excel version does not actually behave this way; it displays the Accounting format like 2-digit Currency numbers, but this may be a bug in my “Fast Lane” preview version. I remember Excel had the Accounting format just the way the Numbers screenshot is showing, but with additional options for zeros (currency symbol + zero or currency symbol + dash) and for negative numbers (minus sign or brackets).

I’d like to point out that this is just a matter of number formats in Excel, similar to having a numbers field in Panorama with an output pattern. Once the data are exported as text, the result is text. So I always prefer the exported text to have a format that you can easily import into your database.

When I export accounting data from Excel to Panorama, I make sure the exported numbers are formatted as easy as possible (e.g. 2-digit decimals). (In fact, I have made a VBA macro for the data export that reformats the copied data before it saves the text file.) Changing the format in Excel is an easy step. No values are changed, it is just how the numbers are displayed. Handling those format options in Panorama during the import of the text file may be tricky and result in clumsy import formulas.

Here is how to replicate the problem. I have uploading two documents.

The first is a PDF describing the steps to be taken.
[https://www.dropbox.com/s/elaa2x8e8yumgcl/PanX%20Spreadsheet%20Data%20Import%20Problem%20Replication.pdf?dl=0]

The second is an Apple Numbers spreadsheet that has a text dash in a few cells.

Both files can be downloaded to your computer. Much thanks for helping with his issue!

James, the data in the spreadsheet is not consistent.

There are cells with the value “0”, and there are cells with the text entry “-”.

This is the big disadvantage of the Accounting number format: You might think an entered dash would be interpreted as value zero. It is not.

Numbers automatically displays the text cells left-aligned, while in cells with the value zero the dash is automatically aligned to the right.

I can confirm this is a bug that affects any cell that consists of only the “-” character except it it is in the first cell of the line. I’m guessing that any such cell in the middle of the line is being interpreted as a minus and is subtracting 0 from the previous cell’s value (just a guess).

I made a procedure to test this in a simple database containing only 4 fields set to Number (Float). I ran this procedure:

let A=“34”+tab()+"-"+tab()+"-"+tab()+“998”+lf()+
“-”+tab()+“541”+tab()+"-"+tab()+“869”
importtext A,“ExistingData”,“Replace”

Which produced this result:

image

I then added a Rearrange option to the importtext statement to filter out the standalone minus characters:

let A=“34”+tab()+"-"+tab()+"-"+tab()+“998”+lf()+
“-”+tab()+“541”+tab()+"-"+tab()+“869”
importtext A,“ExistingData”,“Replace”, “REARRANGE”,arrayfilter(import(),tab(),{?(import()="-",“0”,import())})

Which then produced the desired results:

image

Note that if the standalone “-” character is the first cell of the first line it will be converted to a 0 even though the import would have converted it anyway. If it is the first cell in subsequent lines it will not be converted and the import will convert it automatically for you.

Thanks, Kurt, for digging into this. Yes, the spreadsheet data is not consistent, but unfortunately, that his how we receive data from our customers sometimes. We just need PanX to be able to interpret the text entry dash as a zero when importing into a Numberic (Float) formatted field.

Thank you, Gary, for your suggestion. I am not familiar with the ImportText Rearrange parameter, but I will try implementing it. Hopefully this workaround will help me get by until we get an update in PanoramaX.

I think we should rename the title of this discussion to something like “Trouble importing some spreadsheet data” since it is not specific to Excel Data in Accounting format. I don’t think I have the right permissions to make the change.

Gary’s example code allowed me to rapidly duplicate and investigate this issue. It appears to be a bug in Apple’s floating point conversion code. Fortunately it is trivial to make a special case to bypass Apple’s code in this situation, so this has been fixed for the next version. I never would have dreamed that spreadsheets would export empty numeric values as dashes.

I realized that my suggested temporary solution would not properly convert a cell containing only a dash if it was at the end of a line other than the last line. Here is an expanded version that will trap these cells as well:

importtext A,“ExistingData”,“Replace”, “REARRANGE”,replace(arrayfilter(replace(import(),lf(),tab()+lf()),tab(),{?(import()="-",“0”,import())}),tab()+lf(),lf())

What this does is temporarily convert the line feeds at the end of intermediate lines to a tab + a line feed and then reconverts back to a simple line feed at the end of filtering. I just wanted to correct this in case it is used until the real fix is available in the next Panorama X version.