What You Need to Know About Importing and Exporting in Excel

Although Excel allows you to copy and paste data, this doesn’t make sense with a large dataset or certain formats. Instead, you can import or export data much more quickly. 

Here, you will learn some of the operations related to importing and exporting data in Excel as well as troubleshooting common issues. If you need problem-specific help, Excelchat is always standing by to help. 

Learn how to import and export data in Excel.

Use the Excel Text Import Wizard

Excel doesn’t allow you to export directly from a text file or Word document, but the Excel text import Wizard will bring data from a text file directly into a worksheet. This isn’t instant, but instead a process that will ensure that your data is brought into Excel in the format you want. 

To begin the Text Import Wizard, go to the Data tab in Excel, and click Get External Data, and then select From Text. Then you will choose the text file you want to import. 

The import process involves several steps:

1. You will choose Delimited if the data in your text file is separated by spaces, colons, tabs, semicolons, or other characters. Choose Fixed width if every column is the same length. Select the row for the Start import at row. You can generally leave File origin at default. Then, you can see a Preview of file. 
2. When you have delimited data, you’ll need to choose the character that separates your data. If you don’t see the character listed, choose Other. A text qualifier, such as a quotation mark (“) will ignore the delimiter. 
3. You may or may not need to use the Advanced button on the import feature. For example, you can specify that thousands or decimal separators are used in your text file or that numbers contain a trailing minus sign. You can also skip importing some data and specify certain formats such as dates and currency.

Import and Export Text Files in Excel

Excel also allows you to import and export text files in several formats. The files that you work with can be in either .txt or .csv format. 

To import a file, go to the File tab and select Open. Choose Browse. Then, select the type of file you want to import from the drop-down list (.txt or .csv). Once you’ve done this, navigate to the location of the file in File Explorer and choose Open. 

If you’ve chosen a .csv file, you’re done. If it is a .txt file, it will open the Text Import Wizard (see above). 

To export your Excel sheet to a text file, you’ll want to choose Save As, and then browse to where you want to save the file. Choose a new name. From the Save as type drop-down box, choose Text (tab delimited).  Click Save. 

Import Contacts from Excel to Outlook

If you’ve been keeping a lot of personal or business contacts in an Excel spreadsheet, you can import these straight into Outlook. The steps to do this are:

1. Save your Excel sheet as a .csv file.  If you have a .xls file, click File>Save As. Choose a place to save the file, and then choose OK.  Now close your new .csv file. 
2. Import your contacts to Outlook. Inside Outlook, Choose, File, Open & Export > Import.  Select “Import from another program or file,” and then choose Next.  Choose Comma Separated Values, and then select Next.  Find the .csv file that you just created. Under Options, decided what to do about duplicates, select Next. Contacts should be the default (if not, select it), click Next.  Don’t click Finish yet. 
3. Map your CSV columns. Select Map Customs Fields. A dialog box will appear. Match the From and To column names (some are standard).  Once you have the columns correctly mapped, click Finish. Your contacts will then be imported into Outlook.

Export Access Data to Excel

You might have data in an Access database that you’d like to work with in Excel. You can export this from Access to Excel using the program’s Export Wizard. This is something that you can set up as a one-time operation, or you can even put it on a schedule to do repeatedly. 

From Access, you don’t have the option of choosing Save As to get your data into Excel. Instead, you’ll want to use the Export feature. It’s also important to note that you cannot export modules or macros to Excel. You can only export a single database object with each export operation, but you can later merge multiple worksheets to produce the data you need. 

1. Choose your destination Excel workbook. It must be in .xls format since the newer .xlsx file format won’t work with this type of operation. This can be a new Excel workbook or an existing one. If it is an existing one, the import might overwrite data or create a new sheet. 
2. Make sure your destination workbook is closed before you begin. 
3. In the Access Navigation panel of the source database, select the object you want to export.  Open the Datasheet view of the object and choose only the records you want to export. 
4. In the Export group section of the External Data tab, choose Excel. 
5. An Export-Excel Spreadsheet dialog box will appear. You can change the filename if you wish. Confirm the format in the File Format box. 
6. Choose Export data with formatting and layout if you are exporting a query or table. This isn’t an option if you are exporting a report or form. 
7. Choose Open the destination file after the export operation is complete to see the final result. 
8. Click Ok. 

If there is an error in this operation, Access will tell you what went wrong so that you can try again. 

Did you find what you needed with these Excel import and export explanations? Whether you need some additional guidance or want to troubleshoot a specific issue in Excel, find out how Excelchat can help. 

We have Excel experts standing by 24/7 to lend a hand. Your first session is always free.