Importing Excel Data

[aaron.kempf]

so I'm importing data that is mainly text.. but some large integers...

and the large integers keep on getting changed to scientific notation.

is there somewhere that i can tell windows or access-- or something--
to NEVER EVER EVER use scientific notation?

or is this an excel thing?

-aaron

 

[peregenem]

Yes, it's an Excel thing: Excel Jet doesn't have an INTEGER data type.

 

[aaron.kempf]

i just think that excel is total crap.

i mean- people use it all the time; and it's like the worst repository
for data ever invented..

 

[aaron.kempf]

i just think that excel is total crap.

i mean- people use it all the time; and it's like the worst repository
for data ever invented..

 

[peregenem]

Good for you: tell your client/employer that you are not prepared to
work with Excel data and that you will find yourself another
contract/job if necessary. I wish you the best with that. You've got to
know your limits. Personally, I won't deal with Office versions earlier
than Office2000; if they wont upgrade then it's Hej då.

But I think you are looking at things slightly skewed. Excel data is
fine if you are using it in the Excel app. But you are using it from a
different app (Jet) and it's reasonable for the designer of the
Excel-Jet interface to have made compromises. If you want an Excel view
of Excel data then use the Excel app e.g. via automation.

Also, it sounds likely the designer of your particular spreadsheet did
not have the Jet interface in mind. Did they create the Excel table
using Jet's SQL Data Definition Language (DDL) i.e. CREATE TABLE? If
not then it would seem you are using the spreadsheet in a way which was
not originally intended and you will suffer the consequences.

 

[aaron.kempf]

no they didnt use the create table ddl..

will that actually prevent you from storing anything in an number field
that isnt a number?

like i can specify a long int-- like in Access?

that would rock.

i just think that management-- throughout the world-- needs to be
kicked to the curb. all they do is screw up the ability to write
databases.

find me a manager that is decent with databases-- and i'll go to work
for them for free.

i just have been through 3 contracts at microsoft; and everyone over
there is infected with the Excel bug and i think that it's disgusting..


i would rather use access one-armed then have a half-dozen dorks making
the same XLS week in and week out.

it's just time for MS to start doing something about these lame excel
dorks.

i mean-- they force the IT side to learn 20 new things every year-- why
aren't there comparable certifications for excel?

i just think that 50% of the workplace in corporate america uses Excel
all day long.

and i think that it is absolutely disgusting.

why is it that people with no expertise are allowed to make
mission-critical spreadsheets?

arent you guys tired of making the same XLS week in and week out?

-aaron

 

[peregenem]

no they didnt use the create table ddl..

will that actually prevent you from storing anything in an number field
that isnt a number?

like i can specify a long int-- like in Access?

I told you already, Excel Jet doesn't have an INTEGER data type. It's
only numeric type is FLOAT (double precision floating point). So using
FLOAT in DDL:

CREATE TABLE [Excel 8.0;DATABASE=C:\test2.xls;].Aaron (test_column
FLOAT);
INSERT INTO [Excel 8.0;DATABASE=C:\test2.xls;].Aaron VALUES ('two');
Data type mismatch in criteria expression.

The INSERT fails because the value 'two' is non-numeric. Do you see how
Excel Jet data typing works if you use it properly.

We've determined that your spreadsheet was not designed to be used as a
Jet data source. Aaron, stop using it as if it were a proper database.
Use a spreadsheet as a spreadsheet. Learn some Excel VBA and you'll be
a happier person. If you are being asked to do something against your
principles then quit, people will respect your for that and your will
gain self respect too. Do you see yourself as a DBA (database
administrator)? Then go for it, don't bother with these spreadsheets if
they make you unhappy.

find me a manager that is decent with databases-- and i'll go to work
for them for free.

i just think that 50% of the workplace in corporate america uses Excel
all day long.

I am a manager and I'm more than decent at database and spreadsheets
and spreadsheets-as-databases. In my organization, we use Excel
properly but we are not an American corporation. Why do you think this
is an American problem?

You've lost me with the rest of your rant though. Good to get it off
your chest, eh?