Help with awkward field names

[Guest]

Hi,

I need to find a way of removing spaces from a load of field names in a
table, hopefully to replace them with _'s . I know that I should not have
spaces in there in the first place, but I'm dealing with data imported from
csv's not of my own creation.

Is there a way of doing a kind of 'find and replace' style process on field
names?

(I would prefer to do this in access rather than excel as this is part of a
larger and regular process which I am trying to automate as much as possible)


Thanks in advance

 

[Jeff Boyce]

Zilla

Here's another approach...

Import the csv files, as is, into "temporary" import tables.

Create your permanent relational database structure (i.e., tables,
relationships).

Create queries that "parse" the data from your csv/Excel input (likely to be
not too well normalized) into your well-normalized permanent tables.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor

 

[Guest]

Thanks Jeff,

This sounds like a good way of going about it. Can you be more specific
about how you might go about doing this? The best way i can think would be to
query all the data from the csv data import table and then append it to a new
table with 'clean' field names. Does this sound like a sound approach to you?


Thanks again

 

[Guest]

This is a job for Rick Fisher's Find and Replace:
http://www.rickworld.com/

The problem is that changing the field names will mess up all your queries,
forms, reports, etc., that use the original field names. Rick add-in helps
you find all the places needing changing and will fix them for you.

If you have A03 and Name AutoCorrect on, it should also do the work for you.

Either way make a complete backup of your database before changing the field
names.

 

[Jeff Boyce]

Zilla

The "clean" names part, yes. But if your import data (csv, Excel) isn't
well-normalized, all you've done is put Excel data in a table. You won't
get the benefits of Access' relational functions/features, and you will make
your work much harder.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor