Ledger Export to Excel or CVS incorrect number for

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.

The issue is this:
Softrak is saying it's a Microsoft issue.
I'm saying it's a softrak import/export issue because since the option to export to dbf was removed in April of 2008, we have used the export to excel with no issues and that includes version 8 of ledger, version 9.0A, 9.0B, and 9.0C it worked. The service pack that came out on 100511, that I noticed it also fixed a couple of import/export issues for the 9.0C release from April.

We already pay for the full Softrak Support and upgrade plan so I don't think you could help, but thanks for the offer.

I agree that the tug of war between developers can be annoying, sometimes they are right and sometimes I've proved them wrong.

Sometimes it just takes a fresh set of idependent eyes to see the real problem and get it resolved. There are very few people at Softrak who have more experience with Adagio than I do. Also getting your hands dirty by actually working directly with the data and import file may reveal more than what comes out in this forum.

Thanks for that Doulas,
I know what you mean, after 22 years in IT including 5 years as second tier support for a software development company; I have been on both sides of the telephone receiver in cases like this. Since Softrak want to say it’s an Excel problem, that’s fine with me. Myself and a programmer have determined how to make the changes to our processes to get this to work.

The only way that is going to work (Other than enabling dbase import/exports in the ledger.ini file, because we view this as a step backwards, and don’t want to do an upgrade to find it’s been removed again), is to do the following:
1. Change all our export templates to export to CVS delimited and use the txt file extension.
2. If the file requires editing in excel this will force the excel import wizard to process the files.
3. We can then set the formatting to any column we need to txt (if a cvs extension is used excel 2007 will not run the import wizard).
The next steps are one-time steps that cost us allot of money (we went through this when dbase was removed as a file type).
4. We have 7 or 8 programs that will need to be rewritten to take the txt file and converts it to dbase.
5. Then we have to test each program to the satisfaction of the CFO and compliance officer.
6. Then we have to update all the effected procedures that user are required to follow.
And this does not cover the cost of myself and a programmer working for 3 days on nothing but this issue.

For your offer of fresh eyes on the data/templates, nothing in the data or the templates have changed (other than we’re up to about 31300 accounts). Once the templates were updated 2 years ago we have had no issues with using excel export. Everything worked fine at the end of April. No Windows or Office patches were applied in May (because they all have to be tested before deployment). The only change in the system was the installation of Ledger 9.0C and the service pack 100511.

So on Monday when I outline the changes we’ll need to make to the Finance Department and compliance officer, I will be surprised if I’m not asked the question “Are we getting value from this product based on what we’re paying for it?” While it’s not the worst product I have to support, and in the past Softrak has been helpful, I get a bit testy when the first thing I’m told is “Not our fault, it’s that other company”.

Thanks for your help.

Hi Jeffery,

To answer your original question - yes, something did change in the underlying method for importing or exporting records in Adagio Ledger 9.0C. Indeed when exporting accounts or other records using version 9.0B, a value of 21E083 did not get converted by Excel to 2.1E+84, but it does now when using Adagio Ledger 9.0C. Because of that, it is worthwhile for Softrak to see if there is anything in the formatting of string fields that can be modified to correct this when using Export.

Indeed Softrak is not solely to blame for the interpretation of the field value 21E083 into a Scientific format. This is because if you type this into a blank Excel spreadsheet, Excel changes the format - unless you type a leading apostrophe with the value, ie '21E083. This needs to be mentioned as part of the explanation of the problem.

If you use the Excel Direct button either directly from the Edit Accounts screen or from the SmartFinder using Adagio Ledger 9.0C, I can confirm that the department code does not get re-formatted to a scientific value but instead maintains the correct value.

I can also confirm that using Adagio GridView to create an inquiry on the Ledger account master file and exporting to Excel also maintains the correct value for departments. Perhaps using GridView to create (and even automate) the account exports can be of even greater value, because you wouldn't have to run each export manually.

and to finish...

Excel is quite capable of writing a DBF file, and you could also record a macro in Excel to automate the process. I don't understand why you need to hire a programmer to convert your TXT file to DBF. If you're gong to hire a progammer, why not have him read the CSV directly?

The reason we are pointing at Excel is the the CSV file is correctly formatted and it is Excel that has the problem when you read the data to change it. Have you changed the version of Excel you are using? If you made the necessary change in Notepad there wouldn't be a problem.

Enjoy the weekend.