Altair RISE

A program to recognize and reward our most engaged community members

Nominate Yourself Now!

Trimming trailing spaces and Text instead of General in Excel?

I have Monarch 14.3.2.7917 and Automator 14.3.2.22815

How can I get Monarch to NOT trim trailing spaces from a field? I have the Options>Input>Text Files - "Trim leading and trailing spaces from Character and Memo fields" option unchecked. The file is a flat file with all records with same layout and using a Standard Trap in Report Design. I have a 100 byte field "Variable Coding". I am not getting the trailing spaces. I determined this by creating a formula field with the following formula: Len([Variable Coding]). I should always get 100 from the formula if the option is unchecked - I don't. Some times I get 100 if there is a non-blank character in the last position - other times I do not. I get 100 and 59 for instance. When I check the option to remove leading and trailing spaces I get 99 and 58 since I have a leading space on the field as well as lots of trailing spaces - some of the time.
How can I get Monarch to generate a field/column type in Excel of "Text" instead of "General"? I have a field that is mostly numerics. It has an alpha in position 2 of the field occasionally. When that alpha is an "E" Excel displays the field as an exponential number instead of a character string when the column type is "General". If I change the column type to "Text" Excel displays the field correctly.

Sorry if this is a noob question. Hope I provided enough info about my problem and what I tried to work around the problems.

Find more posts tagged with

English

Monarch

Accepted answers

All comments

Altair Forum User

I tested your first question in v14 and v15 and both behaved as you described. I did see a support ticket from 2 years ago that said that is normal behavior and the work around was to change the field type to memo instead of character. That did not work for me. I will check with support this week to see if there is a better work around or explanation.

For your 2nd question, I am going to assume that the field type is already set to character. I haven't tested this, but I would suggest creating another formula that prepends a single quote (') to your original field and then use the new field for the export. In the excel world, the leading single quote tells excel that what follows should be treated as text.

Altair Forum User

I tested your first question in v14 and v15 and both behaved as you described. I did see a support ticket from 2 years ago that said that is normal behavior and the work around was to change the field type to memo instead of character. That did not work for me. I will check with support this week to see if there is a better work around or explanation.

For your 2nd question, I am going to assume that the field type is already set to character. I haven't tested this, but I would suggest creating another formula that prepends a single quote (') to your original field and then use the new field for the export. In the excel world, the leading single quote tells excel that what follows should be treated as text.

1. Memo didn’t work for me either. Haven’t tried to surround the field with quote. Will try that today.

2. Yes the field is already a Character field. Will use the single quote idea. My user noticed this on another character field: ‘On the Stat tab, I had the same thing happen, but the field was “ 0 “ – it changed to just “0”’ - which almost sounds like a combination of the two. Dropping the trailing spaces and then Excel’s default behavior on a “General” field - making a field numeric instead of leaving it alone.

Robert Phoenix

Application Developer II

Altair Forum User

1. Memo didn’t work for me either. Haven’t tried to surround the field with quote. Will try that today.
2. Yes the field is already a Character field. Will use the single quote idea. My user noticed this on another character field: ‘On the Stat tab, I had the same thing happen, but the field was “ 0 “ – it changed to just “0”’ - which almost sounds like a combination of the two. Dropping the trailing spaces and then Excel’s default behavior on a “General” field - making a field numeric instead of leaving it alone.

Robert Phoenix
Application Developer II

Depending on why you need the trailing spaces, there are a couple of work arounds:

1. Export the table as fixed text. That will pad all text fields to the length of the field with spaces….or

2. Create a new calculated field that appends spaces and then take the left x number of characters needed:

left(Original+space(50),41)

i. This adds 50 spaces to the field “Original”, and then takes the left 41 characters to give me a string of 41 characters.

Altair Forum User

Depending on why you need the trailing spaces, there are a couple of work arounds:

1. Export the table as fixed text. That will pad all text fields to the length of the field with spaces….or
2. Create a new calculated field that appends spaces and then take the left x number of characters needed:

left(Original+space(50),41)

i. This adds 50 spaces to the field “Original”, and then takes the left 41 characters to give me a string of 41 characters.

Need leading and trailing spaces on fields because not every field/column in spreadsheet produced is a single field. The format of the spreadsheet was done long before I started with the company. A single field may actually be 10 separate fields for instance but that field isn't always the same composition on each record. So on one record it might be 10 fields but on another it might only be 8 for instance. So the users want to see the leading and trailing spaces on the fields so they can filter, sort and search and include them. Currently this spreadsheet is built manually from about 8 files into 8 separate tabs where 2 thru 6 at most have data at any one time in addition to the above variations on the fields contained in the tabs. I am trying to automate this to eliminate the grunt work of building it.

Altair Forum User

Need leading and trailing spaces on fields because not every field/column in spreadsheet produced is a single field. The format of the spreadsheet was done long before I started with the company. A single field may actually be 10 separate fields for instance but that field isn't always the same composition on each record. So on one record it might be 10 fields but on another it might only be 8 for instance. So the users want to see the leading and trailing spaces on the fields so they can filter, sort and search and include them. Currently this spreadsheet is built manually from about 8 files into 8 separate tabs where 2 thru 6 at most have data at any one time in addition to the above variations on the fields contained in the tabs. I am trying to automate this to eliminate the grunt work of building it.

Bob,

Are the 10 separate fields all in the same row in the original file? Or are they in a column but treated as a single field on extraction?

If in a single row, does each reported field always start at the same position in the row? In other words each field has its own column and the content of the field is always the same type of data IF it exists but it may be empty?

My initial take is that I would be tempted to suggest improving the spreadsheet at the same time since it sounds like the design may have been based on some old constraints. But either way if you have a way to extract the fields easily and separately it gives you far greater control and probably much easier automation options.

If the Fixed Text concept comes into play at all bear in mind that what you actually see on screen (or potentially in print) depends on the font being used. A fixed size font makes it easier to see what is going on than a proportional font which will tend to compress space 'characters'.

In your original you mention something about a noob question.

If you are new to Monarch this is not the simplest area of functional need to be cutting you teeth on and there can be a number of odd things appear to happen, especially working with Excel and more especially with different versions of Excel. Sometimes that extends to the idea of exporting to Access and then letting Access and Excel sort out between themselves how they want to work with the resulting data!

HTH.

Grant

Altair Forum User

Bob,

Are the 10 separate fields all in the same row in the original file? Or are they in a column but treated as a single field on extraction?

If in a single row, does each reported field always start at the same position in the row? In other words each field has its own column and the content of the field is always the same type of data IF it exists but it may be empty?

My initial take is that I would be tempted to suggest improving the spreadsheet at the same time since it sounds like the design may have been based on some old constraints. But either way if you have a way to extract the fields easily and separately it gives you far greater control and probably much easier automation options.

If the Fixed Text concept comes into play at all bear in mind that what you actually see on screen (or potentially in print) depends on the font being used. A fixed size font makes it easier to see what is going on than a proportional font which will tend to compress space 'characters'.

In your original you mention something about a noob question.

If you are new to Monarch this is not the simplest area of functional need to be cutting you teeth on and there can be a number of odd things appear to happen, especially working with Excel and more especially with different versions of Excel. Sometimes that extends to the idea of exporting to Access and then letting Access and Excel sort out between themselves how they want to work with the resulting data!

HTH.

Grant

It is my understanding that the combined fields can vary what is inside based on the record. So record 1 the first 11 positions might be a numeric field but record 2 the first 30 is a text field containing a name or address for instance.

I would love to improve the spreadsheet but it has existed for a vary long time before I joined the company and the only real change that is open to the users and peers right now is changing the column names to be "Transaction Type" instead of T T for instance. I'm not sure I'm going to be allowed to change it even that far for the first go around.

I did get approval to go ahead with the conversion and not work about the trailing spaces or the field changing to an exponential number. The single leading quote to keep the field a text string was nixed. Users don't like that.

Exporting to Access isn't an option I can do. Not and automate it since the Automator server does not have access to Access currently and expenditures will not happen. I was even asked about outsourcing all of our processes.

Altair Forum User

It is my understanding that the combined fields can vary what is inside based on the record. So record 1 the first 11 positions might be a numeric field but record 2 the first 30 is a text field containing a name or address for instance.

I would love to improve the spreadsheet but it has existed for a vary long time before I joined the company and the only real change that is open to the users and peers right now is changing the column names to be "Transaction Type" instead of T T for instance. I'm not sure I'm going to be allowed to change it even that far for the first go around.

I did get approval to go ahead with the conversion and not work about the trailing spaces or the field changing to an exponential number. The single leading quote to keep the field a text string was nixed. Users don't like that.

Exporting to Access isn't an option I can do. Not and automate it since the Automator server does not have access to Access currently and expenditures will not happen. I was even asked about outsourcing all of our processes.

Hi Bob,

Sounds like your options are very limited.

If the fields can be anything in any order but originate in a columnar format and, if lucky, have some sort of field type identification associated with them, then there can be possibilities to create a dedicated field for each possible field "type".

Sight unseen I would also guess that the same might be possible across a row but would probably be more complicated to deliver with absolute certainty of 100%. (More from the possibility of random undocumented anomalies than anything else.)

As you have automator one option for an approach could be to break out the components of the report using different models to make the modelling simpler and then re-combine the results as part of the automation before export. The users need not see any of that backroom work. Doing that can make certain aspects of modelling or datat extraction and preparation somewhat easier to create and, of course, to maintain in the future.

If you have access to a copy of a version of the source document with dummy data I would be happy to take a look to see if I can come up with any more specific suggestions. It often helps, I find, to see the nature of the problem from a report structure angle - just a visual starting point.

Grant