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

more and more excel to access....and the inputs are really stupid

P: n/a
We get more and more data done in excel and then they want it imported
into access.
The data is just stupid....values of 1 to 5 we get a lot of 0's ok
that alright but 1-jan ?
we get colums that are formatted for number and then half way down
they are changed to text.

OR the famous ok now everything in red is ---- and everything in blue
is---------. WTF are these people thinking?

I could go on and on about excel and data collection.

but I have a question has anyone done a lot of testing and thought and
come up with some guidelines on importing so that you do get the data
you want and reformat what you don't get or get incorrectly?
Can you use xml or something to get the colors of cells and their
other attributes so that you can spot the changes made in rows that
cause errors?
Just for exapmle I found the color definitions on rows and colums but
nothing about individual cell formatting.

thanks big time for help, ideas, web pages or books that deal with
this type of thing
Jerry

Apr 12 '06 #1
Share this Question
Share on Google+
15 Replies


P: n/a
create import specifications - then Access won't try to "guess" what
datatypes you're importing.

Apr 12 '06 #2

P: n/a
Arno R spent a year and a half designing the system!! At every turn he
consulted with an "expert" named B. Quintal. The "expert" kept telling him
to export his data (hundreds of thousands of records) one field at a time.
"It works so it is correct way to do it" the expert kept insisting. At every
turn Arno R asked, "WTF now Bob?"
"sparks" <js******@swbell.net> wrote in message
news:7l********************************@4ax.com...
We get more and more data done in excel and then they want it imported
into access.
The data is just stupid....values of 1 to 5 we get a lot of 0's ok
that alright but 1-jan ?
we get colums that are formatted for number and then half way down
they are changed to text.

OR the famous ok now everything in red is ---- and everything in blue
is---------. WTF are these people thinking?

I could go on and on about excel and data collection.

but I have a question has anyone done a lot of testing and thought and
come up with some guidelines on importing so that you do get the data
you want and reformat what you don't get or get incorrectly?
Can you use xml or something to get the colors of cells and their
other attributes so that you can spot the changes made in rows that
cause errors?
Just for exapmle I found the color definitions on rows and colums but
nothing about individual cell formatting.

thanks big time for help, ideas, web pages or books that deal with
this type of thing
Jerry

Apr 12 '06 #3

P: n/a
Such an eloquent put down, but what has it to do with the question?

John... Visio MVP

"PC D" <fa***@email.com> wrote in message
news:Wf*****************@newsread3.news.atl.earthl ink.net...
Arno R spent a year and a half designing the system!! At every turn he
consulted with an "expert" named B. Quintal. The "expert" kept telling him
to export his data (hundreds of thousands of records) one field at a time.
"It works so it is correct way to do it" the expert kept insisting. At
every turn Arno R asked, "WTF now Bob?"



Apr 12 '06 #4

P: n/a
sparks wrote:
We get more and more data done in excel and then they want it imported
into access.
The data is just stupid....values of 1 to 5 we get a lot of 0's ok
that alright but 1-jan ?
we get colums that are formatted for number and then half way down
they are changed to text.

OR the famous ok now everything in red is ---- and everything in blue
is---------. WTF are these people thinking?

I could go on and on about excel and data collection.

but I have a question has anyone done a lot of testing and thought and
come up with some guidelines on importing so that you do get the data
you want and reformat what you don't get or get incorrectly?
Can you use xml or something to get the colors of cells and their
other attributes so that you can spot the changes made in rows that
cause errors?
Just for exapmle I found the color definitions on rows and colums but
nothing about individual cell formatting.

thanks big time for help, ideas, web pages or books that deal with
this type of thing
Jerry


Jerry, I realize that this doesn't help answer your questions, but
grooming data is always costly. It's time consuming, tedious and error
prone. Generally it is very much preferred to validate the data at the
point of entry. Any chance you could get them to enter the data
directly into an Access application for that purpose, then transfer the
data from Access to Access? That sort of thing is generally easy to
cost justify.

My 2 cents worth,

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.
Apr 12 '06 #5

P: n/a
"John Marshall, MVP" <la******@stonehenge.ca> wrote in
news:g7********************@magma.ca:
Such an eloquent put down, but what has it to do with the
question?

John... Visio MVP

"PC D" <fa***@email.com> wrote in message
news:Wf*****************@newsread3.news.atl.earthl ink.net...
Arno R spent a year and a half designing the system!! At
every turn he consulted with an "expert" named B. Quintal.
The "expert" kept telling him to export his data (hundreds of
thousands of records) one field at a time. "It works so it is
correct way to do it" the expert kept insisting. At every
turn Arno R asked, "WTF now Bob?"

Like PCD himself, it's totally irrelevant to the question.
And like PCD's answers to questions it's wrong.

But I will credit PCD for self-improvement. At least it's not a
wrong answer.
--
Bob Quintal

PA is y I've altered my email address.
Apr 13 '06 #6

P: n/a
Never mind John, let PC D be what he is, a pile of sh*.

I am afraid he forgot to take his pills again....
He is only mad because Bob called him a Pretty Crappy Developer in another thread.

Arno R
"John Marshall, MVP" <la******@stonehenge.ca> schreef in bericht news:g7********************@magma.ca...
Such an eloquent put down, but what has it to do with the question?

John... Visio MVP

"PC D" <fa***@email.com> wrote in message
news:Wf*****************@newsread3.news.atl.earthl ink.net...
Arno R spent a year and a half designing the system!! At every turn he
consulted with an "expert" named B. Quintal. The "expert" kept telling him
to export his data (hundreds of thousands of records) one field at a time.
"It works so it is correct way to do it" the expert kept insisting. At
every turn Arno R asked, "WTF now Bob?"




Apr 13 '06 #7

P: n/a
Well I understand the 3 points you have made and the ARE valid.
BUT these people know only one thing.
Excel is fast easy and I have it up all the time.
I just enter some data, move it where I want and if I need to move or
change anything I just do it.
I DONT HAVE TO MESS WITH YOU PEOPLE
when I want to add variables, or change the input of 1 to 5 to 1 to 99
I DO IT.
I CAN show my people that RED is something they need to look out for.
AND MOST OF ALL I CAN set it up the way I WANT and change it any time
I WANT and don't have to mess with your department.

That is the responses we are seeing more and more.
They keep asking why are you here?
You want to build a database and charge us $$$ then we have to wait
while you make modifications that cost us $$$.
I can do all this and it doesn't cost my department a dime.

This is what we are seeing more and more and there is NO WAY that they
will listen to anything we have to say.
We have one person that says I HATE ACCESS...now foxpro thats a
database.
ALL YOU DO is charge us for stuff and we have to bend over backwards
to get it to work the way we want.

( most of the time when we do a database it is give to them for
testing) 3-4 weeks later we here the data has been entered...NOW make
these changes, add these new variables and WTF do I have to go thru
every record and enter what you didn't do...MAN I HATE ACCESS

sorry about the long rant but we are a small and hated department. NO
MATTER what we do we are wrong and no matter how good the database is
there are people that JUST DONT WANT TO USE IT. for reasons above.

But after all is said and done here is the data.
WTF do you mean your department can't import it into sas for analysis
because of errors...ITS ALL RIGHT THERE

you f#$%ing guys suck...
=================================================
you can't win so we were trying to figure out a way to scan the excel
files for the stupid things they do and be able to convert it to
access tables and give them a database with their excel data so they
can continue on the right way.
(most of these data collections will go on for several years on and
off.)
We get the stuff at some point and are trying to get things back to
access.

AND SORRY FOR THE RANT I just get tired of these people and their
ways.

Jerry
On Wed, 12 Apr 2006 21:35:05 GMT, Randy Harris <pl****@send.no.spam>
wrote:
sparks wrote:
We get more and more data done in excel and then they want it imported
into access.
The data is just stupid....values of 1 to 5 we get a lot of 0's ok
that alright but 1-jan ?
we get colums that are formatted for number and then half way down
they are changed to text.

OR the famous ok now everything in red is ---- and everything in blue
is---------. WTF are these people thinking?

I could go on and on about excel and data collection.

but I have a question has anyone done a lot of testing and thought and
come up with some guidelines on importing so that you do get the data
you want and reformat what you don't get or get incorrectly?
Can you use xml or something to get the colors of cells and their
other attributes so that you can spot the changes made in rows that
cause errors?
Just for exapmle I found the color definitions on rows and colums but
nothing about individual cell formatting.

thanks big time for help, ideas, web pages or books that deal with
this type of thing
Jerry


Jerry, I realize that this doesn't help answer your questions, but
grooming data is always costly. It's time consuming, tedious and error
prone. Generally it is very much preferred to validate the data at the
point of entry. Any chance you could get them to enter the data
directly into an Access application for that purpose, then transfer the
data from Access to Access? That sort of thing is generally easy to
cost justify.

My 2 cents worth,


Apr 13 '06 #8

P: n/a
Oops, almost forgot ---

Arno R never consulted with John Marshall on this project because John
Marshall never makes an y contributions to the newsgroup.

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:g7********************@magma.ca...
Such an eloquent put down, but what has it to do with the question?

John... Visio MVP

"PC D" <fa***@email.com> wrote in message
news:Wf*****************@newsread3.news.atl.earthl ink.net...
Arno R spent a year and a half designing the system!! At every turn he
consulted with an "expert" named B. Quintal. The "expert" kept telling
him to export his data (hundreds of thousands of records) one field at a
time. "It works so it is correct way to do it" the expert kept insisting.
At every turn Arno R asked, "WTF now Bob?"


Apr 13 '06 #9

P: n/a
Meet Mr. B. Quintal!!!

His routine for importing hundreds of thousands of records one field at a
time just ended right before he responded!!
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"John Marshall, MVP" <la******@stonehenge.ca> wrote in
news:g7********************@magma.ca:
Such an eloquent put down, but what has it to do with the
question?

John... Visio MVP

"PC D" <fa***@email.com> wrote in message
news:Wf*****************@newsread3.news.atl.earthl ink.net...
Arno R spent a year and a half designing the system!! At
every turn he consulted with an "expert" named B. Quintal.
The "expert" kept telling him to export his data (hundreds of
thousands of records) one field at a time. "It works so it is
correct way to do it" the expert kept insisting. At every
turn Arno R asked, "WTF now Bob?"

Like PCD himself, it's totally irrelevant to the question.
And like PCD's answers to questions it's wrong.

But I will credit PCD for self-improvement. At least it's not a
wrong answer.
--
Bob Quintal

PA is y I've altered my email address.

Apr 13 '06 #10

P: n/a
To all --

Meet Arno R.

He never makes a contribution to this newsgroup. All he does is demonstrate
to all newsgroup readers what an obsessed A$$ he is by continually posting
his brand of garbage in the newsgroups.

PCD
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..
Never mind John, let PC D be what he is, a pile of sh*.

I am afraid he forgot to take his pills again....
He is only mad because Bob called him a Pretty Crappy Developer in another
thread.

Arno R
"John Marshall, MVP" <la******@stonehenge.ca> schreef in bericht
news:g7********************@magma.ca...
Such an eloquent put down, but what has it to do with the question?

John... Visio MVP

"PC D" <fa***@email.com> wrote in message
news:Wf*****************@newsread3.news.atl.earthl ink.net...
Arno R spent a year and a half designing the system!! At every turn he
consulted with an "expert" named B. Quintal. The "expert" kept telling
him
to export his data (hundreds of thousands of records) one field at a
time.
"It works so it is correct way to do it" the expert kept insisting. At
every turn Arno R asked, "WTF now Bob?"


Apr 13 '06 #11

P: n/a
"PC D" <fa***@email.com> wrote in
news:pK*****************@newsread3.news.atl.earthl ink.net:
Meet Mr. B. Quintal!!!

His routine for importing hundreds of thousands of records one
field at a time just ended right before he responded!!

No, that's your routine for transferring hundreds of thousands
of records. And the user now has 6 different Excel files to
merge manually.

I ran a test case using the method I suggested, which overcame
some genuine constraints about exceeding the 64Ki row limit of
Excel,
and it did 100,000 rows of 5 fields in under one hour.

One hour of the original poster's time to get the data the way
he wanted it is far better than the unworkable solution you
proposed.

And one hour is certainly less time than you've spent whining
about hte solution.

PCD, Go away!
Don't go away mad, but just go away.

--
Bob Quintal

PA is y I've altered my email address.
Apr 13 '06 #12

P: n/a
Who-eeee!!! 1 hour.

I'll bet every newsgroup reader is going to immediately start using your
recommendation to export hundreds of thousands of records one field at a
time. Oh my aching processor!!! But of course it works so it's got to be the
right way.

FYI, I use my method to export almost six hundred thousand records on a 1.6
Mhz machine and it takes only a couple of minutes!!
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135...
"PC D" <fa***@email.com> wrote in
news:pK*****************@newsread3.news.atl.earthl ink.net:
Meet Mr. B. Quintal!!!

His routine for importing hundreds of thousands of records one
field at a time just ended right before he responded!!

No, that's your routine for transferring hundreds of thousands
of records. And the user now has 6 different Excel files to
merge manually.

I ran a test case using the method I suggested, which overcame
some genuine constraints about exceeding the 64Ki row limit of
Excel,
and it did 100,000 rows of 5 fields in under one hour.

One hour of the original poster's time to get the data the way
he wanted it is far better than the unworkable solution you
proposed.

And one hour is certainly less time than you've spent whining
about hte solution.

PCD, Go away!
Don't go away mad, but just go away.

--
Bob Quintal

PA is y I've altered my email address.

Apr 13 '06 #13

P: n/a
"PC D" <fa***@email.com> wrote in
news:yV*****************@newsread2.news.atl.earthl ink.net:
Who-eeee!!! 1 hour.

I'll bet every newsgroup reader is going to immediately start
using your recommendation to export hundreds of thousands of
records one field at a time. Oh my aching processor!!! But of
course it works so it's got to be the right way.
Stop being stupid. I made the reccomendation that I made because it
specifically handled the several constraints that the original
poster listed. In other cases, with different constraints, I'd
provide different answers.
FYI, I use my method to export almost six hundred thousand
records on a 1.6 Mhz machine and it takes only a couple of
minutes!!


Good for you. Publish the code (so we can verify your assertions)
or go away. Don't go away mad,just go away.

--
Bob Quintal

PA is y I've altered my email address.
Apr 13 '06 #14

P: n/a
You're the one who made the grossly stupid recommendation to export hundreds
of thousands of records one field at a time!!!

The only constraint was that an Excel worksheet can only hold 64,000 rows so
the records had to be exported to multple worksheets. That constraint
certanly doe not force an export to be made one field at a time!! Ask Arno R
or John Marshall! Of course neither of them make any contributions to the
newsgroup so they wouldn't know.

I was just thinking. One hundred thousand records in anhour exported one
field at a time took one hour. So, six hundred thousand records exported one
field at a time would take six hours. Who-eee! Oh my aching processor. Every
newsgroup readers must be implementing your recommendation that takes hours
when it can be done in minutes!!!!
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn*********************@207.35.177.135...
"PC D" <fa***@email.com> wrote in
news:yV*****************@newsread2.news.atl.earthl ink.net:
Who-eeee!!! 1 hour.

I'll bet every newsgroup reader is going to immediately start
using your recommendation to export hundreds of thousands of
records one field at a time. Oh my aching processor!!! But of
course it works so it's got to be the right way.


Stop being stupid. I made the reccomendation that I made because it
specifically handled the several constraints that the original
poster listed. In other cases, with different constraints, I'd
provide different answers.

FYI, I use my method to export almost six hundred thousand
records on a 1.6 Mhz machine and it takes only a couple of
minutes!!


Good for you. Publish the code (so we can verify your assertions)
or go away. Don't go away mad,just go away.

--
Bob Quintal

PA is y I've altered my email address.

Apr 14 '06 #15

P: n/a
Hi, Jerry.
AND SORRY FOR THE RANT I just get tired of these people and their
ways.
Your organization's problem is a managerial one. And it's very common in
businesses. However, the IT department's relationship with the other
departments in the organization needs to become one of mutual cooperation,
not the current adversarial one.
But after all is said and done here is the data.
WTF do you mean your department can't import it into sas for analysis
because of errors...ITS ALL RIGHT THERE
As you know, a lot of it is garbage data, because the data wasn't validated
when it was entered. Garbage data can be worse than no data. I worked for
a corporation that had long been managing its manufacturing at one plant
with an outdated, inflexible mainframe application, various spreadsheets,
and records on paper, and whose main customer suddenly got its budget cut to
a trickle and was looking for cheaper alternatives, i.e., a new
manufacturing contractor. The corporation decided that BI would streamline
the management of operations at this plant for their main customer and would
help them attract new customers with their lower resulting costs of doing
business. The plant didn't yet have the budget for the $600K+ Cognos
solution the management had decided on, but they expected to have the money
in four months. Against the advice of IT, as a stop-gap measure till the
Cognos solution could come online, management decided to purchase and
implement Actuate for $140K+ so that they could start showing their main
customer how much cheaper such a system could be and start making better
business decisions as quickly as possible.

Of course, in the rush to implement the Actuate system, no one had time to
clean up the data, so the data went into the system pretty much as-is.
Major business decisions were made based on this system, some of which later
proved to be very poor decisions. The Cognos solution required that the
data be stored in a different structure, which also demanded that the data
be cleaned up beforehand, so a major effort was made to do so. When the
Cognos system eventually came online, it was discovered that some of the
earlier business decisions made with the first system were the wrong
decisions which had cost the corporation many hundreds of thousands of
dollars in unnecessary costs. If the corporation had spent the time
cleaning up the data and manipulating it into the Cognos data structure
instead of implementing and paying for the Actuate stop-gap solution, about
40 productive employees could have remained productive -- and employed --
because the corporation had to cut employees from the payroll in order to
pay for the first BI system and other ongoing costs, which were exacerbated
by the poor business decisions based upon the results produced by the first
BI system.

Please note that I'm not saying that Actuate is inferior to Cognos. I'm
saying that we couldn't see how much garbage data we actually had until we
tried using it with Cognos, which forced us to clean up the garbage data
first. Had they but known, management wouldn't have made certain business
decisions. But hindsight is 20/20.
They keep asking why are you here?
You want to build a database and charge us $$$ then we have to wait
while you make modifications that cost us $$$.
I can do all this and it doesn't cost my department a dime.
It appears to be "free" now, but much of it is useless in another tool, SAS,
and probably useless for other departments who may need the same data, but
in a differently structured format, and must therefore create redundant, but
parallel, systems. Since much of the department-specific data structure is
incompatible with SAS, what will it cost to make it compliant or to re-input
the same data with the correct format into the SAS system later? What does
it cost the departments to build redundant data systems in spreadsheets?
What does it cost to reconcile discrepancies in these multiple systems that
supposedly use/produce the same data, but occasionally don't?

Those costs aren't free, and when push comes to shove, jobs will be cut to
pay for it. The people claiming, "I can do all this and it doesn't cost my
department a dime," don't want to be the ones selected to walk out that door
because they weren't efficient enough in their jobs, now do they? I can
assure you that the 40 or so people that the corporation I worked for didn't
choose to have their jobs cut to pay for a system that would make other
employees more productive. They had no choice, and neither will the workers
at your organization. The workers at your organization need to do
everything they can to be as productive as possible so that push never comes
to shove.
sorry about the long rant but we are a small and hated department. NO
MATTER what we do we are wrong and no matter how good the database is
there are people that JUST DONT WANT TO USE IT. for reasons above.
Organizational management needs to get behind the IT department. The most
effective way I've seen this happen was to show the managers in the other
departments what's going on, how it's impacting organizational efficiency,
and what the adverse consequences are. Those managers are the ones who will
then motivate their people to work for the good of the whole organization,
instead of just their own department. In our case, I set up five-minute
demos to show some of the extremes, but they got the picture very quickly
and most of the people who had been very reluctant suddenly became very
cooperative. The fact that 60 to 100+ more people they'd worked with for
years were walking out the gate for the last time every few weeks was a huge
motivating factor to become visibly more efficient and more cooperative,
too, so I realize that it wasn't just my demos that made the difference, but
they helped nudge attitudes in a more productive direction.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"sparks" <js******@swbell.net> wrote in message
news:79********************************@4ax.com... Well I understand the 3 points you have made and the ARE valid.
BUT these people know only one thing.
Excel is fast easy and I have it up all the time.
I just enter some data, move it where I want and if I need to move or
change anything I just do it.
I DONT HAVE TO MESS WITH YOU PEOPLE
when I want to add variables, or change the input of 1 to 5 to 1 to 99
I DO IT.
I CAN show my people that RED is something they need to look out for.
AND MOST OF ALL I CAN set it up the way I WANT and change it any time
I WANT and don't have to mess with your department.

That is the responses we are seeing more and more.
They keep asking why are you here?
You want to build a database and charge us $$$ then we have to wait
while you make modifications that cost us $$$.
I can do all this and it doesn't cost my department a dime.

This is what we are seeing more and more and there is NO WAY that they
will listen to anything we have to say.
We have one person that says I HATE ACCESS...now foxpro thats a
database.
ALL YOU DO is charge us for stuff and we have to bend over backwards
to get it to work the way we want.

( most of the time when we do a database it is give to them for
testing) 3-4 weeks later we here the data has been entered...NOW make
these changes, add these new variables and WTF do I have to go thru
every record and enter what you didn't do...MAN I HATE ACCESS

sorry about the long rant but we are a small and hated department. NO
MATTER what we do we are wrong and no matter how good the database is
there are people that JUST DONT WANT TO USE IT. for reasons above.

But after all is said and done here is the data.
WTF do you mean your department can't import it into sas for analysis
because of errors...ITS ALL RIGHT THERE

you f#$%ing guys suck...
=================================================
you can't win so we were trying to figure out a way to scan the excel
files for the stupid things they do and be able to convert it to
access tables and give them a database with their excel data so they
can continue on the right way.
(most of these data collections will go on for several years on and
off.)
We get the stuff at some point and are trying to get things back to
access.

AND SORRY FOR THE RANT I just get tired of these people and their
ways.

Jerry
On Wed, 12 Apr 2006 21:35:05 GMT, Randy Harris <pl****@send.no.spam>
wrote:
sparks wrote:
We get more and more data done in excel and then they want it imported
into access.
The data is just stupid....values of 1 to 5 we get a lot of 0's ok
that alright but 1-jan ?
we get colums that are formatted for number and then half way down
they are changed to text.

OR the famous ok now everything in red is ---- and everything in blue
is---------. WTF are these people thinking?

I could go on and on about excel and data collection.

but I have a question has anyone done a lot of testing and thought and
come up with some guidelines on importing so that you do get the data
you want and reformat what you don't get or get incorrectly?
Can you use xml or something to get the colors of cells and their
other attributes so that you can spot the changes made in rows that
cause errors?
Just for exapmle I found the color definitions on rows and colums but
nothing about individual cell formatting.

thanks big time for help, ideas, web pages or books that deal with
this type of thing
Jerry


Jerry, I realize that this doesn't help answer your questions, but
grooming data is always costly. It's time consuming, tedious and error
prone. Generally it is very much preferred to validate the data at the
point of entry. Any chance you could get them to enter the data
directly into an Access application for that purpose, then transfer the
data from Access to Access? That sort of thing is generally easy to
cost justify.

My 2 cents worth,

Apr 17 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.