Importing fixed-width column data to Excel can be challenging when each column has a set number of characters, often resulting in unnecessary spaces. In this article, I’ll show you how to properly import fixed-width CSV files using Power Query, ensuring clean data without those problematic extra spaces.
This technique is essential for handling structured data exports from legacy systems or standardized reports.
Understanding Fixed-Width Column Data
Fixed-width column data is a specific format where each column occupies exactly the same number of characters in every row. Unlike delimited formats (like comma-separated values), fixed-width files allocate a predetermined number of spaces for each field. This means shorter values get padded with spaces to maintain the fixed width. For example, the date column might always be 10 characters wide, the merchant column 20 characters, and so on.
This format is common in older systems, government data, and standardized reports, but it presents unique challenges when importing to Excel. The main issue is that these extra spaces can cause problems with data analysis and visualization if not properly handled during the import process.
Importing Fixed-Width Data Using Power Query
Power Query provides excellent tools for handling fixed-width column data. Here’s the process to properly import this type of data:
Initial Import Steps
To begin importing fixed-width data, follow these steps:
- Go to the Data tab in Excel
- Click on “From Text/CSV” option
- Locate and select your fixed-width file
When the Power Query Editor opens, you might need to adjust the window size to see all available options, as sometimes the third column of settings may be hidden if the window is too narrow. Make sure to expand the window to see all import settings.
Configuring Import Settings
During the import process, you’ll need to configure several important settings:
First, check the file origin (encoding) — UTF‑8 works well for most modern files. Then, verify that the delimiter is set correctly. For fixed-width files, Power Query typically automatically detects this format. You should see “Fixed Width” selected rather than comma, tab, or other delimiters.
The data type detection setting is another crucial choice. You have three options:
- Based on first 200 rows
- Based on entire dataset
- Do not detect data types
In most cases with fixed-width data, it’s better to select “Do not detect data types” initially and handle data type conversion after cleaning the spaces. This prevents Power Query from making assumptions about your data types that might be affected by the extra spaces.
Handling Extra Spaces in Power Query
The key challenge with fixed-width data is managing the extra spaces that pad each column. When examining the data in Power Query, you’ll notice these extra spaces in both the column headers and text fields. These spaces are necessary in the original file to maintain the fixed-width format, but they’re problematic for analysis in Excel.
For example, when clicking on a column header, you might see something like “Date ” with several spaces after the actual name. Similarly, text fields like product names or merchant names will have trailing spaces to fill their allocated width.
Removing Extra Spaces
To clean up the data properly:
- Select all columns in the Power Query Editor by clicking the first column header and then Shift-clicking the last column
- Go to the Transform tab
- Click on “Format” and select “Trim”
This important step removes all leading and trailing white spaces from every cell in your dataset while preserving spaces that are actually part of your data (like spaces between words in product names). After trimming the data, you’ll notice a significant improvement in how your data looks.
Setting Headers and Data Types
After removing the extra spaces, you can proceed with configuring headers and data types:
Use the “Use First Row as Headers” option (found in the Transform tab or by right-clicking on the first row) to set your headers properly. Now that the spaces have been removed, your column names will appear clean and professional.
With clean data, you can now set appropriate data types for each column. For example, date columns should be formatted as dates, and revenue or cost columns as currency or decimal numbers. Power Query will help identify appropriate types, but you can manually change them by clicking the data type icon in each column header.
Loading Data to Excel
The final step is to load your cleaned data into Excel:
- Click “Close & Load” (or “Close & Load To” for more options)
- Select whether to load as a table or to a specific location
- Choose an existing worksheet or create a new one
When loading the data, you have the option to add it to the data model if you plan to use it with Power Pivot, but this isn’t necessary for basic Excel analysis. One important thing to note is that while Power Query may detect currency data types, this doesn’t automatically apply Excel’s currency formatting to your table. You’ll need to apply appropriate number formatting in Excel after importing.
Fortunately, when you refresh your data in the future, Excel will maintain any number formatting you’ve applied to the table, making future updates seamless.
Benefits of Using Power Query for Fixed-Width Data
Using Power Query for importing fixed-width data offers several advantages over other methods:
The ability to transform data before loading it to Excel saves significant time compared to manual cleanup after import. The process is also repeatable — if you receive updated versions of the same file, you can simply refresh your query rather than repeating the entire import and cleanup process.
Additionally, Power Query maintains a record of all transformations applied, making your data preparation process transparent and easily adjustable if needed. This approach ensures consistency in how your fixed-width data is processed each time.