We help IT Professionals succeed at work.

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

Anthony6890
Anthony6890 asked
on
89 Views
Last Modified: 2017-03-27
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

Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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.
Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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 TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

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

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

Commented:
cool.  Lemme take a swing at modifying the code, and see what I can come up with.
Senior Application Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thanks Paul, this did work.  

Much appreciated.

-Anthony

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions