Introduction
Using Excel’s Power Query feature allows you to efficiently import and transform only the necessary rows from a CSV file.
In this article, I will summarize the actual operational steps for individually extracting and shaping specific row ranges (e.g., rows 12–24) and subsequent data (e.g., rows 26 onward).
What I Want to Achieve
- Extract and acquire only specific row ranges from a CSV file.
- Acquire multiple sections in separate queries and merge or process them as needed.
Steps: Flow of Acquiring Data from CSV
By following the steps below, you can import only the necessary rows from a CSV file using Power Query.
1. Open Excel and Go to the “Data” Tab
First, launch Excel as usual and switch to the “Data” tab in the top menu.
2. Select “From Text/CSV”
Select the following menu items in order from the “Data” tab:
- “Get Data” (or directly from the ribbon)
- “From File” -> “From Text/CSV”
Next, select the target CSV file and click “Import”.
3. Launch Power Query Editor
When the file content preview appears, click “Transform Data” to open the Power Query Editor.
4. Extract Only Specific Rows (e.g., Rows 12–24)
To acquire rows 12 through 24:
- From the top menu “Home”, select “Remove Rows” -> “Remove Top Rows”. Enter 11 to remove the first 11 rows.
- Next, select “Keep Rows” -> “Keep Top Rows”. Enter 13 (calculated as 24 – 12 + 1) to keep the target rows.
This leaves only rows 12 to 24.
To acquire row 26 and beyond: Create a separate query (duplicate the original source query) and do the following:
- Select “Remove Top Rows” and remove 25 rows (excluding everything up to row 25).
- Keep the remaining rows as is (or limit further if needed).
5. Data Transformation and Adjustment
Perform the following processing as necessary:
- Change column data types (Number, Date, Text, etc.).
- Rename columns (Use “Use First Row as Headers” if row 12 contained headers).
- Remove or reorder unnecessary columns.
- Clean data (remove blanks, etc.).
6. Load Data into Excel Sheet
Once editing is complete, click “Close & Load” in the top menu “Home”. The data will be loaded into a new Excel sheet or an existing sheet.
Tip: What if You Want to Acquire Multiple Ranges?
If you want to acquire different row ranges, such as “Rows 12–24” and “Row 26 onwards,” it is effective to duplicate the query and perform separate processing for each query.
If you want to combine them later, you can use the “Append Queries” function to merge the two ranges into a single table.
Summary
By using Power Query, you can pinpoint and extract specific row ranges from a CSV file and reflect them in Excel.
- Specify row ranges using a combination of “Remove Top Rows” and “Keep Top Rows”.
- Separating queries allows for flexible handling of complex data processing.
- Load into Excel after processing and adjustment.
This leads to the automation of routine CSV data processing and the reduction of errors. Please make use of Power Query to realize efficient data processing.
