asked on

How to insert a Clrf in query to remove trailing space upon data export

Hi Everyone,

I have a select query that takes a selective amount of fields from a table and then sorts them. I then use the results of this query to export to a text file that has specifications setup, in total the file can be 15,188 bytes in length, if all the fields are filled in, which they are not. What I have noticed, is that when the text file gets exported, there is a lot of trailing white space that makes the text file super large. I have a 3rd party program that can remove the trailing white space; however, I was wondering if there is a way that I can take care of this within access, and specifically within this query or another query if need be.

Any help on this would be appreciated.

-Anthony

Paul Cook-Giles

Anthony, could you post your code, please, and the text of your query?

Anthony

ASKER

Hi Paul,

Here is the SQL of my query...

SELECT STEP_LAYOUT.[Control Plan], STEP_LAYOUT.[Subscriber SUB Num], STEP_LAYOUT.[Senior Care Suffix], STEP_LAYOUT.[Sub Extent Number], STEP_LAYOUT.[To Process Type], STEP_LAYOUT.[To Group Number], STEP_LAYOUT.[To Section Number], STEP_LAYOUT.[Action Code], STEP_LAYOUT.[Sub Effective Date], STEP_LAYOUT.[Sub Cancel Date], STEP_LAYOUT.[Package Code], STEP_LAYOUT.[SUB Last Name], STEP_LAYOUT.[SUB First Name], STEP_LAYOUT.[SUB MI], STEP_LAYOUT.[SUB SSN], STEP_LAYOUT.[Bill Relation Code], STEP_LAYOUT.[SUB Sex Code], STEP_LAYOUT.[SUB DOB], STEP_LAYOUT.[Street Address 1], STEP_LAYOUT.[Street Address 2], STEP_LAYOUT.City, STEP_LAYOUT.State, STEP_LAYOUT.[Zip Code], STEP_LAYOUT.Filler1, STEP_LAYOUT.HIRE, STEP_LAYOUT.Filler2, STEP_LAYOUT.SubPhone, STEP_LAYOUT.Filler3, STEP_LAYOUT.DepCount, STEP_LAYOUT.[1DepActionCode], STEP_LAYOUT.[1DepEffDate], STEP_LAYOUT.[1DepTermDate], STEP_LAYOUT.[1DepFirstName], STEP_LAYOUT.[1DepMidInt], STEP_LAYOUT.[1DepLastName], STEP_LAYOUT.[1DepSSN], STEP_LAYOUT.[1DepStatus], STEP_LAYOUT.[1DepMultiBirth], STEP_LAYOUT.[1DepSexRel], STEP_LAYOUT.[1DepDOB], STEP_LAYOUT.[1DepFiller], STEP_LAYOUT.[2DepActionCode], STEP_LAYOUT.[2DepEffDate], STEP_LAYOUT.[2DepTermDate], STEP_LAYOUT.[2DepFirstName], STEP_LAYOUT.[2DepMidInt], STEP_LAYOUT.[2DepLastName], STEP_LAYOUT.[2DepSSN], STEP_LAYOUT.[2DepStatus], STEP_LAYOUT.[2DepMultiBirth], STEP_LAYOUT.[2DepSexRel], STEP_LAYOUT.[2DepDOB], STEP_LAYOUT.[2DepFiller], 
FROM STEP_LAYOUT
ORDER BY STEP_LAYOUT.REC_ID, STEP_LAYOUT.[Subscriber SUB Num];

Open in new window

I don't have any other code to post. The program is third party and does the trim within it's own program.

crystal (strive4peace) - Microsoft MVP, Access

in the query definition, for each field that may have trailing spaces:

 field -->  MyFieldname_: trim([MyFieldname])

Open in new window

or, in SQL: trim([MyFieldname]) as MyFieldname_

only do this for text fields. If you have errors running, post back -- the equation can be made more elaborate.

The reason that
_
is added to the end of the fieldname is to make the calculated fieldname different ... you can choose any name you like.

Paul Cook-Giles

Is the trailing white space at the end of each row, or below the rows? If it's at the end of each row, Crystal's solution should work.
if it's at the bottom of the export, check your query to be sure that it's not returning blank rows.

Anthony

ASKER

It is at the end of each row.

I will try his suggestion and see how that works out.

Thanks.

crystal (strive4peace) - Microsoft MVP, Access

realise the the TRIM function will remove trailing spaces ... so if the reason for the multiple lines is just spaces, this will do the trick. If however, the reason for the extra lines turns out to be characters that define the next line, than another function will also be needed. It may be wrapping trim around a replace such as this:

 trim(Replace( ([MyFieldname], chr(10), " ") )
OR
 trim(Replace( ([MyFieldname], chr(13) & chr(10), " ") )

Open in new window

test the data to find out if 10 or 13+10 (or something else) is needed.

Anthony

ASKER

Hi, so I tried your suggestion, and it didn't remove the trailing spaces. The file is still the same size as before...

Paul Cook-Giles

Anthony, could you post a sample of the output file? It wouldn't need to be all of the rows, just fifteen or twenty that have the trailing white space.

Anthony

ASKER

Hi Paul,

Unfortunately, I cannot post a sample of the output. It is very sensitive material that I can't really expose to other people. If there isn't a different solution that can be looked at, I'll have to continue with my current process.

What is appears to me is that access space fills the remaining fields because the entire file length is 15,188. If only 30 of the 144 fields are filled in, the export of the file is then just space filling. This is what I'm assuming considering the size of the file upon exportation.

Paul Cook-Giles

OK. :) No stress. I've copied and slightly tweaked the start of your second paragraph:

What is appears to me is that access space-fills the remaining fields because the entire row width is 15,188. If only 30 of the 144 fields are populated, the rest of the row is just space characters.

Is that an accurate restating of your situation?
Is the row solid text (that is, no spaces) up to the point the trailing empty spaces begin?

If the answers are both yes, I think I can build you some VBA code that would remove the spaces from the row ends.

Anthony

ASKER

Thanks Paul.

Yes, you are correct in your reiteration.

No, the row is not just solid text. For some individuals, the row's values might end with a numeric value, space, or 0 filled space.

-Anthony

John Tsioumpris

How about using default values for the fields that are note filled like ...when empty --> "N.A"

Paul Cook-Giles

Hm. The second question was a bit ambiguous. Let me try again:

Are all the rows solid alpha-numeric text (that is, no spaces) up to the point the trailing empty spaces begin? I'm going to put code in a second post that will removed any spaces from the contents of a text file, so if that's what you want, you've got it.

If the rows are not solid alpha-numeric text (that is, there are valid spaces between other data elements) up to the point the trailing empty spaces begin, would there ever be two (or more) valid spaces next to each other?

Paul Cook-Giles

Code to remove all spaces from the content of a text file:

Dim intFreeFile As Integer, strTempString As String, strPathAndFileName  as string
strPathAndFileName = "C:\Test\TestSpaceRemoval.txt"

intFreeFile = FreeFile
Open strPathAndFileName For Input As #intFreeFile
  strTempString = Input(LOF(intFreeFile), #intFreeFile)
Close #intFreeFile

Do While InStr(1, strTempString, " ") > 0
  strTempString = Replace(strTempString, " ", "")
Loop

intFreeFile = FreeFile

strPathAndFileName = Replace(strPathAndFileName, ".txt", "2.txt")
Open strPathAndFileName For Output As #intFreeFile
  Print #intFreeFile, strTempString
Close #intFreeFile

Open in new window

Replace "C:\Test\TestSpaceRemoval.txt" with the path and file name of your file. The first time you run this, you'll find a second file, with a "2" added to the end of the file name, in the same folder. Check the results, and confirm that you're getting what you expect. If you are, comment out the "Replace" line of code (line 15) by placing an apostrophe at the beginning of the line. The next time the code runs, your original file will be replaced with the space-removed version.

Anthony

ASKER

Hi John, thanks for your suggestion, but believe you solution would result in the N.A value actually being there... unless I'm misunderstanding you...

Paul, yes there are valid spaces and yes there would spaces next to each other. I have fillers within this that have a specific number of spaces and 0's at specific points within the data file. I believe the code you provided is essentially what the program that I have currently does for me.

Paul Cook-Giles

Well, damn.

Am I correct in thinking that the trailing spaces you want to remove begin immediate after the last non-space character? If that's the case, I think my code could be tweaked to work....

Anthony

ASKER

Ha, I know very tricky.

Yes, you are correct. I want all trailing spaces removed after the last non-space character.

Paul Cook-Giles

cool. Lemme take a swing at modifying the code, and see what I can come up with.

ASKER CERTIFIED SOLUTION

Paul Cook-Giles

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony

ASKER

Thanks Paul, this did work.

Much appreciated.

-Anthony