separate vs. concatenate

T

tjtjjtjt

I have to Import some Tables from Access into Excel.
The field names in the tables, and I don't have access to the database to
add captions.
Is there a formula I could use (with a helper column) to separate the words
in a Field Name such as: PayRate?
Thanks,

tj
 
B

Bob Phillips

Check out Data>Text To Columns

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

David DeRolph

The field names must be the same in Access and Excel in order for the Import
to work, right? So, why do you want to change the field names? You want to
see Pay Rate instead of PayRate, right? If PayRate is in cell A1, the
formula =(LEFT(A1,3)&" "&(RIGHT(A1,4))) would yield Pay Rate.

But, you'll have to decide how you're going to handle this so that it won't
interfere with future imports. I don't think you can simply put the formula
in cell A2, for example, because it would be overwritten by subsequent
imports. However, I haven't worked with such imports for awhile; perhaps
I'm wrong about this; perhaps you can append future imports instead of
replacing existing records in Excel.

Perhaps you could copy the entire table or tables (that are imported) to a
separate range and use the formula to generate your column headings there.
 
J

Jamie Collins

The field names must be the same in Access and Excel in order for the Import
to work, right?
Click to expand...

I'm a bit baffled how the OP can import the data without having access
to the database. Assuming they *can* actually query the data, they
could alias the columns e.g.

SELECT PayRate AS [Pay Rate] FROM MyTable;

If they have no control over the query command text, e.g. they are
calling a stored procedure, they should request the database
administrator, who *does* have access to the database, to make the
change.

Jamie.

--
 
D

David DeRolph

I've never used the alias technique in Access, but your idea seems to be a
good solution. As I understand you, you're basically saying the format of
the label, PayRate, can be changed to Pay Rate in an Access Query, then the
Query imported to Excel rather the the Access table, which would still only
contain PayRate as a field name.

Sounds like a very good idea to me. It would be easier than manipulating
the data in Excel to convert the label.

Jamie Collins said:
The field names must be the same in Access and Excel in order for the Import
to work, right?
Click to expand...

I'm a bit baffled how the OP can import the data without having access
to the database. Assuming they *can* actually query the data, they
could alias the columns e.g.

SELECT PayRate AS [Pay Rate] FROM MyTable;

If they have no control over the query command text, e.g. they are
calling a stored procedure, they should request the database
administrator, who *does* have access to the database, to make the
change.

Jamie.

--
Click to expand...
 
Top