Using version 9.0C, we are doing our month end and have found a major issue with exporting accounts. When we open the excel or cvs file after exporting we are receiving incorrecttly formatted department codes. Need this looked at ASAP. Thanks


1520 11B02M SURPLUS RECEIVABL (LOSSES) B 0 A 0
1520 11B0VS SURPLUS RECEIVABL (LOSSES) B 0 A 0
1520 1.10E+29 SURPLUS RECEIVABL (LOSSES) B 0 A -877.2
1520 1.10E+36 SURPLUS RECEIVABL (LOSSES) B 0 A -8090.24
1520 11E03K SURPLUS RECEIVABL (LOSSES) B 0 A 2679.59
1520 1.10E+44 SURPLUS RECEIVABL (LOSSES) B 0 A 0.01
1520 1.10E+46 SURPLUS RECEIVABL (LOSSES) B 0 A -11668.91
1520 1.10E+54 SURPLUS RECEIVABL (LOSSES) B 0 A 6032.76
1520 1.10E+62 SURPLUS RECEIVABL (LOSSES) B 0 A 6286.56

How did you produce the data you show.

If it is from File | Export | Accounts have a look at your definition and make sure "Separtor" is not blank.

OK, yes it is blank. What need to go in there? Thanks

It's not even allowing me to click in to the seperator box

Hi Jeffrey,

If the export format is Excel, there is no access to the separator as it isn't used. Blank is valid for Excel exporting. Only if the CSV option is selected will the separator be available.

Can you give an example of a Department code value that you wanted to export that is giving a strange result? I have seen some values get re-interpreted by Excel because Excel thinks it knows what the value should be and is actually wrong.

If you try exporting to CSV, try editing the exported file with Notepad or Word instead of Excel. That way, you could determine if the Export process is getting the Dept value correctly and Excel is mis-formatting the value, or if the raw export value is wrong.

We have tried both excel and csv formats with the same results. I am trying to get the info from the financial. With this our program in foxpro does not reconise the file as excel.

Here are some of the accounts that are excel does not understand.
1515 21B0X1 CODED EXCES RECEIVABL (LOSSES)
1515 21E082 CODED EXCES RECEIVABL (LOSSES)
1515 21E0A2 CODED EXCES RECEIVABL (LOSSES)
1515 21E0G3 CODED EXCES RECEIVABL (LOSSES)
1515 21E0V3 CODED EXCES RECEIVABL (LOSSES)
1517 21B04B HIGH EXCESS RECEIVABLE (LOSSES

Hi Jeffrey,

Just to confirm, but if you choose to Export Accounts from Adagio to a CSV file (with a separator of comma), then edit the created CSV file with notepad, the Dept code is correct. But if you open the same file with Excel, the depts above get converted to a different value? And you get similar errors if you Export the accounts directly to an Excel format?

If I export to a CSV file and open with Notepad, the contents are correct. When opening the CSV file with Excel 2007, or exporting directly to Excel, I saw the dept 21E082 get changed to 2.1E+83. That is because Excel interprets the value '21E082' as the scientific exponential format of a number 21 E 82, or 2.1 E 83.

This is an Excel issue, not Adagio. Try opening a blank Excel sheet and type 21E082 into a cell - it will get changed to 2.1E+83.

Unfortunately, your coding for some departments with a number followed by E followed by another number is not Excel-friendly. You need to avoid Excel and do everything strictly with CSV.

Excel is interpreting these department codes as containing exponential numerical values when it opens the text file. Use the wizard to force it to treat them as text, or export the data as CSV and don't look at it using Excel. Use Noteped or some other editor.

Hello,
Yes you are correct that excel has an issue with number like 21E0000. I’ve also had a discussion with you in Apr. 2008 about this issue with the financial reported. Andrew help us create a template that would extract the department codes as text and then we would open the file in excel. Since the time you removed the option to export to dbf we have used the export to excel option without an issue. We have used this option using both Office 2003 and Office 2007.

Our month end process exports 4 or 5 exports of accounting info that we import to our Insurance System. From our Insurance System we export 7 or 8 files that we import in to the Ledger program. Once we had the templates set-up and working we had no issues.

Next we updated to version 9.0C of the ledger and ran our standard testing. This includes testing importing and exporting and this worked. (but I did notice the report issue with the billion dollar batches losing the first number did return). I next applied the 100511 service pack to Ledger and while I noticed the billion dollar issue was resolved, I may not have looked far enough down to locate the excel numbering issue. I’m sure it was not there after applying 9.0C.

The work around I preformed yesterday to get a proper set of data in to excel was the following:

1. From Financial Reporter | opened the above smart sheet template.
2. Edited it to capture the required data.
3. Did the Excel Direct.
4. Once completed and the data was opened in excel 2003, I reformatted the negative number from (11100.00) to -11100.00
5. Since we have over 30,000 accounts (this is a requirement in property insurance), I cut rows 16,000 to 31,500 to and pasted in to a new excel file.
6. I then saved both excel files in to version 5.0 or Excel 95 and this saved and read the department codes correctly.
7. I closed and reopened both spreadsheet in excel 2007 was able to combined these two files in to one and save and excel keep the correct number formats.


Today tried the following:
1. Did an export to csv.
2. Opened in notepad.
3. Closed and opened in excel 2007
4. Did the cvs import and set the department code to text.
5. Everything looks fine, save the file into excel 2003 format.
6. We then run a foxpro program on the file and receive an error “file is not a recognised Microsoft excel file”
7. If I perform steps 5 to 7 from above ie saving to excel 95 then the foxpro can read the file.

I seem to recall reading or speaking to someone who told me that you used excel 2.0 (maybe 4.0) because that was the most readable spreadsheet and why you were using the earlier version in your import/export function.

So to recap:
Import issues:
For the new version of ledger we had to change the date formats and found that the new version does not read the excel file in the same way before a file that showed a value but actually contained a link to another spreadsheet which imported correctly on April Month end but failed after the 9.0C and the service pack were installed.

Exporting:
Foxpro no longer can read a file exported to excel and some department codes being read as scientific exponential.

If I had a dollar to wager I would say something in the import/export of adagio has changed. Was the version of excel you use upgraded?

For Andrew:
When I open export and highlight the department code, it is set to string and will not let me change it even if I log on as the sys account.

Jeffery:

Please keep in mind that with the exception of Softrak Support all of the experts who respond on this forum volunteer their time.

When posting you should restrict your posts to a single issue that can be described in a few short paragraphs.

This issue would be better handled by a chargable consulting session with your Adagio dealer. As we are your Adagio dealer please give us a call if you would like to have us look at this issue further. My number is 604 534 4344 x223.