4.1 fails to compare spreadsheets

[joecar]

encounters an error...

[Aaron]

Thanks for the report. In our own tests, 4.1 can open and compare Excel files and other spreadsheet files. Would it be possible to attach sample files? Or email us at [email protected] with a link back to this forum thread for our reference and a BCSupport.zip from the Help menu -> Support; Export.

[joecar]

I can't send you my xls because they contain sensitive information...

I emailed you (can you send me some sample xls's that I can try to duplicate the error on...?)

Thanks.

[Aaron]

Hello,

Replied and sent. Do all xls files cause the problem for you or only some configurations?

[sumangalam]

Hello,

I did also face error in 4.1 Beta in XLS files exported from FoxPro DBF files. FoxPro exports in Microsoft Excel 5.0/95 WorkBook Format. May be 4.1 beta is not compatible with that. Would have preferred if 4.1 beta handled those files in old way. But now some are being compared in Text Compare. Will try to replicate problem; once encounter it again.

With Regards

Sanjiv Bansal

[Aaron]

The Session type used (Text or Table) is controlled by your File Format's list in the Tools menu -> File Formats. Whichever is the topmost installed format for that extension is the session type that will be used. The white icon next to the format name represents the Text Compare, the purple table the Table Compare.

If you were to drag and drop onto the interface, and drop over a specific button (Text Compare) then we would open in that compare type.

Are you able to create/export any example files? They could be very small and contain no real information, just as long as they reproduce the conversion error for you.

[joecar]

Hello,

Replied and sent. Do all xls files cause the problem for you or only some configurations?

Hi Aaron,

I emailed you the BC4.1 BCsupport.zip and some blurred-out screenshots.

What we know so far:
BC4.0.7: compare is ok (on all my xls's).
BC4.1: compare fails with error:
- happens only when latest xls is compared to any previous xls ,
- no error when comparing any previous xls to any other previous xls.

I suspect the latest xls has some format change (which BC4.07 handles ok and BC4.1 fails to handle).

-Joe

[joecar]

Hi Aaron,

I also noticed in the screenshots I emailed to you that the xls column headings are not displayed in BC4.1 (whereas they are in BC4.0.7).

Thanks
-Joe

[Aaron]

Would it be possible to get a modified version of the problem XLS? For example, if you delete all but a couple rows, does it still have the same problem? Then if you edit the text to be random "aaa" "bbb" etc, does it still reproduce? It may be something inherent to how the file was created. It will be difficult to track this down if it is specific to a certain file.

As a workaround, you can install the older 4.0.7 conversion into BC4. You can clone the BC3 format with a new name: "MS Excel BC3", then export the BC3 format using the Tools menu -> File Formats.

[joecar]

Hi Aaron,

I emailed you sanitized xls files that cause the error in BC4.1... they compare ok in BC4.0.7.

Thanks
-Joe

[joecar]

...

As a workaround, you can install the older 4.0.7 conversion into BC4. You can clone the BC3 format with a new name: "MS Excel BC3", then export the BC3 format using the Tools menu -> File Formats.

I'm not sure how to do this...

[Aaron]

No problem. In BC3, go to the Tools menu -> File Formats, select the MS Excel format, and use the Save As button to create a copy named "MS Excel BC3".

Then in BC3 go to the Tools menu -> Export, File Formats, and export your MS Excel BC3 format.

In BC4, now use the Tools menu -> Import, and import this format. Then, in the Tools menu -> File Formats, whichever format is higher in the list associated with *.xlsx is the format used automatically when you open that file type.

[sumangalam]

I will send files; if I encounter the error again. Right now am not getting the error. In my machine there were two "MS Excel WorkBooks" formats - One was having Purple Icon, another White icon. Bringing the Purple higher in list solved the problem of Text format being preferred over Table format.

With Regards

Sanjiv Bansal

[joecar]

No problem. In BC3, go to the Tools menu -> File Formats, select the MS Excel format, and use the Save As button to create a copy named "MS Excel BC3".

Then in BC3 go to the Tools menu -> Export, File Formats, and export your MS Excel BC3 format.

In BC4, now use the Tools menu -> Import, and import this format. Then, in the Tools menu -> File Formats, whichever format is higher in the list associated with *.xlsx is the format used automatically when you open that file type.

I export from BC4.0.7 to file, and then when I try to Import into BC4.1, no MS Excel formats (existing or new) show up (i.e. so I can't checkmark them for importing)...

[Aaron]

Hello,

Thanks; that does appear to happen if using 4.0.7. If you use an install of BC3 it should work. Also, I'll include a BC3.bcpkg file as an attachment that you can download.