Trouble importing Excel data in Accounting format


#1

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.


#2
  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.

#3

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.


#4

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.


#5

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?


#6

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.