Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Force Formatting in v14.2

Hey everyone,

I was wondering if there is a way to force formatting, of any kind, when loading an Excel template? I have some interest rates, for example, that go out three decimal places (0.000), but Monarch is loading them to the nearest character. As a result, some of the rates in this example get returned as 0.000, but other whole numbers, for example 4.000, get rounded to 4. The formatting feature does not appear to be able to bypass this configurations, and I was wondering if there was a work around for it?

Thanks,

David Rubin

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

David,

Are you looking at Data Prep Studio or Classic in relation to this question?

If DPS I assume you can modify the display format easily? If so you are right, it would be better to have a more appropriate format to start with.

Out of interest, how many rows of the Excel sheet are you using as a data sample?

(Application (Or Workspace) default settings for Excel)

Grant

Altair Forum User

David,

Are you looking at Data Prep Studio or Classic in relation to this question?

If DPS I assume you can modify the display format easily? If so you are right, it would be better to have a more appropriate format to start with.

Out of interest, how many rows of the Excel sheet are you using as a data sample?

(Application (Or Workspace) default settings for Excel)

Grant

Hey Grant,

I was referring to DPS, correct. However, using my example, the original format of the file actually is set to 0.000, but it appears DPS auto adjusts any trailing zeroes to the nearest number when it is loaded into a new workspace. For example, an interest rate that is 4.000 becomes 4 in DPS, but oddly for data integrity and delivery purposes, I would need it to maintain the trailing zeroes, i.e. 4.000. Is there a method to force formatting in DPS to achieve this desired value or similar ones for other value types?

Thanks,

David Rubin

Altair Forum User

Hey Grant,

I was referring to DPS, correct. However, using my example, the original format of the file actually is set to 0.000, but it appears DPS auto adjusts any trailing zeroes to the nearest number when it is loaded into a new workspace. For example, an interest rate that is 4.000 becomes 4 in DPS, but oddly for data integrity and delivery purposes, I would need it to maintain the trailing zeroes, i.e. 4.000. Is there a method to force formatting in DPS to achieve this desired value or similar ones for other value types?

Thanks,

David Rubin

Hi David,

You can always set the format of the field using a "Change" and then save the change list and apply it when you open that Excel spreadsheet layout. However I was wondering if there is another way via the number of sample lines. I have noticed the same behaviour, not restricted to Excel files, but have not bothered to investigate it at source so far as it was not pertinent to what I have been working on.

Having just changed the number of lines analysed and opened an XLSX file it looks like that is not a factor.

As far as I can tell DPS will recognise a field as number (rather than character or date for example) but makes no attempt to decide what sort of number and how it should be formatted.

Strange as that may seem I half suspect that there are good reasons for that though it may seem strange at first.

You might want to raise the question with Datawatch Technical Support if no one else offers anything here in the Community.

HTH.

Grant

Altair Forum User

Hi David,

You can always set the format of the field using a "Change" and then save the change list and apply it when you open that Excel spreadsheet layout. However I was wondering if there is another way via the number of sample lines. I have noticed the same behaviour, not restricted to Excel files, but have not bothered to investigate it at source so far as it was not pertinent to what I have been working on.

Having just changed the number of lines analysed and opened an XLSX file it looks like that is not a factor.

As far as I can tell DPS will recognise a field as number (rather than character or date for example) but makes no attempt to decide what sort of number and how it should be formatted.

Strange as that may seem I half suspect that there are good reasons for that though it may seem strange at first.

You might want to raise the question with Datawatch Technical Support if no one else offers anything here in the Community.

HTH.

Grant

Fair enough. Although you seemed to conclude otherwise, if you would like to know, the number of rows in this one report was 613, but they can vary in size, high or lower, depending on the report that month.

I appreciate the direction.

Thanks,

David Rubin

Altair Forum User

Fair enough. Although you seemed to conclude otherwise, if you would like to know, the number of rows in this one report was 613, but they can vary in size, high or lower, depending on the report that month.

I appreciate the direction.

Thanks,
David Rubin

Hi David,

I set the default to read 16k rows and found an 800+ row Excel file with sufficient numbers to act as a good sample and no decimals were interpreted for Whole Numbers.

Change the format with a DPS change and all is well.

Now in Classic you get to control things in a different way and normally (Excel has over the years had a few "moments" even within its own MS family!) Classic will deal with the format as Excel identifies.

However if one then sends some large numbers to DPS directly from Classic there may be odd results and I have a few situations that seemed to be difficult to revert easily when I first had experienced it. So far I have not needed to go back and work out the details (for my current main project it matters not if the values are correctly formatted at that point) so I don't claim to have a full analysis nor design intent understanding but it is just possible that keeping things simple and NOT making an assumption based on Excel format is in many cases no bad thing.

Obviously in your case that is not so .... but then a saved change list can sort out the requirement at a click ...

Alternatively open in classic and pass to DPS maybe - but it really all depends on your required workflow and the ultimate purpose of passing the Excel data through DPS (or Classic) in the first place.

It would be interesting to know the decision process that set the design for the operation. There may be something especially valid that is not immediately obvious.

Grant