How to importing into Access form Excel?

Question

I have a bunch of tables in excel with financial data such as balance sheets. They are organized by account and by data. How would I import them into access so I can build reports and manipulate the data within Access?

Answer 1

It depends if you want to import them or link them. If you want to create reports with Access, but still be able to change the data in Excel, then choose to create a new table, select link, switch the type to excel, then find your spreadsheet. If there are multiple tabs, each one will have to be a separate table.



Depending on how the spreadsheet is set up, you may just be better creating a new table in Access and copying the data into it, but if it works the way I suggested, then great.



If you want to use the spreadsheet as it is now, without being able to update in Excel and re-run the reports in Access, then follow the same steps mentioned above, but chose import instead of link.

Answer 2

Remember when importing data from Excel to Access that you do not want totals or extra line breaks. It's better to put the raw data or refined data straight into Access without all of the fluff of sub-totals or totals at the bottom. Then you can create reports and queries off of this to better show or manipulate the data.



To import is easy. File -- Get External Data --Import -- and then select your file.



Also, be sure you columns are setup with the format and type you want otherwise they will not import correctly and could give you import errors.

Answer 3

yes, very easily. YOu dont have to set up any tables in Access, just create a blank database so you have one ready and waiting. From the database window, up to File, Get External Data, Import, and navigate to your database, select the spreadsheets, follow the instructions and you should have absolutely no problems. Once you've got your spreadsheets in table format, you can go into design, and change the relevant fields to the data format you want, i.e. Yes/No (Boolean), date or whatever. From the tables, you can click on the autoform icon to create automatic forms until you learn how to design your own forms from scratch, same as autoreports, until you learn how to create more aesthetically pleasing reports from scratch.For queries, easy peasy, use the simple query wizard, if you want to sort, say alphabetically or by any field, use the third row, the fifth row is for criteria so if you have names and addresses, and you want to extract, say SA1 postcodes, just type SA1 on the fifth (criteria row)in the relevant field, and press the big exclamation mark to run the query. You'll find it's a wonderful program, some find it hard to get to grips with because you firstly have to name the database before you create it and then everything within it has to be named and also everything has two views, but it is a very logical program and very powerful when used properly.

Answer 4

right click on white area in tables.



Import



browse to the file you want to import and change filetype to xls



You can then go through the wizard to import your sheets.