Removing Unwanted Spaces From Table Fields, Access 2007

[rd.prasanna]

Hi All,



I have a concern in IMPORTING data from Excel to Access Table,
Actually I'am importing bulk data from excel to access table using codes, in which code will auto generate the temptable and uplaod the data to temptable late move required data to the target table.

My problem is that while creating the temptable im not specifing the lenfg of the fields, cos data is like that, but in target table im getting unwanted extra SPACES after the text, How to get rid of this.

Thanks in advance
Prasanna R.D

[JoeM]

Not quite sure I entirely follow your process, but as far as removing extra spaces, both Excel and Access have TRIM functions which will remove blank spaces at the end of any field.

[IslandHydro]

To add to Prasanna's comment, once you've brought data into the temptable, you can use an update query along this lines:

UPDATE TableName SET TableName.[FieldName] = Trim([FieldName]);

You can use this type of query on multiple fields, updating the entire table in one step

[DerFarm]

If your entries can have double spaces INSIDE the string you can't use the trim function. You can (I think) invoke a global search and replace after opening the text file under program control.

In that case, simply replace " " with " ".

Good Luck