Import spreadsheet and Trim initial space in Column A

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I am trying to import an Excel spreadsheet into an Access table. All the
names in Column A of the spreadsheet are preceded by a space, and this is
really screwing up one of my queries!! Ideally, I would like to trim out the
space during the import process. Can this be done? I know how to do this in
Excel with VBA, but I don’t know how to do it in Access. I’m guessing it
requires VBA, right. Does anyone know how to do this? I tried to use a
TransferSpreadsheet macro and that didn’t give me the kind of control I was
looking for. Can anyone offer a suggestion?

Regards,
Ryan---
 
I am trying to import an Excel spreadsheet into an Access table. All the
names in Column A of the spreadsheet are preceded by a space, and this is
really screwing up one of my queries!! Ideally, I would like to trim out the
space during the import process. Can this be done? I know how to do this in
Excel with VBA, but I don¢t know how to do it in Access. I¢m guessing it
requires VBA, right. Does anyone know how to do this? I tried to use a
TransferSpreadsheet macro and that didn¢t give me the kind of control I was
looking for. Can anyone offer a suggestion?

Regards,
Ryan---
Click to expand...

You're sure that leading blank is a space character.. chr(32)?
Not some other non-print character?
Here's one way to make sure.
Copy one value of the Field that has the leading blank.
Then open the debug window.
Type in

?Asc("Paste the value here within quotes")
if it displays 32 on the next line down it is a space.

No, VBA is not the cure, though you could write some fancy code.
Import the spreadsheet data 'as is'.
Then run an update query to remove the spaces.
Back up your table first.

Update YourTable Set YourTable.[FieldName] = Trim([FieldName]);

Look up Trim in VBA help.
 
Thanks, and where do I put this? In the query view, where do I put this?
Field? Table? Criteria? Is there a way to 'automate' this a little more?
Thanks again,
Ryan---

--
RyGuy


fredg said:
I am trying to import an Excel spreadsheet into an Access table. All the
names in Column A of the spreadsheet are preceded by a space, and this is
really screwing up one of my queries!! Ideally, I would like to trim out the
space during the import process. Can this be done? I know how to do this in
Excel with VBA, but I donʼt know how to do it in Access. Iʼm guessing it
requires VBA, right. Does anyone know how to do this? I tried to use a
TransferSpreadsheet macro and that didnʼt give me the kind of control I was
looking for. Can anyone offer a suggestion?

Regards,
Ryan---
Click to expand...

You're sure that leading blank is a space character.. chr(32)?
Not some other non-print character?
Here's one way to make sure.
Copy one value of the Field that has the leading blank.
Then open the debug window.
Type in

?Asc("Paste the value here within quotes")
if it displays 32 on the next line down it is a space.

No, VBA is not the cure, though you could write some fancy code.
Import the spreadsheet data 'as is'.
Then run an update query to remove the spaces.
Back up your table first.

Update YourTable Set YourTable.[FieldName] = Trim([FieldName]);

Look up Trim in VBA help.
Click to expand...
 
Thanks, and where do I put this? In the query view, where do I put this?
Field? Table? Criteria? Is there a way to 'automate' this a little more?
Thanks again,
Ryan---
Click to expand...

Back up your table first.
On the main database folder, click on Queries + New
Select Design View and click OK
When the Show Table dialog opens, select the table name.
Click Add, then Close.
When the query grid opens, double click on the name of the field you
wish to remove the space from.
Then click on the Query tool button + Update Query

In the UpdateTo: row write:

Trim([FieldName])

Note.. change [FieldName] above to whatever the actual name of the
field is.

Then click on the View tool button and select SQL view.

The SQL should then look like this, with your actual table and field
names instead of my generic names.

Update YourTable Set YourTable.[FieldName] = Trim([FieldName]);

Click on the Bang (!) to run the query and update the field.
You will be warned about updating the data. Click Yes.

Save the query if you are going to need it in the future.
 
Wow! That is awesome! Thanks so much!! I have lots of experience with
Excel, but not so much experience with Access. I just bought the Access 2007
Bible yesterday. I plan to learn a lot more about Access because I believe,
like Excel, it is a very powerful tool, and as far as I can tell, it is
strong in areas where Excel is feeble (and vice versa).

Thanks again Fred!!

Regards,
Ryan---

--
RyGuy


fredg said:
Thanks, and where do I put this? In the query view, where do I put this?
Field? Table? Criteria? Is there a way to 'automate' this a little more?
Thanks again,
Ryan---
Click to expand...

Back up your table first.
On the main database folder, click on Queries + New
Select Design View and click OK
When the Show Table dialog opens, select the table name.
Click Add, then Close.
When the query grid opens, double click on the name of the field you
wish to remove the space from.
Then click on the Query tool button + Update Query

In the UpdateTo: row write:

Trim([FieldName])

Note.. change [FieldName] above to whatever the actual name of the
field is.

Then click on the View tool button and select SQL view.

The SQL should then look like this, with your actual table and field
names instead of my generic names.

Update YourTable Set YourTable.[FieldName] = Trim([FieldName]);

Click on the Bang (!) to run the query and update the field.
You will be warned about updating the data. Click Yes.

Save the query if you are going to need it in the future.
Click to expand...
 
Back
Top