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

Anthony6890
Anthony6890 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

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

Author

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], 
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, AccessRemote Training and Programming
Top Expert 2015

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Paul Cook-GilesSenior Application Developer

Commented:
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.

Author

Commented:
It is at the end of each row.

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

Thanks.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
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.

Author

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 Developer

Commented:
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.

Author

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 Developer

Commented:
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.

Author

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.  

-Anthony
John TsioumprisSoftware & Systems Engineer

Commented:
How about using default values for the fields that are note filled like ...when empty --> "N.A"
Paul Cook-GilesSenior Application Developer

Commented:
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 Developer

Commented:
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.

Author

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 Developer

Commented:
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....

Author

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 Developer

Commented:
cool.  Lemme take a swing at modifying the code, and see what I can come up with.
Senior Application Developer
Commented:
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)
readFile.Close

'# 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
Next

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

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

End Sub

Open in new window

Author

Commented:
Thanks Paul, this did work.  

Much appreciated.

-Anthony

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial