Using TRIM with INSERT w/ Text File Creation

Report · May 08, 2008

Good morning.

I have a simple form we use w/access dbase. The fields are inserted as is, the way it is typed in and works just fine. I also output so they can view what it was they just added. (Customer info)
The next step is to take the new records and create a TXT file to email. This also works. I build the array first from my query; I then build the .txt file with my array and email it, and also output it to screen.

The output to screen is what I need to modify in my TXT file. As per our vendor, each field has to have x number of characters. Those values are set in the form. IE: name is 32 chars, Address, 32 chars, etc.

An example of what I do for my CFOUTPUT is as follows

As you can see, the NAME field must contain 32 characters for the file and I'm just looping to add spaces to make up the difference. (If anyone else has an idea I'm open to it).

What I want to do is when the record is added to the database, I want to make sure each field contains the required number of spaces.

can someone suggest a way to do this in my insert?

TIA
Terry

Report · May 08, 2008

I did not look at your code in detail, but it sounds like you maybe
working too hard.

If you set the database fields to be a fixed width character fields then
all the padding and spacing will be handled for you. No need to loop or
count. This is a usually a 'char' type in most common database
management programs.

If you do not want to or are unable to do it in the database you can the
use the xJustify() functions [lJustify()|rJustify()|cJustify()] to
create a string of a specific length.

Finally to display this type of data you need to override the HTML
specification defined behavior to collapse all consecutive white space.
The most straight forward way to do this is the HTML tags designed for
the purpose, the <pre> and <code> tags. Otherwise you can do a simple
string replace on the fixed length space characters with the ' '
HTML Non-Breaking-SPace entity.

HTH
Ian

Report · May 08, 2008

Instead of looping do a string concatenation and a trim.

<cfset NonBreakingSpace=" >
<cfset Spaces=RepeatString(" ",50)>

<td>
<span class="style47">


#Replace(Right(Spaces & Trim(ccpReports.ccpAddress2),32)," ",NonBreakingSpace)#
</span>
</td>

If you do not want to do that you need to correct your looping statement to this:

<cfloop index="space" from="1" to="#32 - LEN(TRIM(ccpReports.ccpAddress2))#">

Report · May 08, 2008

How would I incorporate that into my insert statement?

Report · May 08, 2008

All I saw in your message was the output to the screen. You can insert with, or without, the spaces. Trim() before the insert or after. You can also append, or prepend, spaces to your data before inserting.

So there are several options. Or I am not entirely clear on your question.

Report · May 08, 2008

It's my inexperience with CF that makes me post vague questions. The output, w/your correction looks the way it should be sent in the TXT file.

Now that it displays on the screen with proper character count, I need the same correct spacing in the TXT file.

That's where I'm getting backwards on it.

Report · May 08, 2008

Ian was correct when he said you were doing too much work. Your plan for storing trailing spaces in your db has more disadvantages than advantages.

If I was doing this, I would store in my db whatever the user gave me, as you said you were doing. For the text file, I would do this.
1. run the query
2. create a new file with cffile.
3. Loop through the query. In that loop, use querysetcell to set your trailing spaces, and then use cffile to append a new row to your file.

No arrays necessary.

Report · May 08, 2008

I like easier than harder. Let me give that a crack.