Use Excel Power Query to Convert Variable-Size CSV Files to XLSX
Question details
Import CSV files with changing row counts, remove rows whose column L value is zero, and create an Excel table without hard-coded row limits.

- Product
- Microsoft Excel Power Query and Office Scripts
- Device & OS
- not provided
- Scenario
- The user needs a repeatable CSV-to-XLSX workflow that works when each source CSV contains a different number of populated rows.
- Observed behavior
- A fixed row range in an Office Script does not adapt when the imported CSV contains more or fewer rows than expected.
Confirm that the CSV has a consistent header row and that the values to evaluate are in column L. In Excel desktop, use a current version that includes the Data tab and Get & Transform Data tools.
Import and filter the variable-size CSV with Excel Power Query
Use this recommended method when you want a refreshable import that automatically handles changing CSV row counts.
Power Query reads the available data from the CSV instead of relying on a predefined worksheet range. You can apply the filter once and refresh the query whenever a new CSV is available.
In Excel, select Data > From Text/CSV, choose the CSV file, and review the preview in the import window.
Select Transform Data to open Power Query Editor before loading the file into the workbook.
Select the column that corresponds to column L, open its filter arrow, and clear the value 0. If the column is numeric, use Number Filters > Does Not Equal and enter 0.
Select Home > Close & Load To, choose Table, and select the worksheet location for the imported results.
Select File > Save As and choose Excel Workbook (*.xlsx). To process updated source data later, select Data > Refresh All.

Make an Office Script use the worksheet used range
Use this option when CSV data is already placed on a worksheet and your workflow must remain script-based.
Open, filter, and save CSV data with WPS Spreadsheet
If you need a free, lightweight spreadsheet application for everyday CSV cleanup and workbook conversion, WPS Office provides a familiar spreadsheet interface and supports a smooth transition from Microsoft Office files. Open the CSV in WPS Spreadsheet, filter the relevant column, and save the result as an Excel-compatible workbook.

Frequently Asked Questions
Why does Power Query work better than a fixed Office Script range for CSV files?
Power Query imports the rows that exist in the selected CSV at refresh time. Unlike a fixed range such as A1:L145, it does not require you to change the row limit when the source file grows or shrinks.
How do I exclude rows where column L equals zero in Power Query?
In Power Query Editor, select the column corresponding to column L, open the filter menu, and exclude 0. For numeric data, select Number Filters > Does Not Equal and enter 0.
Can Power Query load the filtered CSV results into an Excel table?
Yes. After applying transformations, select Home > Close & Load To, choose Table, and select the worksheet destination. Excel creates a table from the query output.
Can Office Scripts import a CSV file from my computer by themselves?
Office Scripts primarily automate workbook content and do not directly browse local files. Use Power Query for interactive CSV imports, or use Power Automate with Office Scripts for an automated file-based workflow.
How do I update the table when I receive a new CSV file?
If the query points to the same file path, replace the source CSV and select Data > Refresh All. If the file name or location changes, update it through Data > Queries & Connections and edit the query source settings.




