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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesSenior Application DeveloperCommented:
Anthony, could you post your code, please, and the text of your query?
Anthony6890Author Commented:
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], 

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, AccessRemote Training and ProgrammingCommented:
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.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Paul Cook-GilesSenior Application DeveloperCommented:
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.
Anthony6890Author Commented:
It is at the end of each row.

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

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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), " ") )
 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.
Anthony6890Author Commented:
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-GilesSenior Application DeveloperCommented:
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.
Anthony6890Author Commented:
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-GilesSenior Application DeveloperCommented:
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.
Anthony6890Author Commented:
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.  

John TsioumprisSoftware & Systems EngineerCommented:
How about using default values for the fields that are note filled like ...when empty --> "N.A"
Paul Cook-GilesSenior Application DeveloperCommented:
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-GilesSenior Application DeveloperCommented:
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, " ", "")

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.
Anthony6890Author Commented:
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-GilesSenior Application DeveloperCommented:
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....
Anthony6890Author Commented:
Ha, I know very tricky.

Yes, you are correct.  I want all trailing spaces removed after the last non-space character.
Paul Cook-GilesSenior Application DeveloperCommented:
cool.  Lemme take a swing at modifying the code, and see what I can come up with.
Paul Cook-GilesSenior Application DeveloperCommented:
Arrrgh!  I found the solution here on EE, but neglected to grab the URL before closing the window.  If anyone can find the OP so credit can be given, that'd be much appreciated.  I inserted line  23  ("repLine(l) = Trim(ln)") and commented out lines 24 & 25.  Other than that, the code is unchanged.

Anthony, the new line uses Access' Trim function (which removed leading and trailing spaces) to replace each line with it's trailing spaces removed.  I didn't modify it for compactness;  if you want to clone my code posted yesterday for a structure that will let you pass the file name into the code.  Please give this a whirl and let me know if it works.  :)

Public Sub FindLines()
'Declare ALL of your variables :)
Const ForReading = 1    '
Const fileToRead As String = "C:\Test\TestSpaceRemovalText.txt"  ' the path of the file to read
Const fileToWrite As String = "C:\Test\TestSpaceRemovalText2.txt"  ' the path of a new file
Dim FSO As Object
Dim readFile As Object  'the file you will READ
Dim writeFile As Object 'the file you will CREATE
Dim repLine As Variant   'the array of lines you will WRITE
Dim ln As Variant
Dim l As Long

Set FSO = CreateObject("Scripting.FileSystemObject")
Set readFile = FSO.OpenTextFile(fileToRead, ForReading, False)
Set writeFile = FSO.CreateTextFile(fileToWrite, True, False)

'# Read entire file into an array & close it
repLine = Split(readFile.ReadAll, vbNewLine)

'# iterate the array and do the replacement line by line
For Each ln In repLine
repLine(l) = Trim(ln)
'    ln = IIf(InStr(1, ln, "ant", vbTextCompare) > 0, Replace(ln, "t", "wow"), ln)
'    repLine(l) = ln
    l = l + 1

'# Write to the array items to the file
writeFile.Write Join(repLine, vbNewLine)

'# clean up
Set readFile = Nothing
Set writeFile = Nothing
Set FSO = Nothing

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony6890Author Commented:
Thanks Paul, this did work.  

Much appreciated.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.