How to Fix Incorrect CSV Delimiter Detection in Excel Power Query
Question details
Users need to correct a data import issue where Power Query automatically and incorrectly identifies colons instead of commas as the column delimiters in bank CSV files.
- Product
- Excel Power Query
- Device & OS
- not provided
- Scenario
- Importing bank statement CSV files into Excel where certain columns contain time values separated by colons.
- Observed behavior
- Power Query automatically detects colons as field separators because the time values are unquoted, which incorrectly splits single columns into multiple columns and disrupts the data structure.
Before adjusting your import settings, open the problematic CSV file in a plain text editor like Notepad to confirm that the actual data fields are separated by commas and to check if time values are enclosed in quotation marks.
Manually Define the Delimiter During Data Import
Bypass Power Query's automatic delimiter detection by explicitly selecting the comma as your delimiter in the preview window.
Power Query attempts to guess the structure of your CSV by analyzing the first 200 rows. If it encounters a high density of colons in unquoted time fields, it may override standard comma detection. Forcing the delimiter ensures consistent column parsing.
Open Excel, navigate to the 'Data' tab on the ribbon, and click 'From Text/CSV'. Select your bank CSV file and click 'Import'.
In the data preview window that appears, locate the 'Delimiter' dropdown menu at the top. Change the selection from 'Colon' or 'Custom' to 'Comma'.
Check the preview area to ensure the columns are now splitting correctly and time values remain intact.
Click 'Transform Data' to open the Power Query Editor for further cleaning, or click 'Load' to bring the data directly into your Excel spreadsheet.
Sanitize and Format the Source CSV File
If manual delimiter selection fails or causes inconsistencies, pre-formatting the source file ensures unquoted colons are properly ignored during parsing.
Easily Import and Format CSV Data with WPS Spreadsheet
WPS Spreadsheet features an intuitive, step-by-step Text Import Wizard that gives you absolute control over data delimiters. You can easily select commas and ignore colons, ensuring bank statements format perfectly without needing complex Power Query troubleshooting.
- 1. Open WPS Spreadsheet: Launch WPS Office, open the Spreadsheet application, and create a blank workbook.
- 2. Import Data: Go to the 'Data' tab on the top menu and select 'Import Data'. Choose your CSV file from the local directory.
- 3. Use Text Import Wizard: Select 'Delimited' in the first step of the wizard and click 'Next'.
- 4. Set the Delimiter: Under the Delimiters section, check the box for 'Comma' and ensure all other options (like Space or Custom) are unchecked. Click 'Finish' to load your perfectly formatted data.
Frequently Asked Questions
Why does Power Query detect a colon as a delimiter instead of a comma?
Power Query scans the first 200 rows of your dataset to guess the delimiter. If your CSV file contains numerous unquoted colons (which is common in time formats or timestamps), the engine may statistically conclude that the colon is the intended column separator.
Can I change the delimiter after the data is already imported into Power Query?
Yes. Open the Power Query Editor, navigate to the 'Applied Steps' pane on the right side of the screen, and click the small gear icon next to the 'Source' step. A dialog box will appear allowing you to change the delimiter from colon to comma.
How do quotation marks affect CSV delimiter detection?
Quotation marks act as text qualifiers in CSV files. They tell the import software to treat everything inside the quotes as a single text block. If a time value like "12:30:45" is quoted, Power Query will safely ignore the internal colons and will not misinterpret them as field separators.
