Access SQL Trim All Data In Temp Table (1 Viewer)

[Trevor G]

I am importing data from Excel which is download from another countries system and the data comes with extra spaces and I have explored using Excels Trim Function before the data is uploaded and it is massively slow. So I am looking for some VBA (SQL statement) to run an update to the table to trim all the relevenat fields in that table, but I am a little unsure how to do this. It has been a long day here with lots of VBA coding and I am struggling.

Lets say the table name is tblTempImport and it will be all the fields with varying names.

Any pointers would be helpfull.

 

[Trevor G]

I import data into a Temp Table and there are additional spaces. What I have below is code to loop through all the tables in the database and trim all text fields, what I need it to do is just do this on the TempImport table.

What would I need to adjust this to assign the code to the TempImport only?

Sub TrimAllTextFieldsAllTables()
'The following code will trim all text fields in all tables
Dim db As DAO.Database
Dim tbls As DAO.TableDefs
Dim tbl As DAO.TableDef
Dim thisTable As DAO.TableDef
Dim SQLString As String
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb
Set tbls = db.TableDefs
' loop through each appropriate table (i.e. no linked or system tables)
For Each tbl In tbls
If tbl.Attributes = 0 Then
Set thisTable = tbl
' grab all fields
Set flds = thisTable.Fields

SQLString = "UPDATE [" & tbl.Name & "] SET "

' if field is text, create SQL string to trim it
For Each fld In flds
If fld.Type = dbText Then
SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
End If
Next fld

SQLString = Left(SQLString, Len(SQLString) - 1) & ";"
Debug.Print SQLString
' execute update statement on table
db.Execute SQLString, dbFailOnError

End If
Next tbl

End Sub

 

[plog]

UPDATE YourTableNameHere SET FieldName = Trim([FieldName]);

That's a generic query that will update a field to remove leading and trailing spaces. You could create a query that did that for every FieldName in YourTableNameHere by manually adding every field in the same manner as above. You could also get snazzy and have VBA load your table's field names and dynamically creating a query that does the same thing.

If your field names don't change, I vote manually adding your fields.

 

[plog]

You posted right as I posted. So let me give you more specific advice. I think changing this line:

If tbl.Attributes = 0 Then


To this:

if tbl.Name="TempImport" Then

Will accomplish what you want. However, always make a copy of your database before running any action queries.

 

[Trevor G]

Excellent that works a treat and I did check it on a copy of the database.

Thank you, its been a long hard day. I can smile now though thanks to you.