RE: XLParser treats text fields as numeric fields

Yep, un-typed data bad :-)

Might be a good idea to put the data into MS Access instead of Excel.

Then the data can be fully types and converted correctly when it's read back.

Regards,
Richard Schoen
RJS Software Systems Inc.
Where Information Meets Innovation
Document Management, Workflow, Report Delivery, Forms and Business Intelligence
Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com
Tel: (952) 736-5800
Fax: (952) 736-5801
Toll Free: (888) RJSSOFT
------------------------------

message: 6
date: Tue, 15 Jan 2013 21:13:22 -0500
from: John Yeung <gallium.arsenide@xxxxxxxxx>
subject: Re: XLParser treats text fields as numeric fields

On Tue, Jan 15, 2013 at 6:49 PM, Richard Schoen <richard@xxxxxxxxxxxxxxx> wrote:

Unfortunately this is common with Excel.

It's common with data sources that do not have explicit data types
(such as CSV), which are then loaded into Excel, because Excel
aggressively tries to convert anything it can into numeric data. But
if the data is saved in a native Excel file, and *Excel* says it's
text, then it's text.

Usually the single quote resolves the problem, but the Excel formatting may also.

He didn't say single quotes didn't work; he said that using them
wasn't an option. This would make sense, as single quotes are only a
way *within the Excel program* to manually force cells to be text.
This isn't practical if a lot of data needs to be fixed or if the
processing has to be fully automated. Injecting single quotes
programmatically into a data file will either be ineffective or will
introduce unwanted data (namely, the single quotes themselves).

If you need to do some formatting directly with Excel, our RPG2SQL
Integrator functionality actually uses Excel to format the data and can
be integrated to any RPG application.

Excel is the single worst offender when it comes to misinterpreting
text as numeric data. If what he's doing now isn't working, then
introducing Excel's own parser isn't going to help.

John