|
Home > Archive > MS Access Database with External Data > November 2005 > Importing Multiple Excel fiels to one Access Database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Importing Multiple Excel fiels to one Access Database
|
|
| aaearhart 2005-11-15, 8:25 pm |
| Hi - I've seen this question before, but not answered.
I have three excel spreadsheets like this:
alpha.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
beta.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
gamma.xls
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
as you can see, all spreadsheets have identical colums. the data within the
spreadsheets differs, but the fieldnames and data types are identical.
my access table is like this:
t_MyTable
[Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
again, exact same field names and data types.
i can successfully import the first spreadsheet; let's use alpha.xls for
this example. i can then open the table and see, that yes, all my data is
there and correct. then, when i try to import a second spreadsheet, let's use
beta.xls, access errors out saying "An error occurred trying to import file
'beta.xls'. The file was not imported.
now, i've tried saving a copy of alpha.xls under the name omega.xls
(identical EVERYTHING), and still received this error.
how do i import multiple files in to the same access table?
thanks!
/amelia
| |
| aaearhart 2005-11-15, 8:25 pm |
| I'd like to add some complication to the issue:
my actual fields are like so:
who, what, 1-NOV, 2-NOV, 3-NOV....30-NOV
i'm realising that the fact that my field names are dates (and in dd-mmm
format in the excel spreadsheet) is important.
carry on :)
"aaearhart" wrote:
> Hi - I've seen this question before, but not answered.
>
> I have three excel spreadsheets like this:
>
> alpha.xls
> [Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
>
> beta.xls
> [Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
>
> gamma.xls
> [Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
>
> as you can see, all spreadsheets have identical colums. the data within the
> spreadsheets differs, but the fieldnames and data types are identical.
>
> my access table is like this:
>
> t_MyTable
> [Field1] [Field2] [Field3] [Field4] [Field5] [Field6]
>
> again, exact same field names and data types.
>
> i can successfully import the first spreadsheet; let's use alpha.xls for
> this example. i can then open the table and see, that yes, all my data is
> there and correct. then, when i try to import a second spreadsheet, let's use
> beta.xls, access errors out saying "An error occurred trying to import file
> 'beta.xls'. The file was not imported.
>
> now, i've tried saving a copy of alpha.xls under the name omega.xls
> (identical EVERYTHING), and still received this error.
>
> how do i import multiple files in to the same access table?
>
> thanks!
> /amelia
| |
| John Nurick 2005-11-16, 3:25 am |
| You say the field names are identical, so I'll assume that all the
worksheets refer to the same month.
The first thing I'd try would be to change the field names in the
worksheet from dates to text (a date value in Excel is of course a
number).
On Tue, 15 Nov 2005 15:34:05 -0800, aaearhart
< aaearhart@discussion
s.microsoft.com> wrote:
[color=darkred]
>I'd like to add some complication to the issue:
>
>my actual fields are like so:
>
>who, what, 1-NOV, 2-NOV, 3-NOV....30-NOV
>
>i'm realising that the fact that my field names are dates (and in dd-mmm
>format in the excel spreadsheet) is important.
>
>carry on :)
>
>
>
>"aaearhart" wrote:
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
| |
| aaearhart 2005-11-18, 1:24 pm |
| I could change the Excel dates in to text no problem. but then the field name
is a number, not a date. then when i work in reports to analyse the data,
grouping by week and whatnot, it's impossible since i no longer have dates
for field names, but numbers.
if only ms would produce a timesheet or calendar wizard. it would help out
so much.
"John Nurick" wrote:
> You say the field names are identical, so I'll assume that all the
> worksheets refer to the same month.
>
> The first thing I'd try would be to change the field names in the
> worksheet from dates to text (a date value in Excel is of course a
> number).
>
>
>
> On Tue, 15 Nov 2005 15:34:05 -0800, aaearhart
> < aaearhart@discussion
s.microsoft.com> wrote:
>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
>
| |
| John Nurick 2005-11-18, 8:25 pm |
| I think we must be at cross purposes. I was suggesting that you replace
(e.g.) the date value 38674 (today) formatted as 18-NOV with the text
value "18-NOV".
On Fri, 18 Nov 2005 10:56:02 -0800, aaearhart
< aaearhart@discussion
s.microsoft.com> wrote:
[color=darkred]
>I could change the Excel dates in to text no problem. but then the field name
>is a number, not a date. then when i work in reports to analyse the data,
>grouping by week and whatnot, it's impossible since i no longer have dates
>for field names, but numbers.
>
>if only ms would produce a timesheet or calendar wizard. it would help out
>so much.
>
>
>
>"John Nurick" wrote:
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
|
|
|