Find Blank Spaces in Fields in a table

[JohnLee]

Good day folks,

I'm hoping someone might be able to help me here, I have a number of tables with a number of fields [excuse the generalising]. The problem I have is that in some of the fields in some of these tables a blank space appears causes another application that uses these tables to crash out.

So what I need to do is to have some code that will look for spaces in any of the fields that I specify and check for spaces either at the beginning of the text or at the end of the text and if there is a space to remove that space.

Any assistance would be most appreciated.

John

 

[namliam]

Trim is the function that will remove the spaces both at the start and end of any text.

Then doing update queries on your tables would make the updates...

You can use the query designer to do this... Good luck

 

[DCrake]

Just to confirm are you talking about blank spaces in the text of the known field OR spaces in the field names themselves. From reading your post it seemed to me to be the latter, as unbracketed field names that contain spaces would cause Access to crash, as you put it.

David

 

[JohnLee]

Hi,

Thank you for your response. I have 31 tables that have been exported from an SQL database, because our IT department will not give direct access to them. I have imported them into my Access application.

The Software develeopers of the application have said that there are blanks spaces at the either the beginning or end of the text in the fields in some of the tables that need to be removed, but could not tell me specifically which ones. So what I need to do now is go through each of the tables and check the contents of each fields text data for spaces at the beginning and end and remove them. The text in the fields row by row are not all the same text for instance:

z:\aeg.fof\age.txt
z:\beko.fof\beko.txt
z:\hotp.fof\hotp.txt

and so on.....

other table fields will have something like this as the text:

[ValidationFieldName]¦^([^ ]+)([^ ]{3})$¦[ValidationFieldName]¦$1 $2¦1
^[ ]*$¦NilToKeyException¦NilToKeyReason¦NO MODEL NO

and so on...........

Which is why I thought code rather than a querie would be the right route. I also need to identify somehow which fields and which row had these blank spaces removed, so that I can then get those amended in the SQL database, because again our IT department have made it clear that I will not be allowed to upload the fixed tables and that I would have to provide details of tables/fields to be amended.

I hope that has helped to create a clearer picture of why I need to do this and what I need to do, any assistance would be appreciated.

John

 

[DCrake]

Did you say that the 31 tables have been exported to txt files? If so could you post a sample file and indicate what is being used as a field delimiter.

David

 

[JohnLee]

Hi David,

No, they have been exported as an Excel file, and a tab allocated to each table. I then imported each tab as a table in its own right into my Database.

I have attached a sample of the tables in the Spreadsheet. In each of the fields in each of the spreadsheet tabs[tables] is the text data that I need to check for spaces.

Your assistance is appreciated.

John

 

[DCrake]

Are we talking about the field ValidationParams that you want strip out the spaces. I don't know the reaonsing behind you request but lets say the issue is resolved what are you going to do with the data once imported? it is going to be used elsewhere? there seems to be alot of repetition going on. A little more info might suffice.

Does [^] signify a space ?

David

 

[JohnLee]

Hi David,

these tables come from an SQL Database that interacts with an application called eFLOW. These application is scanning technology.

Because I don't have any control over the way in which these tables have been constructed I have to work with what is as seen in the spreadsheet sample.

Your right in that there is a lot of repetition, which adds to the frustration of it all. Unfortunately the company that design the SQL database really don't understand relational database concepts, otherwise they would not have created their SQL database in this way.

There are expressions within the fields of which I have no understanding and can not provide you with what they mean or do, like the one you highlighted.

I have been tasked with checking for spaces at the beginning and the end of a line of text as per one of my earlier posts, apparently spaces within a line of text are permitted.

Once all spaces have been removed from the front and end of a line of text and I have identified where those spaces appeared, the data will hopefully will be used to replace the data in the original SQL Database and thus resolve the problems we are currently experiencing.

I hope this of assistance.

John

 

[namliam]

Of the sample you attached there are two lines that end on a space, 2 whole lines...
579 and 625 on the validation sheet

Insert a column in the excel and past this formula into (new) column A
=AND(LEN(TRIM(B2))=LEN(B2),LEN(TRIM(C2))=LEN(C2),LEN(TRIM(D2))=LEN(D2),LEN(TRIM(E2))=LEN(E2),LEN(TRIM(F2))=LEN(F2),LEN(TRIM(G2))=LEN(G2),LEN(TRIM(H2))=LEN(H2),LEN(TRIM(I2))=LEN(I2))

Then filter on this column beeing false.

 

[JohnLee]

Hi David,

Thanks for that, so is it better to do this in a spreadsheet using your formula rather than via an Access Database. Assuming your suggestion is the spreadsheet, I would need to do this for each column I want to check for spaces for.

John

 

[namliam]

Hi David,

Thanks for that,

Your welcome David

Well if its a one time thing, doing it as delivered in excel is the easy quick fix...
If you need to redo this 2 times a year... This is the easy quick fix...
If you need to do this 5 times a day... find another way, either automated in excel or in access...

 

[JohnLee]

Hi David,

Thanks for your suggestion, I've had a meeting of the mass mullers and we have decided that your suggestion is the best option, as we won't being doing it that often.

Thanks once again.

John

 

[namliam]

Hi David,

Guess I have a new nick name