|
141- Importing from access Play Video You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or the many functions that are not available in Access. 1) From Access, you can copy data from a datasheet view and then paste the data into an Excel worksheet. 2) Start Access, and then open the table, query, or form that contains the records that you want to copy. 3) On the Home tab, click View, and then click Datasheet View. 4) Select the records that you want to copy. 5) If you want to select specific columns, drag across adjacent column headings. 6) On
the Home tab, in the Clipboard group, click Copy 7) Start Excel, and then open the worksheet that you want to paste the data into. 8) Click in the upper-left corner of the worksheet area where you want the first field name to appear. 9) To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell that you click. 10) On
the Home tab, in the Clipboard group, click Paste
By using the Export Wizard in Access, you can export an Access database object, such as a table, query, or form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use, and even schedule the export operation to run automatically at specified intervals. 1) The following are common scenarios for exporting data from Access to Excel: 2) Your department or workgroup uses both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyze the data and to distribute the results of your analysis. Your team currently exports data to Excel when they need to, but you would like to make this process more efficient. 3) You are a long-time user of Access, but your manager prefers to view reports in Excel. At regular intervals, you do the work of copying the data into Excel, but you would like to automate this process to save yourself time. 4) For more information about exporting data from Access to Excel, see the Access Help system.
Connect to Access data from Excel: To bring refreshable Access data into Excel, you can create a connection, often stored in an Office Data Connection file (.doc), to the Access database and retrieve all of the data from a table or query. The main benefit of connecting to Access data is that you can periodically analyze this data in Excel without repeatedly copying or exporting the data from Access. After you connect to the data, you can also automatically refresh (or update) your Excel workbooks from the original Access database whenever the database is updated with new information. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month’s data. 1) Click the cell where you want to put the data from the Access database. 2) On the Data tab, in the Get External Data group, click From Access. 3) In the Look in list, locate and double-click the Access database that you want to import. 4) In the Select Table dialog box, click the table or query that you want to import, and then click OK. 5) In the Import Data dialog box, do the following: * Under Select how you want to view this data, do one of the following: § To view the data as a table, select Table. § To view the data as a PivotTable report, select PivotTable report. § To view the data as a PivotChart and PivotTable report, select PivotChart and PivotTable report. * Optionally, click Properties to set refresh, formatting, and layout options for the imported data, and then click OK. * Under Where do you want to put the data? Do one of the following: § To return the data to the location that you selected, click Existing worksheet. § To return the data to the upper-left corner of the new worksheet, click new worksheet. 6) Click OK.
Work with Excel data in Access: You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users might find particularly useful for their Excel data: · Reports If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports. · Forms If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or view a lengthy row of data more easily on one screen.
1) Start Excel, and then open the worksheet that contains the data that you want to copy. 2) Select the rows that you want to copy. 3) On
the Home tab, in the Clipboard group, click Copy 4) Start Access, and then open the table, query, or form in which you want to paste the rows. 5) On the Datasheet tab, click View, and then click Datasheet View. Do one of the following: § To
replace records, select those records, and then on the Home tab, in the Clipboard group, click Paste § To append the data as new records, on the Home tab, in the Clipboard group, click Paste Append on the Edit menu.
Import Excel data into Access: To store data from Excel in an Access database, and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet. The following are common scenarios for importing Excel data into Access: · You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases. · Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them. · You use Access to manage your data, but the weekly reports that you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.
Link to Excel data from Access: You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want it to be available from within Access. You create this type of link from within the Access database, not from Excel. When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file. The following are common scenarios for linking to an Excel worksheet from within Access: · You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access. · Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don’t want to maintain copies of external data, but want to be able to work with it in Access.
142- Using the import wizard for text files Play Video Import a text file by opening it: You can convert a text file that you created in another program to an Excel workbook by using the Open command. 1) Click
the Microsoft Office Button 2) The Open dialog box appears. 3) In the Files of type box, select Text Files. 4) In the Look in list, locate and double-click the text file that you want to open: § If the file is a text file (.txt), Excel starts the Import Text Wizard. Follow
the instructions in the Text Import Wizard. Click Help § If the file is a .csv file, Excel automatically converts the text file and opens it.
Import a text file by connecting to it: You can import data from a text file as an external data range. 1) Click the cell where you want to put the data from the text file. 2) On the Data tab, in the Get External Data group, click From Text. 3) In the Look in list, locate and double-click the text file that you want to import.
1) In the Import Data dialog box, do the following: o Optionally, click Properties to set refresh, formatting, and layout options for the imported data. o Under Where do you want to put the data?, do one of the following: § To return the data to the location that you selected, click Existing worksheet. § To return the data to the upper-left corner of a new worksheet, click new worksheet. 2) Click OK. Excel puts the external data range in the location that you specify.
1) Click
the Microsoft Office Button 2) In the Save as type box, choose the text file format for the worksheet. 3) In the Save in list, browse to the location where you want to save the new text file. 4) In the File name box, review the name that Excel proposes for the new text file, and change it if necessary. 5) Click Save. · A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are certain that the current worksheet is the one that you want to save as a text file, click OK. You may save other worksheets as separate text files by repeating this procedure for each worksheet. · A second dialog box appears, reminding you that your worksheet may contain features that are not supported by text file formats. If you are only interested in saving the worksheet data into the new text file, click yes. If you are unsure and would like to know more about which Excel features are not supported by text file formats, click Help for more information. · For more information about saving files in other formats, see Save a workbook in another file format. For a delimited text file, you can change the delimiter from a TAB character to another character in step 2 of the Text Import Wizard. In step 2, you can also change the way that consecutive delimiters, such as consecutive quotes, are handled. For more information about using the Text Import Wizard, see Text Import Wizard. 1) Click the Windows Start menu. 2) Click Control Panel. 3) Open the Regional and Language Options dialog box. 4) Click the Regional Options tab. 5) Click Customize. 6) Type a new separator in the List separator box. 7) Click OK twice.
Back To Top Back To Excel Index Page (Open Exercise file)
|
. 
.
,
and then click Open. 
for more information about using the Text Import Wizard or
see