|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hello,
I have several Excel spreadsheets. One column is numeric. I have set it to
numeric through the "Format Cells...". When I try to import it, either from
the excel workbook or a tab delimited file, the numeric values are eith:
1. arbitrarily excluded (some get in, others don't)
2. arbitrarily quoted (same thing as above)
It needs to be numeric. That is why the number keeys were used to enter the
data and why the poor users chose to use Excel.
Questions -
1. How can we get Excel to recognize number as number?
2. How can we get Access to recognize numbers as numbers?
Now that I have asked questions for which no useful answer will likely be
forthcoming (in at least a timely manner), might I offer a few comments?
This is a straight forward business function. I have been working with
computers for a long long time. Some of the worst times have been spent
working with Microsoft solutions for users (the dev tools are mostly pretty
good). I have to wonder why these problems are found in Release code? Why
am I sitting here, looking at a column of numbers that are not being treated
like numbers?
|
|
One approach might be to import whatever you can get into a temporary table
in Access, then run queries that "coerce" the data into a final data
type/format in more permanent tables. I use "tables" because Excel data is
usually "flat", where a well-normalized Access application uses relational
data (table) structures.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"mklapp" <mklapp[ at ]discussions.microsoft.com> wrote in message
news:1F57A5E9-FC26-4063-8706-875A7CD4D3EA[ at ]microsoft.com...
[Quoted Text] > Hello,
>
> I have several Excel spreadsheets. One column is numeric. I have set it
> to
> numeric through the "Format Cells...". When I try to import it, either
> from
> the excel workbook or a tab delimited file, the numeric values are eith:
>
> 1. arbitrarily excluded (some get in, others don't)
> 2. arbitrarily quoted (same thing as above)
>
> It needs to be numeric. That is why the number keeys were used to enter
> the
> data and why the poor users chose to use Excel.
>
> Questions -
>
> 1. How can we get Excel to recognize number as number?
> 2. How can we get Access to recognize numbers as numbers?
>
> Now that I have asked questions for which no useful answer will likely be
> forthcoming (in at least a timely manner), might I offer a few comments?
>
> This is a straight forward business function. I have been working with
> computers for a long long time. Some of the worst times have been spent
> working with Microsoft solutions for users (the dev tools are mostly
> pretty
> good). I have to wonder why these problems are found in Release code?
> Why
> am I sitting here, looking at a column of numbers that are not being
> treated
> like numbers?
>
>
>
>
|
|
Actually, that was pretty close. I change the column to Text and loaded the
rows. Used a Find and replace to remove quotation marks ( one at a time.
The command would not get both in the same 'cell'. Had to redefine the data
type a couple of times because Access kept truncating the cents.
I conditioned the Excel data into a nicely normalized table on each sheet
and followed what anyone would have called due diligence in preparing the
sheets for import. The frustrating thing was the indeterminate behavior of
Access. There was absolutely no condition that was useful in predicting or
controlling the behavior. That may be acceptable for some types of fantasy
AI but NOT for a database or spreadsheet application.
"Jeff Boyce" wrote:
[Quoted Text] > One approach might be to import whatever you can get into a temporary table
> in Access, then run queries that "coerce" the data into a final data
> type/format in more permanent tables. I use "tables" because Excel data is
> usually "flat", where a well-normalized Access application uses relational
> data (table) structures.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "mklapp" <mklapp[ at ]discussions.microsoft.com> wrote in message
> news:1F57A5E9-FC26-4063-8706-875A7CD4D3EA[ at ]microsoft.com...
> > Hello,
> >
> > I have several Excel spreadsheets. One column is numeric. I have set it
> > to
> > numeric through the "Format Cells...". When I try to import it, either
> > from
> > the excel workbook or a tab delimited file, the numeric values are eith:
> >
> > 1. arbitrarily excluded (some get in, others don't)
> > 2. arbitrarily quoted (same thing as above)
> >
> > It needs to be numeric. That is why the number keeys were used to enter
> > the
> > data and why the poor users chose to use Excel.
> >
> > Questions -
> >
> > 1. How can we get Excel to recognize number as number?
> > 2. How can we get Access to recognize numbers as numbers?
> >
> > Now that I have asked questions for which no useful answer will likely be
> > forthcoming (in at least a timely manner), might I offer a few comments?
> >
> > This is a straight forward business function. I have been working with
> > computers for a long long time. Some of the worst times have been spent
> > working with Microsoft solutions for users (the dev tools are mostly
> > pretty
> > good). I have to wonder why these problems are found in Release code?
> > Why
> > am I sitting here, looking at a column of numbers that are not being
> > treated
> > like numbers?
> >
> >
> >
> >
>
>
>
|
|
BTW - Thanks for your response.
"Jeff Boyce" wrote:
[Quoted Text] > One approach might be to import whatever you can get into a temporary table
> in Access, then run queries that "coerce" the data into a final data
> type/format in more permanent tables. I use "tables" because Excel data is
> usually "flat", where a well-normalized Access application uses relational
> data (table) structures.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "mklapp" <mklapp[ at ]discussions.microsoft.com> wrote in message
> news:1F57A5E9-FC26-4063-8706-875A7CD4D3EA[ at ]microsoft.com...
> > Hello,
> >
> > I have several Excel spreadsheets. One column is numeric. I have set it
> > to
> > numeric through the "Format Cells...". When I try to import it, either
> > from
> > the excel workbook or a tab delimited file, the numeric values are eith:
> >
> > 1. arbitrarily excluded (some get in, others don't)
> > 2. arbitrarily quoted (same thing as above)
> >
> > It needs to be numeric. That is why the number keeys were used to enter
> > the
> > data and why the poor users chose to use Excel.
> >
> > Questions -
> >
> > 1. How can we get Excel to recognize number as number?
> > 2. How can we get Access to recognize numbers as numbers?
> >
> > Now that I have asked questions for which no useful answer will likely be
> > forthcoming (in at least a timely manner), might I offer a few comments?
> >
> > This is a straight forward business function. I have been working with
> > computers for a long long time. Some of the worst times have been spent
> > working with Microsoft solutions for users (the dev tools are mostly
> > pretty
> > good). I have to wonder why these problems are found in Release code?
> > Why
> > am I sitting here, looking at a column of numbers that are not being
> > treated
> > like numbers?
> >
> >
> >
> >
>
>
>
|
|
Actually, I was recommending doing the work inside of Access, importing all
the "raw" Excel data first.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"mklapp" <mklapp[ at ]discussions.microsoft.com> wrote in message
news:2225370E-85CE-4E77-B3CC-E28E9F99962B[ at ]microsoft.com...
[Quoted Text] > Actually, that was pretty close. I change the column to Text and loaded
> the
> rows. Used a Find and replace to remove quotation marks ( one at a time.
> The command would not get both in the same 'cell'. Had to redefine the
> data
> type a couple of times because Access kept truncating the cents.
>
> I conditioned the Excel data into a nicely normalized table on each sheet
> and followed what anyone would have called due diligence in preparing the
> sheets for import. The frustrating thing was the indeterminate behavior
> of
> Access. There was absolutely no condition that was useful in predicting
> or
> controlling the behavior. That may be acceptable for some types of
> fantasy
> AI but NOT for a database or spreadsheet application.
>
> "Jeff Boyce" wrote:
>
>> One approach might be to import whatever you can get into a temporary
>> table
>> in Access, then run queries that "coerce" the data into a final data
>> type/format in more permanent tables. I use "tables" because Excel data
>> is
>> usually "flat", where a well-normalized Access application uses
>> relational
>> data (table) structures.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "mklapp" <mklapp[ at ]discussions.microsoft.com> wrote in message
>> news:1F57A5E9-FC26-4063-8706-875A7CD4D3EA[ at ]microsoft.com...
>> > Hello,
>> >
>> > I have several Excel spreadsheets. One column is numeric. I have set
>> > it
>> > to
>> > numeric through the "Format Cells...". When I try to import it, either
>> > from
>> > the excel workbook or a tab delimited file, the numeric values are
>> > eith:
>> >
>> > 1. arbitrarily excluded (some get in, others don't)
>> > 2. arbitrarily quoted (same thing as above)
>> >
>> > It needs to be numeric. That is why the number keeys were used to
>> > enter
>> > the
>> > data and why the poor users chose to use Excel.
>> >
>> > Questions -
>> >
>> > 1. How can we get Excel to recognize number as number?
>> > 2. How can we get Access to recognize numbers as numbers?
>> >
>> > Now that I have asked questions for which no useful answer will likely
>> > be
>> > forthcoming (in at least a timely manner), might I offer a few
>> > comments?
>> >
>> > This is a straight forward business function. I have been working with
>> > computers for a long long time. Some of the worst times have been
>> > spent
>> > working with Microsoft solutions for users (the dev tools are mostly
>> > pretty
>> > good). I have to wonder why these problems are found in Release code?
>> > Why
>> > am I sitting here, looking at a column of numbers that are not being
>> > treated
>> > like numbers?
>> >
>> >
>> >
>> >
>>
>>
>>
|
|
|