Importing Data from Excel, CSV, and Multiple Files

Course: Advanced Excel

Topic: Importing Data from Excel, CSV, and Multiple Files Using Excel and Power Query

Course Link: Advanced Excel Course

Demo Link: Register for a Free Course Demo

Website: JustAcademy

1. Introduction to Data Importing in Excel

Data importing is the process of bringing information from external files or other data sources into Microsoft Excel for cleaning, organizing, analyzing, and reporting. In professional environments, data is often stored in multiple workbooks, CSV exports, monthly reports, departmental files, or system-generated downloads.

Manually copying and pasting this information can take time and introduce errors. Excel provides several import options, including the Get Data feature and Power Query, which help users connect to files, preview their contents, transform the data, and load it into worksheets or the Excel Data Model.

Power Query is especially useful when the same import process must be repeated. After a query has been configured, users can often refresh it to retrieve updated information from the source files without repeating every manual step.

Learning Objectives

• Understand common data file formats used with Excel.
• Import data from another Excel workbook.
• Import and configure CSV files correctly.
• Combine multiple files from a folder using Power Query.
• Clean imported data and correct data types.
• Understand the difference between loading data and connecting to a source.
• Refresh queries and troubleshoot common import problems.
• Build a repeatable data-import workflow for reporting and analysis.

2. Understanding Common Data File Formats

Before importing data, it is important to understand the structure and purpose of each file format.

Excel workbooks can contain several structured data objects, whereas CSV files usually contain one flat table. When combining files, ensure that the intended records and columns are compatible.

3. Understanding Excel's Get Data Feature

The Get Data feature allows Excel users to connect to external sources and import their information through a guided process. Power Query is the data preparation engine used by many of these import options.

Common Import Options

• From Excel Workbook: Connects to another Excel file and lets you select an available worksheet, table, or named range.
• From Text/CSV: Imports data from delimited text files.
• From Folder: Lists files in a folder and can combine compatible files into one dataset.
• From Table/Range: Creates a query from a table or range in the current workbook.
• Transform Data: Opens Power Query Editor so you can inspect and clean the data before loading it.
• Load: Loads the query results to an appropriate destination.

General Import Workflow

1. Open the Excel workbook where the imported data will be used.
2. Select the Data tab.
3. Choose Get Data or the relevant import command.
4. Browse to the source file or folder.
5. Preview the available data and select the required worksheet, table, or file.
6. Choose Transform Data if you need to clean or reshape the information.
7. Review column headers, data types, blank rows, and errors.
8. Select Close & Load or the appropriate Load option.
9. Verify the resulting dataset before using it for analysis.

The exact menu wording and available connectors may vary by Excel version, operating system, and Microsoft 365 update.

4. Importing Data from Another Excel Workbook

Excel workbooks are frequently used to store sales reports, employee records, inventory details, budgets, and operational data. Importing a workbook through Power Query makes it easier to reuse the same source and refresh the results later.

Step-by-Step Procedure

1. Open the destination workbook.
2. Go to Data > Get Data > From File > From Excel Workbook.
3. Browse to the source workbook and select it.
4. In the Navigator window, inspect the available worksheets, tables, and named ranges.
5. Select the object that contains the required data.
6. Preview the data to confirm that the correct range has been selected.
7. Choose Load to import the data directly, or Transform Data to edit it in Power Query.
8. After any required transformations, select Close & Load.

Example: Importing a Sales Workbook

Suppose a workbook named Sales_Records.xlsx contains a worksheet called SalesData with the following information:

After importing the SalesData table, you can create PivotTables, calculate regional sales, build charts, or use the data in a dashboard.

Best Practices for Excel Workbook Imports

• Prefer structured Excel Tables over loosely defined ranges whenever possible.
• Use clear, unique column headers.
• Remove decorative title rows and unnecessary merged cells from source data.
• Check whether formulas in the source workbook have been calculated and saved correctly.
• Select the specific table or worksheet you need instead of importing unrelated content.
• Keep the source file in a stable location if the query will be refreshed regularly.

5. Importing Data from CSV Files

CSV stands for Comma-Separated Values. It is a plain-text format commonly used to exchange tabular information between Excel, databases, websites, accounting applications, CRM platforms, and business systems.

A CSV file typically stores one record per line, with fields separated by a delimiter. Although a comma is common, some files use semicolons, tabs, or other separators.

Example of CSV Content

Order ID,Order Date,Product,Region,Sales
ORD101,2026-10-01,Laptop,West,55000
ORD102,2026-10-02,Monitor,North,12000
ORD103,2026-10-03,Keyboard,South,2500

When imported correctly, the values are separated into five columns: Order ID, Order Date, Product, Region, and Sales.

Step-by-Step CSV Import Procedure

1. Open the destination workbook in Excel.
2. Select Data > Get Data > From File > From Text/CSV.
3. Browse to the required CSV file.
4. Select the file and review the preview window.
5. Check that the delimiter separates fields into the correct columns.
6. Confirm the file origin or encoding if special characters appear incorrectly.
7. Select Transform Data if you need to correct column types or clean the records.
8. Select Close & Load to load the prepared data.

Important CSV Import Settings

Common CSV Import Problems

• All values appear in one column: Check the delimiter. The file may use a semicolon or tab instead of a comma.
• Dates appear incorrectly: Confirm the date format and locale. For example, 04/05/2026 can mean different dates depending on regional settings.
• Leading zeros disappear: Import identifier fields as text. A code such as 00125 should not automatically become 125.
• Special characters appear incorrectly: Review the file encoding, such as UTF-8.
• Large numbers change: Import identifiers and long account numbers as text if they are not intended for arithmetic.
• Values shift into the wrong columns: Check whether text fields containing delimiters are properly enclosed in quotation marks in the source file.

6. Importing Multiple Files from a Folder

In many organizations, data is divided into separate files by month, region, branch, department, or reporting period. Power Query can combine files from a folder into one dataset when their structures are compatible.

For example, a sales team might receive one workbook for each month. Instead of copying all records into a master worksheet, you can create a folder query and combine the files into a single table.

Example Folder Structure

SalesReports/
    Sales_January.xlsx
    Sales_February.xlsx
    Sales_March.xlsx

Assume each file contains the same columns: Order ID, Order Date, Product, Region, and Sales. Power Query can append the records from these files into one consolidated table.

Step-by-Step Procedure for Combining Files

1. Place the source files in a dedicated folder.
2. Ensure that the files intended for combination use a consistent data structure.
3. Open the destination workbook.
4. Select Data > Get Data > From File > From Folder.
5. Browse to the folder containing the files and confirm the selection.
6. Review the file list to ensure that the intended files are included.
7. Select Combine & Transform Data if you want to inspect or clean the data before loading.
8. In the Combine Files window, select the appropriate sample file and data object, if prompted.
9. Review the combined preview and check that columns match correctly.
10. Apply any required transformations in Power Query Editor.
11. Select Close & Load to create the consolidated dataset.

How Power Query Combines Files

1. Reads the folder: Power Query lists files available at the selected location.
2. Identifies eligible files: You can filter by file extension, filename, or folder path.
3. Examines a sample file: It uses a sample to determine how to interpret and transform the data.
4. Applies the transformation: Similar processing steps are applied to the selected files.
5. Appends records: Rows from the files are combined into a consolidated table.
6. Loads the result: The combined output can be loaded into Excel for analysis.

Important Requirements for Combining Files

• Use a dedicated folder containing only the intended source files where practical.
• Keep column names consistent across files.
• Ensure that the same fields represent the same type of information in each file.
• Check for repeated header rows that might appear in the middle of imported data.
• Filter out temporary files, unrelated exports, or backup copies before combining.
• Inspect files with errors rather than automatically excluding them without review.

7. Understanding Append and Merge Operations

When working with multiple data sources, it is important to distinguish between appending rows and merging columns.

Append Example

January sales contain 100 rows and February sales contain 150 rows. Appending the two tables produces up to 250 rows before any duplicate removal or filtering.

Merge Example

An Orders table contains Customer ID and Order Amount. A Customers table contains Customer ID and Customer Name. Merging these tables on Customer ID allows the customer name to be added to each matching order.

Remember: Use Append when you want to stack records. Use Merge when you want to look up related information across tables.

8. Cleaning Imported Data with Power Query

Importing data is only the first stage of a reliable data workflow. Source files may contain blank rows, inconsistent column names, extra spaces, duplicate records, missing values, or incorrect data types. Power Query allows many of these issues to be handled before the data reaches the worksheet.

Common Data Cleaning Tasks

• Promote Headers: Use the correct first row as column names.
• Rename Columns: Replace unclear labels with meaningful names.
• Remove Rows: Exclude unnecessary title rows, blank rows, or irrelevant records.
• Remove Columns: Eliminate fields that are not required for analysis.
• Trim and Clean Text: Remove unnecessary leading, trailing, or non-printing characters.
• Change Data Types: Set dates, whole numbers, decimal numbers, and text correctly.
• Replace Values: Standardize inconsistent labels or correct known source values.
• Remove Duplicates: Remove repeated records when the business rules confirm that they are duplicates.
• Filter Rows: Keep only the relevant records.
• Handle Errors: Investigate invalid values before replacing or removing them.

Example of Data Cleaning

Suppose a CSV file contains these region values:

Using text-cleaning and replacement steps helps standardize these labels so that reports do not treat the same region as several separate categories.

9. Managing Data Types During Import

Correct data types are essential for calculations, sorting, filtering, PivotTables, and charts. Automatic type detection is convenient, but it should always be checked.

A numeric-looking field is not always a number. Employee IDs, invoice codes, and phone numbers are usually identifiers and should often remain text. Changing them to numeric types can remove leading zeros or alter the original representation.

10. Loading Imported Data into Excel

After preparing data in Power Query, you can choose how and where the results should be loaded.

Common Load Options

• Load to Worksheet: Places the resulting table in a worksheet.
• Load to the Data Model: Makes data available for certain advanced analysis scenarios, including relationships and PivotTables using the Data Model.
• Only Create Connection: Creates a connection without displaying the complete query result as a worksheet table.
• Transform Data: Returns to the Power Query Editor for additional cleaning and shaping.

Depending on the Excel version and the selected connector, available destinations may differ. Large datasets may be better suited to a connection or Data Model than a worksheet, which has a limit of 1,048,576 rows per sheet.

11. Refreshing Imported Data

One of the main advantages of Power Query is the ability to refresh a query after the source data changes. For example, if a new monthly sales file is added to a folder, refreshing a properly configured folder query can include the new records.

How to Refresh Data

1. Update the source workbook or add the new file to the source folder.
2. Open the destination workbook.
3. Go to the Data tab.
4. Select Refresh All, or right-click a query result and choose Refresh.
5. Wait for the refresh to finish.
6. Check the row count, totals, and any query errors.

Refresh Considerations

• Keep source file names and locations stable where possible.
• Ensure that new files follow the expected structure.
• Confirm that the workbook has permission to access the source folder.
• Review errors after refreshing instead of assuming the import succeeded completely.
• Check whether report formulas, PivotTables, and charts need additional refreshing depending on how they are configured.

12. Common Import Errors and Troubleshooting

13. Best Practices for Professional Data Importing

• Use structured source files: Prefer Excel Tables with clear headers and consistent columns.
• Standardize file naming: Use predictable names such as Sales_2026_10.xlsx.
• Separate source and output folders: Keep input files apart from exported results and unrelated documents.
• Validate before analysis: Check row counts, key fields, date ranges, and totals after importing.
• Preserve identifiers: Keep codes and other non-arithmetic values as text when appropriate.
• Document transformations: Use clear query names and meaningful step names so other users can understand the workflow.
• Minimize manual copy-and-paste: Use repeatable query steps for recurring reports.
• Check duplicates carefully: Remove duplicates only when the definition of a duplicate is understood.
• Protect sensitive data: Import only the fields required for the task and follow organizational access policies.
• Review refresh results: Validate the output after every important source or query change.

14. Practical Exercise: Combine Monthly Sales Files

Scenario: A company receives three Excel files containing sales data for October, November, and December. Each file has the same five columns: Order ID, Order Date, Product, Region, and Sales. The goal is to create one consolidated dataset for quarterly reporting.

Exercise Tasks

1. Create a folder named QuarterlySales.
2. Place the three source files in that folder.
3. Open a new Excel workbook and import the folder using Get Data.
4. Preview the files and combine the appropriate worksheet or table from each file.
5. Ensure that Order Date is a date, Sales is numeric, and Order ID is text.
6. Remove blank rows and investigate any errors.
7. Load the consolidated result into Excel.
8. Create a PivotTable showing total sales by region and month.
9. Create a chart comparing monthly sales.
10. Add another valid monthly file to the folder and refresh the query.
11. Verify that the new records appear and the report totals update as expected.

Expected Outcome

You should have a consolidated table containing the records from the selected source files, a repeatable import process, and a report that can be updated by refreshing the query when compatible source files are added.

15. Mini Project: Build a Multi-Source Sales Report

Project Objective: Create a reliable sales analysis workbook using information imported from an Excel workbook and several CSV exports.

Project Requirements

• Import a product master file from Excel.
• Import transaction records from CSV files.
• Combine monthly transaction files from a folder.
• Clean product names, region labels, and date fields.
• Verify that sales amounts are numeric and transaction IDs are handled consistently.
• Use Merge Queries to connect transaction records to product information when a common product key exists.
• Use Append Queries to consolidate compatible monthly transaction tables.
• Load the prepared data into a worksheet or Data Model, depending on the reporting requirement.
• Create a PivotTable and charts showing sales by product, region, and month.
• Test the refresh process by adding a new compatible source file.

Project Deliverables

• A workbook with clearly named queries.
• A cleaned and consolidated sales dataset.
• A summary report with PivotTables and charts.
• A short description of data-quality checks and assumptions.
• A tested refresh workflow.

16. Interview Questions and Answers

Q1. What is data importing in Excel?

Answer: Data importing is the process of bringing information from external files or other sources into Excel so it can be organized, transformed, analyzed, and reported.

Q2. What is Power Query?

Answer: Power Query is a data connection and transformation tool that allows users to import, clean, reshape, combine, and refresh data from supported sources.

Q3. What is the difference between an Excel workbook and a CSV file?

Answer: An Excel workbook can contain multiple worksheets, tables, formulas, formatting, and other workbook features. A CSV file is a plain-text representation of a single delimited table and does not preserve Excel workbook features.

Q4. How do you import a CSV file using Power Query?

Answer: Select Data > Get Data > From File > From Text/CSV, choose the file, verify the delimiter and preview, transform the data if required, and load the result.

Q5. How can multiple files be combined in Excel?

Answer: Use Data > Get Data > From File > From Folder, select the folder, and use the Combine option to append compatible files into a consolidated dataset.

Q6. Why should source files have consistent column names?

Answer: Consistent column names and data structures help Power Query identify corresponding fields and combine records correctly. Matching is based on column names, so differences can result in extra columns or missing values.

Q7. What is the difference between Append and Merge in Power Query?

Answer: Append adds rows from one table to another. Merge joins information from two tables using matching columns, such as Customer ID or Product ID.

Q8. Why might Excel remove leading zeros from an imported value?

Answer: Excel or Power Query may interpret the field as a number. Importing identifiers as text helps preserve values such as 00125.

Q9. What is the purpose of Transform Data?

Answer: Transform Data opens Power Query Editor, where users can clean, filter, rename, combine, and change the data types of columns before loading the results.

Q10. What does Refresh All do?

Answer: Refresh All updates supported workbook connections and queries from their configured sources. Users should still verify that each refresh completes successfully and that the resulting data is correct.

Q11. What should you do when all CSV values appear in one column?

Answer: Check the delimiter in the import preview. The file may use a semicolon, tab, or another separator instead of a comma.

Q12. How can you make recurring data imports more efficient?

Answer: Create a reusable Power Query workflow, keep source files in stable locations, standardize their structure, and refresh the query when new data becomes available.

17. Quick Revision Checklist

• Understand the differences between Excel workbooks, CSV files, and text files.
• Know how to import an Excel workbook through Get Data.
• Know how to import a CSV and verify its delimiter and encoding.
• Know how to combine compatible files from a folder.
• Understand the difference between Append Queries and Merge Queries.
• Use Power Query to clean records and set correct data types.
• Choose the appropriate load destination.
• Refresh queries and validate the updated results.
• Troubleshoot missing files, incorrect dates, errors, and duplicate records.
• Keep the import workflow organized and repeatable.

18. Conclusion

Importing data from Excel, CSV, and multiple files is an essential Advanced Excel skill for business reporting and data analysis. Excel's Get Data feature and Power Query help users connect to different files, transform raw information, combine compatible datasets, and create repeatable workflows.

By checking file structure, selecting the correct delimiter, preserving identifiers, validating data types, and testing refresh operations, you can build more accurate and maintainable reports. These techniques are especially valuable when working with monthly sales reports, financial records, inventory files, customer exports, and other recurring datasets.

To learn more about advanced spreadsheet skills, explore the Advanced Excel Course at JustAcademy, visit the JustAcademy website, or register for a course demo.

19. Additional Learning Resources

• Microsoft Support: Import Data from Data Sources Using Power Query
• Microsoft Support: Import Data from a Folder with Multiple Files
• Microsoft Support: About Power Query in Excel