Importing Data Into Excel - Best Practices and Mistakes To Avoid
In this blog post, I am going to give you several tips for importing data into excel spreadsheets with little or no hassle. I will also mention some common mistakes that may hinder the process of your data conversion and processing. Let's jump right in.
How to import data to Excel
Microsoft Excel is the world's most popular table-structured data processor that can import a variety of file types aside from its standard .xls/.xlsx format. It can also use data from external data sources including other files, databases, or web pages, so you can combine this information with another one in the spreadsheet editor.
To import data to Excel:
Open the application and click the Data tab on the Ribbon;
Click the Get Data button and then choose the data source: file, database, azure, online services, or other sources.
Follow these steps if you need to import data from a CSV file:
- Select From File;
- Choose your data file format, such as XLS Workbook, text/CSV, XML, or JSON. You can also select the entire folder when you need to import data to Excel in bulk.
- Select the file you want to import and click Import.
After that, you need to verify if the preview looks accurate, then click Load. You are all set.
The data in a CSV file is separated by commas, so the delimiter should be already set. However, you can use the Delimiter menu if you need to change it.
If you want to import a different file type, the process is almost the same. For instance, to import your data from a PDF file, select Get Data > From File > From PDF. All you need to do is to make sure that the rows and columns are recognized properly.
How to avoid some common mistakes when importing data to Excel
To avoid several common errors in importing data to Excel, please make sure that all the columns and rows are aligned and named similarly in both Excel file and another one that you are going to import.
Use the Export Importable Spreadsheet action and utilize the exported spreadsheet as a template for data importing. When you export, site-defined and classification attributes are also included.
Some import errors can be caused by limits set by the program using the file or the amount of available memory on the system. In case your import fails because of the size issues, please break the source file up into smaller ones that can be successfully uploaded. In addition, I never recommend importing to Excel a file over 10,000 rows at once, since various errors may occur.
A text format is recommended for all cells in the spreadsheet, because Excel may trim zeros and fractional numbers if you are not using text format.
Another common import error is related to matching. Some columns may not match expected field names caused by different values than expected, field names not on the first line, or simply the complete absence of column names. Your columns may also be in the wrong order or required fields may be missing.
Some cells may be merged, which confuses an importer, so before importing data select all cells and unmerge them in a spreadsheet.
When you import your data to Excel, select the suitable encoding. When it is incorrect or unexpected, a data translation error may happen.
Problems with data values can be a significant cause of Excel importing errors, such as unexpected data length, text in a numbers-only column, or numbers in a boolean. That is why it is best practice to validate the data in a source file before importing it.
On the final step of the import process you may face an "Unexpected Error.", which indicates that something is wrong with your CSV/Text file. To solve it make sure you do not have any empty rows between data in the source file. Moreover, some files can become corrupted, so you might need to recreate them. In that case, open the file in Excel, then copy the cells that have data and paste them into a brand new spreadsheet. Save it as a new CSV/Text file and try importing it to Excel again.
To sum up, importing data to Excel is an easy process when you follow several essential tips. However, some errors may occur as well, and minimizing Excel import errors can be a time-consuming procedure. You can avoid the majority of importing issues by checking your data and aligning it with Excel's requirements.
Import Data Dialog Box
The Import Data dialog box appears. As you observe, you have the following options to view the data you are importing in your workbook - Table PivotTable Report PivotChart Power View Report You also have an option - only create connection.
Microsoft Access Database
Importing Data From Microsoft Access Database In layman's terms, Microsoft Access is a Database Management System (DBMS). If you have Microsoft Access, you can now easily import data from the Access database directly into an Excel sheet.
Get External Data Group
On the Data tab, in the Get External Data group, click From Text. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.
Access Database File
In the Import Data dialog box, browse for and locate the Access database file (.accdb). Select the file, and then select Open. The Navigator dialog box appears. Select the table or query in the left pane to preview the data in the right pane. If you have many tables and queries, use the Search box to locate an object.
Imported Data
Excel tables for the imported data tables will not appear in the workbook. However, you will find all the data tables in the PivotTable fields list, along with the fields in each table.
Save Data Connection File
The table name will be the worksheet name. So, to have meaningful table names, name / rename the worksheets as appropriate. The Data Connection Wizard dialog box appears with Save Data Connection File and Finish.
External Data Source
The goal in this section is to connect to an external data source, and import that data into Excel for further analysis. Let's start by downloading some data from the Internet. The data describes Olympic Medals, and is a Microsoft Access database.
Connection Properties Dialog Box
The Connection Properties dialog box appears. The Refresh Control options appear. Click Enable background refresh. The Background refresh is enabled for your workbook.
Existing Connections Dialog Box
The Existing Connections dialog box appears. Select the connection from where you want to import data and click Open. Renaming the Data Connections It will be useful if the data connections you have in your workbook have meaningful names for the ease of understanding and locating.
Workbook Connections Dialog Box
The Workbook Connections dialog box appears. Click the Properties button. The Connection Properties dialog box appears. Click the Usage tab. The Refresh Control options appear. Click Enable background refresh. Click OK. The Background refresh is enabled for your workbook
Excel Workbook
Your workbook looks like the following screen. Now that you have an Excel workbook with tables, you can create relationships between them. Creating relationships between tables lets you mash up the data from the two tables.
External Data Range
Optionally, click Properties to set refresh, formatting, and layout options for the imported data. Click OK. Excel puts the external data range in the location that you specify. If Excel does not convert a column of data to the format that you want, you can convert the data after you import it.
Text Import Wizard
you can see the effect of your new choice immediately in the data preview, so you can be sure you make the choice you want before you proceed. If you use the Text Import Wizard to import a text file, you can change the delimiter that is used for the import operation in Step 2 of the Text Import Wizard.
Multiple Tables Box
Tables in a database are similar to worksheets or tables in Excel. Check the Enable selection of multiple tables box, and select all the tables. Then click OK. The Import Data window appears.
Text Files
New Import data into Excel from another Excel file. You can import data into an Excel worksheet using text files, databases and another Excel file. The process can help data entry as well as data analysis.
Blank Workbook
If you have Microsoft Access, you can now easily import data from the Access database directly into an Excel sheet. In Excel, create a new blank workbook.
Web Page
Import data using copy and paste Now that we've imported data from an Excel workbook, let's import data from a table we find on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the Olympic host cities from a table.
Existing Worksheet
You have an option to create the PivotTable in an existing worksheet or a new worksheet. Excel tables for the imported data tables will not appear in the workbook.
