By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Is Access bogus, or is it me?

P: n/a
DFS
I have a job to automatically import Excel data and post to database tables,
via a point-click interface. Choose-file-and-it-does-the-rest kind of
thing.

Cient stores data in columns in his sheet (I told them it should be in rows,
but that's another issue). Sometimes the sheet gets new columns added. At
that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET NAME",
True
* can't store a link to the Excel file and use it, because the sheet has
text data in columns which Access recognizes as dates, and it shows #Num and
gives Numeric Overflow errors when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to the .csv,
because some of the data has quotes around names, and it gets split up
wrong. Plus, when the user adds columns to the sheet and saves as .csv,
Access won't see the new columns because the import spec won't recognize
them.

* can't save sheet as .txt (tab-delimited) and import it, or store a link to
the .txt, because when the user adds columns to the sheet and saves as .txt,
Access won't see the new columns because the import spec won't recognize
them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #1
Share this Question
Share on Google+
25 Replies


P: n/a
I suspect it's neither.

Why would they be adding new columns (!), that sounds very stupid to me.

I would expect the table schema to stay the same but the data to change
(from your comments I think you do as well), if there is a valid reason why
they are adding columns instead of rows then I can see there is a reason to
pursue this, if they should be adding rows instead of columns then tell them
that is what they have to do and stop making life difficult for yourself.

--

Terry Kreft
"DFS" <nospam@dfs_.comwrote in message
news:Ks******************@bignews1.bellsouth.net.. .
I have a job to automatically import Excel data and post to database
tables,
via a point-click interface. Choose-file-and-it-does-the-rest kind of
thing.

Cient stores data in columns in his sheet (I told them it should be in
rows,
but that's another issue). Sometimes the sheet gets new columns added.
At
that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET
NAME",
True
* can't store a link to the Excel file and use it, because the sheet has
text data in columns which Access recognizes as dates, and it shows #Num
and
gives Numeric Overflow errors when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to the .csv,
because some of the data has quotes around names, and it gets split up
wrong. Plus, when the user adds columns to the sheet and saves as .csv,
Access won't see the new columns because the import spec won't recognize
them.

* can't save sheet as .txt (tab-delimited) and import it, or store a link
to
the .txt, because when the user adds columns to the sheet and saves as
..txt,
Access won't see the new columns because the import spec won't recognize
them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #2

P: n/a
DFS
Terry Kreft wrote:
I suspect it's neither.
It would be nice if Access provided methods and properties for the developer
to update the import/export specs programmatically.

Why would they be adding new columns (!), that sounds very stupid to
me.
That's how their guy works with his spreadsheets. He has lists of names and
job titles. He adds columns when he adds job titles (though it is
infrequent). I told him it wasn't the way, but he has a bunch of queries
and other processes going against his sheet.
I would expect the table schema to stay the same but the data to
change (from your comments I think you do as well), if there is a
valid reason why they are adding columns instead of rows then I can
see there is a reason to pursue this,
There is no truly valid reason to use columns, but there are many valid
reasons not to.

if they should be adding rows
instead of columns then tell them that is what they have to do and
stop making life difficult for yourself.
ha! That's the feeling I've been getting this morning.

I'm not good at telling the client 'you have to do it this way'. I prefer
to work around their situation. This one may not be tenable, though, if
they require a fully-automated system.

Thanks


"DFS" <nospam@dfs_.comwrote in message
news:Ks******************@bignews1.bellsouth.net.. .
>I have a job to automatically import Excel data and post to database
tables, via a point-click interface.
Choose-file-and-it-does-the-rest kind of thing.

Cient stores data in columns in his sheet (I told them it should be
in rows, but that's another issue). Sometimes the sheet gets new
columns added.
At
>that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET
NAME", True
* can't store a link to the Excel file and use it, because the sheet
has text data in columns which Access recognizes as dates, and it
shows #Num and gives Numeric Overflow errors when trying to
query/post the data.

* can't save sheet as .csv and import it, or store a link to the
.csv, because some of the data has quotes around names, and it gets
split up wrong. Plus, when the user adds columns to the sheet and
saves as .csv, Access won't see the new columns because the import
spec won't recognize them.

* can't save sheet as .txt (tab-delimited) and import it, or store a
link to the .txt, because when the user adds columns to the sheet
and saves as .txt, Access won't see the new columns because the
import spec won't recognize them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #3

P: n/a
U R teh SUCK. heh.

O.K., seriously. Why not write code that transposes the spreadsheet,
thus turning columns into rows? Then store the data the way a database
is meant to store it, and let the jagoff keep his data the way a jagoff
was meant to keep it. Everyone's happy.

I am teh WIN!!

DFS wrote:
I have a job to automatically import Excel data and post to database tables,
via a point-click interface. Choose-file-and-it-does-the-rest kind of
thing.

Cient stores data in columns in his sheet (I told them it should be in rows,
but that's another issue). Sometimes the sheet gets new columns added. At
that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET NAME",
True
* can't store a link to the Excel file and use it, because the sheet has
text data in columns which Access recognizes as dates, and it shows #Num and
gives Numeric Overflow errors when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to the .csv,
because some of the data has quotes around names, and it gets split up
wrong. Plus, when the user adds columns to the sheet and saves as .csv,
Access won't see the new columns because the import spec won't recognize
them.

* can't save sheet as .txt (tab-delimited) and import it, or store a link to
the .txt, because when the user adds columns to the sheet and saves as .txt,
Access won't see the new columns because the import spec won't recognize
them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?
Aug 10 '06 #4

P: n/a
DFS wrote:
That's how their guy works with his spreadsheets. He has lists of names and
job titles. He adds columns when he adds job titles (though it is
infrequent). I told him it wasn't the way, but he has a bunch of queries
and other processes going against his sheet.
No answers, but I'm about to go into rant mode...

What a moron this guy is! We have someone like this at work who has at
his disposal a perfectly good computerized maintenance manmagement
system, yet what he does for every new project he's involved with is
create his own set of spreadsheets. As a result we have information
fragmented all over our organization as a result of this particular
manager. It's stupid beyond all belief.

This client of yours needs to be strung up and beaten with spreadsheet
print outs fashioned into paper mache bats. Better yet, enclose him in
a paper mache pig made of spread sheet print outs and have everyone
clobber him with a baseball bat as per standard Pinata operating procedures.

Spreadsheet people destroy the world of information systems and should
all be abolished!
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 10 '06 #5

P: n/a
LOL

Spreadsheet people, Pinata style paper-mache pig, basball bats.....what
fun could be had - especially after having a day of spreadsheet grief
here!!!

Tim Marshall wrote:
DFS wrote:
That's how their guy works with his spreadsheets. He has lists of names and
job titles. He adds columns when he adds job titles (though it is
infrequent). I told him it wasn't the way, but he has a bunch of queries
and other processes going against his sheet.

No answers, but I'm about to go into rant mode...

What a moron this guy is! We have someone like this at work who has at
his disposal a perfectly good computerized maintenance manmagement
system, yet what he does for every new project he's involved with is
create his own set of spreadsheets. As a result we have information
fragmented all over our organization as a result of this particular
manager. It's stupid beyond all belief.

This client of yours needs to be strung up and beaten with spreadsheet
print outs fashioned into paper mache bats. Better yet, enclose him in
a paper mache pig made of spread sheet print outs and have everyone
clobber him with a baseball bat as per standard Pinata operating procedures.

Spreadsheet people destroy the world of information systems and should
all be abolished!
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 10 '06 #6

P: n/a
DFS
ManningFan wrote:
U R teh SUCK. heh.
What language is that?

O.K., seriously. Why not write code that transposes the spreadsheet,
thus turning columns into rows? Then store the data the way a
database is meant to store it, and let the jagoff keep his data the
way a jagoff was meant to keep it. Everyone's happy.
We can't all be happy. I do transpose the data into rows (so I'm happy),
but when he adds columns to the sheet (his happy way), I can't auto-import
or auto-link it into Access for the reasons I mentioned earlier (now neither
of us is happy).

It's just sad...


I am teh WIN!!

DFS wrote:
>I have a job to automatically import Excel data and post to database
tables, via a point-click interface.
Choose-file-and-it-does-the-rest kind of thing.

Cient stores data in columns in his sheet (I told them it should be
in rows, but that's another issue). Sometimes the sheet gets new
columns added. At that point, Access/import spec won't recognize
the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET
NAME", True
* can't store a link to the Excel file and use it, because the sheet
has text data in columns which Access recognizes as dates, and it
shows #Num and gives Numeric Overflow errors when trying to
query/post the data.

* can't save sheet as .csv and import it, or store a link to the
.csv, because some of the data has quotes around names, and it gets
split up wrong. Plus, when the user adds columns to the sheet and
saves as .csv, Access won't see the new columns because the import
spec won't recognize them.

* can't save sheet as .txt (tab-delimited) and import it, or store a
link to the .txt, because when the user adds columns to the sheet
and saves as .txt, Access won't see the new columns because the
import spec won't recognize them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #7

P: n/a
Can you clarify the data stored in the spreadsheet a little more?
That might help with a solution.
>From reading your message, it sounds like it is 1 row per contact
record (good) and the columns are things like:

Name | Email | Phone | Title | ...

But they guy occasionally adds new columns? Exactly what are these?
I'm thinking you are saying he has a column like "Sales director" and
another as "HR manager" or some such, that both columns are are Titles,
and he may add a new column with a new title when he adds a new person
who's title doesn't match an existing one?

Am I close? If I am, of course you are correct in saying he should have
one column for TITLE, and enter the person's title in the cell.

If I'm in the right ball park, your best bet is to help him reorganize
the Excel Spreadsheet so that it both meets his needs for easy entry
and sorting, grouping on those Titles as well as makes the spreadsheet
more like a database that can be easiliy imported into Access.

Once the Spreadsheet is cleaned up, then you can work on the Access
portion.

Appelq
DFS wrote:
I have a job to automatically import Excel data and post to database tables,
via a point-click interface. Choose-file-and-it-does-the-rest kind of
thing.

Cient stores data in columns in his sheet (I told them it should be in rows,
but that's another issue). Sometimes the sheet gets new columns added. At
that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET NAME",
True
* can't store a link to the Excel file and use it, because the sheet has
text data in columns which Access recognizes as dates, and it shows #Num and
gives Numeric Overflow errors when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to the .csv,
because some of the data has quotes around names, and it gets split up
wrong. Plus, when the user adds columns to the sheet and saves as .csv,
Access won't see the new columns because the import spec won't recognize
them.

* can't save sheet as .txt (tab-delimited) and import it, or store a link to
the .txt, because when the user adds columns to the sheet and saves as .txt,
Access won't see the new columns because the import spec won't recognize
them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?
Aug 10 '06 #8

P: n/a
If I understand the data would look like this.

FirstName | John | Ann | Jane
LastName | Doe | Smith | Doe
Title | VP | Accounting | Data Manager

Where the columns would be rows in Access. Your client should be able to
names without problems, or is he add a new row such as [Title2]?

You could transpose the rows and columns into a new workbook. Import that as
text into a new temp table to be process into your Access tables. Using a
new table would allow the client to add a new row which you can flag with a
message and possibly ignore.

"DFS" <nospam@dfs_.comwrote in message
news:Ks******************@bignews1.bellsouth.net.. .
I have a job to automatically import Excel data and post to database
tables,
via a point-click interface. Choose-file-and-it-does-the-rest kind of
thing.

Cient stores data in columns in his sheet (I told them it should be in
rows,
but that's another issue). Sometimes the sheet gets new columns added.
At
that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET
NAME",
True
* can't store a link to the Excel file and use it, because the sheet has
text data in columns which Access recognizes as dates, and it shows #Num
and
gives Numeric Overflow errors when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to the .csv,
because some of the data has quotes around names, and it gets split up
wrong. Plus, when the user adds columns to the sheet and saves as .csv,
Access won't see the new columns because the import spec won't recognize
them.

* can't save sheet as .txt (tab-delimited) and import it, or store a link
to
the .txt, because when the user adds columns to the sheet and saves as
..txt,
Access won't see the new columns because the import spec won't recognize
them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #9

P: n/a
DFS
paii, Ron wrote:
If I understand the data would look like this.

FirstName | John | Ann | Jane
LastName | Doe | Smith | Doe
Title | VP | Accounting | Data Manager

Where the columns would be rows in Access. Your client should be able
to names without problems, or is he add a new row such as [Title2]?

You could transpose the rows and columns into a new workbook. Import
that as text into a new temp table to be process into your Access
tables.
Doing it programmatically won't work. I have to use an import spec - the
data has to be text format when I link or import because he has non-date
data in fields with date as the first row, and Access munges it. If he adds
columns, the import spec won't see them. And the import spec can't be
updated programmatically.

It's a freakin' nightmare. I'm about to tell him he'll have to manually
import it into a separate Access file, and I'll handle it from there with
the automation.


Using a new table would allow the client to add a new row
which you can flag with a message and possibly ignore.

"DFS" <nospam@dfs_.comwrote in message
news:Ks******************@bignews1.bellsouth.net.. .
>I have a job to automatically import Excel data and post to database
tables, via a point-click interface.
Choose-file-and-it-does-the-rest kind of thing.

Cient stores data in columns in his sheet (I told them it should be
in rows, but that's another issue). Sometimes the sheet gets new
columns added.
At
>that point, Access/import spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET
NAME", True
* can't store a link to the Excel file and use it, because the sheet
has text data in columns which Access recognizes as dates, and it
shows #Num and gives Numeric Overflow errors when trying to
query/post the data.

* can't save sheet as .csv and import it, or store a link to the
.csv, because some of the data has quotes around names, and it gets
split up wrong. Plus, when the user adds columns to the sheet and
saves as .csv, Access won't see the new columns because the import
spec won't recognize them.

* can't save sheet as .txt (tab-delimited) and import it, or store a
link to the .txt, because when the user adds columns to the sheet
and saves as .txt, Access won't see the new columns because the
import spec won't recognize them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #10

P: n/a
DFS
Appelq wrote:
Can you clarify the data stored in the spreadsheet a little more?
That might help with a solution.
>From reading your message, it sounds like it is 1 row per contact
record (good) and the columns are things like:

Name | Email | Phone | Title | ...

But they guy occasionally adds new columns? Exactly what are these?
I'm thinking you are saying he has a column like "Sales director" and
another as "HR manager" or some such, that both columns are are
Titles, and he may add a new column with a new title when he adds a
new person who's title doesn't match an existing one?
Exactly. Not only that, but 5 columns per Title: Sales Director Name, Sales
Director Hire Date, Sales Director Attended Class?, etc.

So a spreadsheet with 10 titles has 50 columns and few rows, rather than 5
columns and many rows.

For now I have to work within their constraints.
Am I close? If I am, of course you are correct in saying he should
have one column for TITLE, and enter the person's title in the cell.
Yes.

If I'm in the right ball park, your best bet is to help him reorganize
the Excel Spreadsheet so that it both meets his needs for easy entry
and sorting, grouping on those Titles as well as makes the spreadsheet
more like a database that can be easiliy imported into Access.

Once the Spreadsheet is cleaned up, then you can work on the Access
portion.
I can't make him clean up his spreadsheet. I built normalized Oracle tables
to post the data to, but I first import it into Access - and that's where
the problems are.

Appelq
DFS wrote:
>I have a job to automatically import Excel data and post to database
tables, via a point-click interface.
Choose-file-and-it-does-the-rest kind of thing.

Cient stores data in columns in his sheet (I told them it should be
in rows, but that's another issue). Sometimes the sheet gets new
columns added. At that point, Access/import spec won't recognize
the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME", "SHEET
NAME", True
* can't store a link to the Excel file and use it, because the sheet
has text data in columns which Access recognizes as dates, and it
shows #Num and gives Numeric Overflow errors when trying to
query/post the data.

* can't save sheet as .csv and import it, or store a link to the
.csv, because some of the data has quotes around names, and it gets
split up wrong. Plus, when the user adds columns to the sheet and
saves as .csv, Access won't see the new columns because the import
spec won't recognize them.

* can't save sheet as .txt (tab-delimited) and import it, or store a
link to the .txt, because when the user adds columns to the sheet
and saves as .txt, Access won't see the new columns because the
import spec won't recognize them.

I can't figure out how to do this - in fact I think it can't be done.

Or am I missing something?

Aug 10 '06 #11

P: n/a
"DFS" <nospam@dfs_.comwrote in
news:ro*****************@bignews2.bellsouth.net:

I can't figure out how to do this - in fact I think it can't be
done.
Or am I missing something?
The only thing that I can add here is that I remember investigating
TransferSpreadsheet years ago and finding that it worked in a manner
different than that described in the help file; that is, it would do more,
better than MS confessed.

But I have a difficult time modelling (in my little brain) what is going on
here. If you were to post a few of the excel files, (shortened), those with
the standard columns and those with the additional columns, with munged
data, and a clear description of what you want the ultimate JET Table to be
like, some of us might find some time to see how we might tackle the
problem.

This could be helpful for us as a learning situation and, perhaps for you,
if we come upon anything.

(Oh ... and the rules of engagement, as well).

--
Lyle Fairfield
Aug 10 '06 #12

P: n/a
"DFS" <nospam@dfs_.comwrote in
news:Ks******************@bignews1.bellsouth.net:
I have a job to automatically import Excel data and post to
database tables, via a point-click interface.
Choose-file-and-it-does-the-rest kind of thing.

Cient stores data in columns in his sheet (I told them it
should be in rows, but that's another issue). Sometimes the
sheet gets new columns added. At that point, Access/import
spec won't recognize the new columns.

DoCmd.TransferText acImportDelim, "importSpec", "TABLE_NAME",
"SHEET NAME", True
* can't store a link to the Excel file and use it, because the
sheet has text data in columns which Access recognizes as
dates, and it shows #Num and gives Numeric Overflow errors
when trying to query/post the data.

* can't save sheet as .csv and import it, or store a link to
the .csv, because some of the data has quotes around names,
and it gets split up wrong. Plus, when the user adds columns
to the sheet and saves as .csv, Access won't see the new
columns because the import spec won't recognize them.

* can't save sheet as .txt (tab-delimited) and import it, or
store a link to the .txt, because when the user adds columns
to the sheet and saves as .txt, Access won't see the new
columns because the import spec won't recognize them.

I can't figure out how to do this - in fact I think it can't
be done.

Or am I missing something?
You could try to open the spreadsheet as an application object,
http://www.mvps.org/access/modules/mdl0006.htm

Then start reading a block of cells, writing the data out to an
array, Repeat till you've got all your headers.

Then read out the data in a similar fashion, writing the data to a
properly designed table. .

Or, migrate his data once, build him a user interface and delete
Excel from his computer.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Aug 10 '06 #13

P: n/a
Oh good grief, 5 minutes I was laughing at this, thank you!

I can see the pinata and hear it as spreadsheet guy squeeks inside, LOL.

--

Terry Kreft
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:eb*********@coranto.ucs.mun.ca...
DFS wrote:
That's how their guy works with his spreadsheets. He has lists of names
and
job titles. He adds columns when he adds job titles (though it is
infrequent). I told him it wasn't the way, but he has a bunch of
queries
and other processes going against his sheet.

No answers, but I'm about to go into rant mode...

What a moron this guy is! We have someone like this at work who has at
his disposal a perfectly good computerized maintenance manmagement
system, yet what he does for every new project he's involved with is
create his own set of spreadsheets. As a result we have information
fragmented all over our organization as a result of this particular
manager. It's stupid beyond all belief.

This client of yours needs to be strung up and beaten with spreadsheet
print outs fashioned into paper mache bats. Better yet, enclose him in
a paper mache pig made of spread sheet print outs and have everyone
clobber him with a baseball bat as per standard Pinata operating
procedures.
>
Spreadsheet people destroy the world of information systems and should
all be abolished!
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Aug 10 '06 #14

P: n/a
The best way to handle this situation is to work out the costs then talk to
the money guy.

"If you can arrange it so that the spreadsheet is properly formatted so that
I can use the standard methods in Access to import the data it will take me
x days and cost you $a, if on the other hand this spreadsheet stays in the
same format then it will take me y days and cost you $b to achieve the same
task"

Of course "y" is significantly larger than "x" with a corresponding
difference between $a and $b. You'd be surprised (not) by how quickly the
customer sees the light in that situation.

The alternative is to simply link to the spreadsheet, you say in your
original post that you have dates stored in cells which give rise to #NUM
errors, if the column is formatted as text (prior to data entry) then this
should not happen.

If there are still problems then please do as Lyle has asked and post some
sample data so we can see the problem (in all it's horror).
--

Terry Kreft
"DFS" <nospam@dfs_.comwrote in message
news:yT*****************@bignews1.bellsouth.net...
Terry Kreft wrote:
<SNIP>
I'm not good at telling the client 'you have to do it this way'. I prefer
to work around their situation. This one may not be tenable, though, if
they require a fully-automated system.

Thanks
<SNIP>
Aug 10 '06 #15

P: n/a
DFS
Thanks for the offer, Lyle. I'm going out of town for a week, but will
continue the drama when I return...

Lyle Fairfield wrote:
"DFS" <nospam@dfs_.comwrote in
news:ro*****************@bignews2.bellsouth.net:

>I can't figure out how to do this - in fact I think it can't be
done.
>Or am I missing something?

The only thing that I can add here is that I remember investigating
TransferSpreadsheet years ago and finding that it worked in a manner
different than that described in the help file; that is, it would do
more, better than MS confessed.

But I have a difficult time modelling (in my little brain) what is
going on here. If you were to post a few of the excel files,
(shortened), those with the standard columns and those with the
additional columns, with munged data, and a clear description of what
you want the ultimate JET Table to be like, some of us might find
some time to see how we might tackle the problem.

This could be helpful for us as a learning situation and, perhaps for
you, if we come upon anything.

(Oh ... and the rules of engagement, as well).

Aug 11 '06 #16

P: n/a
Per DFS:
>It would be nice if Access provided methods and properties for the developer
to update the import/export specs programmatically.
I've been around the block a few times vis-a-vis importing data from
spreadsheets.

Import specs are OK, but ODBC linking to a spreadsheet is more convenient.

Having said that, at this stage of the game I do neither. Been burned too many
times by users changing layouts without telling anybody - or typing goofy data
into an unchanged layout.

My current approach is to open spreadsheet via a VBA instance of Excel, do a
series of sanity checks to make sure the format hasn't changed, and then boogie
through the rows and columns - writing the data to one or more work tables -
after which I do the final stage by massaging the work table(s).

Things still get weird when somebody changes a layout - but if my sanity check
catches it, there isn't that period of users walking around wondering what's
gone wrong with this fushlurggener application. Instead, they get a nice little
dialog telling them the spreadsheet format isn't right... and maybe even a
screen snap showing what the correct format is. If something slips past the
sanity check - like goofey data - it's easier to find with a work table and I
also have the option of building an MS Access report on the work table that
lists any invalid data.

It's a *lot* more coding, but in the end - after people have changed the layout
a few times and the app has loaded bad data without anybody knowing about it
until fluky results come out later - I could argue that the man hours aren't
that different.
--
PeteCresswell
Aug 11 '06 #17

P: n/a
It's you.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #18

P: n/a
"DFS" <nospam@dfs_.comwrote in
news:yT*****************@bignews1.bellsouth.net:
Terry Kreft wrote:
>I suspect it's neither.

It would be nice if Access provided methods and properties for the
developer to update the import/export specs programmatically.
A static description of a data source schema should not, I think, be
expected to be easily updatable.
>Why would they be adding new columns (!), that sounds very stupid
to me.

That's how their guy works with his spreadsheets. He has lists of
names and job titles. He adds columns when he adds job titles
(though it is infrequent). I told him it wasn't the way, but he
has a bunch of queries and other processes going against his
sheet.
The problem you are experiencing is created by not settling on a
static data structure.
>I would expect the table schema to stay the same but the data to
change (from your comments I think you do as well), if there is a
valid reason why they are adding columns instead of rows then I
can see there is a reason to pursue this,

There is no truly valid reason to use columns, but there are many
valid reasons not to.
Including the fact that it makes it harder for you, the programmer,
to accomodate his changes.
>if they should be adding rows
instead of columns then tell them that is what they have to do
and stop making life difficult for yourself.

ha! That's the feeling I've been getting this morning.

I'm not good at telling the client 'you have to do it this way'.
I prefer to work around their situation. This one may not be
tenable, though, if they require a fully-automated system.
The problem you're experiencing is directly caused by the bad
structure of the spreadsheet.

Consider: if this were all Access, and you had a table to which
columns were constantly being added, would you think there was
something wrong with Access forms for not automatically being able
to pick up and display these newly added columns?

If you do, then I think you have completely unreasonable
expectations for any software application, but I suspect this
analogy will make it clear why the problem is not with Access but
with the client's methods: he's changing structure of a data source.
That can't be accomodated with static structures, but only by
methods that dynamically read the data source and import through
methods that don't depend on stored data schemas.

Automating Excel would be the way to go if he insists on adding
columns.

However, that will vastly increase the amount of code needed over
what would be necessary if you used rows instead of columns. If he
wants to pay for the Excel automation so he can continue doing it
wrong, that's his choice.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #19

P: n/a
Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote in
news:eb*********@coranto.ucs.mun.ca:
Spreadsheet people destroy the world of information systems and
should all be abolished!
Spreadsheet people are driving the design of the UI in Access 2007.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Aug 11 '06 #20

P: n/a
Terry Kreft wrote:
Oh good grief, 5 minutes I was laughing at this, thank you!
I'm delighted I could at least do something for you after all the code
snippets from you I've used from mvps.org (thanks). 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Aug 11 '06 #21

P: n/a
"DFS" <nospam@dfs_.comwrote
I have a job to automatically import Excel
data and post to database tables, via a point-
click interface. Choose-file-and-it-does-the-
rest kind of thing.
Here in the asylum, we have known for many years that Access was not "bogus"
but was only a figment of our fevered imaginations. But, as long as no one
lets our clients in on that secret, it gives us something to do, or imagine
we are doing, that pays, or we imagine that it does. :-)

Larry
Aug 11 '06 #22

P: n/a
rkc
David W. Fenton wrote:
Tim Marshall <TI****@PurplePandaChasers.Moertheriumwrote in
news:eb*********@coranto.ucs.mun.ca:

>>Spreadsheet people destroy the world of information systems and
should all be abolished!


Spreadsheet people are driving the design of the UI in Access 2007.
Sad, but seemingly true.
Aug 11 '06 #23

P: n/a
I'm thinking you can do this with a little VBA code, but need more
info.

DFS wrote:
Exactly. Not only that, but 5 columns per Title: Sales Director Name, Sales
Director Hire Date, Sales Director Attended Class?, etc.
Sow Row # 5 is John Doe, and Column M is named Sales Director Name.
If John Doe is the Sales Director, what value is in the Cell M5? "Yes"
or "X" or something?
If John is NOT the Sales Director, I assume the cell is blank and there
is something in the column with his Title.

Essentially, you'll need to create a table to hold the cleaned up data,
with fields like:
Title; HireDate; AttendedClass; etc.

The read each record in VB with something like
Begin Case
Case rs.SalesDirectorName NOT NULL
Title = rs.SalesDirectorName
HireDate = rs.SalesDirectorHireDate
AttendedClass = rs.SalesDirectorAttendedClass
Case rs.HRManagerName NOT NULL
Title = rs.HRManagerName
HireDate = rs.HRManagerHireDate
AttendedClass = rs.HRManagerAttendedClass
Case yada yada yada
End Case

Of course the limitation with this approach is that everytime the user
adds a new title and the 5 columns that go with it, you need to add a
case statement to the code.

There is lots of help on the web on setting up the basic VB code,
initiallizing and opening the dataset, building the loop, etc. Let us
know if you need help with that.

The other approach that occurred to me was to use some Excel functions
to clean it up before importing to Access. Investigate Excel functions
like TRANSPOSE that might do the trick (Disclaimer: I'm not an Excel
expert).

Aug 12 '06 #24

P: n/a
Pete thanks for this, I am currently wrestling with a similar problem.
two columns set to XL "General" format so transfer spreadsheet fails. I
wrote code to read the sheet into an array, and then into a properly
formatted table, but your idea on data validation per column will be
taken on board.
Its a pity that transferspreadsheet won't work properly as creating the
array takes a good deal longer.
Best wishes
Graham

(PeteCresswell) wrote:
Per DFS:
It would be nice if Access provided methods and properties for the developer
to update the import/export specs programmatically.

I've been around the block a few times vis-a-vis importing data from
spreadsheets.

Import specs are OK, but ODBC linking to a spreadsheet is more convenient.

Having said that, at this stage of the game I do neither. Been burned too many
times by users changing layouts without telling anybody - or typing goofy data
into an unchanged layout.

My current approach is to open spreadsheet via a VBA instance of Excel, do a
series of sanity checks to make sure the format hasn't changed, and then boogie
through the rows and columns - writing the data to one or more work tables -
after which I do the final stage by massaging the work table(s).

Things still get weird when somebody changes a layout - but if my sanity check
catches it, there isn't that period of users walking around wondering what's
gone wrong with this fushlurggener application. Instead, they get a nice little
dialog telling them the spreadsheet format isn't right... and maybe even a
screen snap showing what the correct format is. If something slips past the
sanity check - like goofey data - it's easier to find with a work table and I
also have the option of building an MS Access report on the work table that
lists any invalid data.

It's a *lot* more coding, but in the end - after people have changed the layout
a few times and the app has loaded bad data without anybody knowing about it
until fluky results come out later - I could argue that the man hours aren't
that different.
--
PeteCresswell
Aug 13 '06 #25

P: n/a
The other approach that occurred to me was to use some Excel functions
to clean it up before importing to Access. Investigate Excel functions
like TRANSPOSE that might do the trick (Disclaimer: I'm not an Excel
expert).
I need to add my to cents into the rants listed above on this topic.

Here is a valid reason for placing data in column format instead of the
normal row format:

Excel currently has a limitation of 256 columns, I currently have a
project that is required to track upto 650 data elements per unit, with
~300 units worth of data. I need to place this data into Access but
cannot perform a normal "Import from Excel" because all of my data
field names are listed in column 1, instead of row 1.

ie,
__________________________________________________ ________
Col1 | Col2 | Col3 | Col4 ... One Col for each Unit
SN | X | Y | Z
HiLim1| 10 | 10 | 10
LoLim1| 0 | 0 | 0
T1_250| 2 | 5 | 7
T1_300| 12 | 1 | 5
..
..
..
one row for each test element (650 rows needed)
__________________________________________________ ________

I cannot perform a transpose of my data as it will break the 256 column
limit.

It sure would be nice for Access to have the capability to import with
column 1 as header instead of the current capability of only row 1 as
header.

How can I do this using Access. I am a whiz in excel macro writing, but
only now starting to use Access.

Aug 14 '06 #26

This discussion thread is closed

Replies have been disabled for this discussion.