Full Version: Importing a large amount of data from Excel
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
pmarley2
Hi all,
I am completely new to Access and really don't know the first thing about it.
I have close to 200 spreadsheets in CSV format, each of which has 6965 rows and 34 columns of data. These 200 files are split up into six folders, name January thru June in particular directory.
I want to import everything into Access so that I can analyze all the data simulataneously. Is there a way to write a macro or something so that I can automate all of the imports? I want to combine all 200 excel sheets into one sheet in access, so that the first sheet is at the top, and the rest of the sheets are appended below the first sheet.
To give you a scope of how new I am to access, I tried copying one cell and pasting its contects to entire column, but I couldn't figure out how...

Thanks
rbianco
if the files are truly in csv format (with all of the data on the 1st sheet of the work book - csv files only permit data on the 1st tab when viewed in excel) and if all of the files are in the exact same format with the same fields in the same order...
When you can place all of these files in one directory folder, use your command prompt (or create a bat file) and copy all of the data into one massive csv using:
>MyDriveLetter:
>cd myfulldirectorypath\properly\entered
>copy *.csv mybig.csv
then you can link this one massive csv into your access database. the link table manager can assist with this
allieanne
I don't know how to write the macro, but I do a lot of csv importing.
On the toolbar, File-Get External Data- Import
And then follow the directions, make sure you specify the type of data you want it to be (ie a number as a number not text).
Import the first sheet as a new sheet, and then for each sheet after, you import to an "existing sheet".
Its not pretty, and 200 sheets will be long and tedious.
(from not an ACCESS expert at all)
rbianco
OK, given the numbers you are talking about (6965 rows * 200 sheets ~ 1.4 million records!) I recommend not importing this data but rather linking this data

follow me on this and you can have this done in a few minutes:

What is the EXACT server file path where your data resides?

for example:
U:\ACCOUNTING\SHARED\DATA\JAN\200901.csv
Edited by: rbianco on Thu Aug 6 12:40:44 EDT 2009.
rbianco
Assuming your current directory structure is something like this:
C:\MyDataFolder\January
C:\MyDataFolder\February
C:\MyDataFolder\March…
Create a new (temporary) directory:
C:\MyDataFolder\Temp
Move (or Copy) all of your csv files into this new directory:
Open up a notepad and paste in the following:
cd C:\MyDataFolder\Temp\
copy *.csv mybig.csv
Save this file and name it MyTemp.bat and place it in:
C:\MyDataFolder\
To run the bat file go through Windows Explorer. Drive to C:\MyDataFolder\MyTemp.bat
You can either double-click it, or right mouse click and select Run...from the shortcut menu.
This bat file will merge all of your csv files into ONE MASSIVE file.
When the process is complete, you can move your files back, or if you copied them just delete the copies.
Now go to Access and select File>Get External Data>Link
The wizard (which you are already familiar with) will walk you through the required steps. When the wizard is complete you will have the data residing “virtually” within you database. This has the added benefit of not bloating the size of your db. You can build queries off of this linked table just as you would a resident table.
pmarley2
Hi rbianco and allieanne,
Thank you very much for your help! I've followed your directions and everything seems to work.
Yes, the complete spreadsheet I have imported has 1270667 rows, and the DB file ends up being about 240 MB. I'm going to try linking and see what the effects are.
Thanks
rbianco
Glad to know you are on your way...! thumbup.gif
pmarley2
Hi rbianco,
did encounter a minor problem with linking my large CSV file as opposed to importing it directly - it takes somewhat longer for the list to load each time I open the table. However, by linking, I only had to use 107MB of space (for the original CSV file), as opposed to the 240MB required for the imported file.
Thanks
rbianco
The link can take a little longer when you open the linked table and wish to go to the end of the records, but you want to avoid directly interacting with the table.

Try building queries with your linked data, and depending on how complex they are, if you are filtering for smaller subsets of data within the larger whole, you should find they run fine.

FOr now that you are linked, try creating a make table query based on your linked data, that summarizes the raw data into a more manageable set of record rows (if that fits your need). For instance, maybe the data you linked to is the daily raw data of cash receipts for different accounts, but you know that you will only need to have the monthly totals. Create make table that calculates the monthly totals for each account and write that to a table. The smaller summarized table will be smaller and now that it is resident in the database, can also be distributed with the database without need to retain the link to the massive raw data file....

just an idea or two

btw...try Compact & Repair. 107mb of space still seems a little large for a db whose main data table resides "off-site"...

Edited by: rbianco on Thu Aug 6 15:12:05 EDT 2009.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.