Flat File Database, Multiple Excel files into one Access Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am trying to creat a Flat File Database as opposed to a Relational
Database. I have 34 Excel Spreadsheets I need to combine and the first three
spreadsheets alone exceed Excel's row maximum of appx 65,000 rows. So I
can't use Excel to analyze all this data. These 34 Excel spreadsheets were
all downloaded from the same report and are the same in terms of layout and
column headings, etc. Only the data differs as they are from 34 different
months.

I can get as far as creating an Access table with the first spreadsheet for
my first month of data. I am having difficulty adding the data from the
remaining 33 Excel spreadsheets to the first, same Access table.

SOS. Please help! LOL!
 
Have you considered moving to Excel 2007? It now handles 1 million rows by
16,000 columns

How are you trying to import the data from Excel? If you're doing it
manually, the wizard gives you a choice of table name into which to import.
If you're doing it using TransferSpreadsheet, you have to specify the table
name.
 
Hi Doug. I'm at work and we have Office 2003. Here is what I did. I
converted the first excel spreadsheet into access with no problem. Then I
took the following steps:
Open Access
Click once on file name
Hit File, get external data, import, select file types .xls, double click on
excel file I want to add to access file and go thru import wizard.
I indicate that first row of data contains column headings, which I have
made identical to those in access including adding a column A called ID for
the primary key. But the column has no data in it. Could that be the
problem?
I then try to store the data in an existing file by selecting it and
continue with the wizard. I end with an error message saying an error
occurred while trying to import the data and the file was not imported.
I feel like this should be so easy. Am I missing an obvious step? Can I
cut and past the data from excel into Access?

Thanks for your help.
Chris
 
When you imported the first file, did you walk all the way to the end of the
wizard, or did you click on Finish before the end? If you did walk all the
way through, did you tell Access to add a field as a primary key? If either
of those are true, try importing again, telling Access not to create a
primary key for you.
 
Doug,
Thanks for your help. I figured it out. My Field headings had spaces
between words. Once I made field headings in accces exactly the same as
excel column headings with no spaces between words the wizard walked me thru
it. I'm just now loading all 34 files into the access table.
Thanks so much again for your help.
Chris
 
NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1
 
these newsgroups are for the FREE exchange of ideas, information, and
assistance. this is absolutely NOT the place to troll for business, such
postings are not welcome, and they make such a bad impression that it can
only hurt your reputation in the Access community.

if you want to post actual advice and assistance here in the newsgroups for
all to benefit from, or post questions of your own, then your participation
will be welcomed.


NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1


learningaccess said:
Hi,
I am trying to creat a Flat File Database as opposed to a Relational
Database. I have 34 Excel Spreadsheets I need to combine and the first three
spreadsheets alone exceed Excel's row maximum of appx 65,000 rows. So I
can't use Excel to analyze all this data. These 34 Excel spreadsheets were
all downloaded from the same report and are the same in terms of layout and
column headings, etc. Only the data differs as they are from 34 different
months.

I can get as far as creating an Access table with the first spreadsheet for
my first month of data. I am having difficulty adding the data from the
remaining 33 Excel spreadsheets to the first, same Access table.

SOS. Please help! LOL!
Click to expand...
Click to expand...
 
Back
Top