Importing Multiple Excel fiels to one Access Database

[aaearhart]

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]

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

 

[John Nurick]

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).

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

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]

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.

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).

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

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]

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".

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.

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).

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



:

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