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? 25 2223
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?
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?
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?
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
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
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?
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?
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?
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?
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?
"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
"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
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
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>
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).
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
"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/
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/
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
"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
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.
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).
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Luke |
last post by:
Hi,
I'm getting an OverflowError which doesn't make sense to me. Is this a
python bug?
Traceback (most recent call last):
File "/home/demoau/lib/py/omniORB/__init__.py", line 717, in...
|
by: Graham |
last post by:
This has to do with class variables and instances variables.
Given the following:
<code>
class _class:
var = 0
#rest of the class
|
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to...
|
by: Martin |
last post by:
I have an intranet-only site running in Windows XPPro, IIS 5.1, PHP
5.2.5. I have not used or changed this site for several months - the
last time I worked with it, all was well.
When I tried it...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |