'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Import CSV with Commas Inside Brackets into Excel
Question details
The user needs to import a CSV file into Excel without the software splitting values that contain internal commas enclosed in brackets (e.g., [1,2,3]).
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing non-standard CSV data into a spreadsheet for analysis.
- Observed behavior
- When opening the CSV directly, Excel interprets every comma as a delimiter, incorrectly splitting single bracketed values across multiple columns.
Ensure you have the CSV file saved locally on your computer and close it if it is currently open in Excel or another text editor.
Use Power Query to Import and Transform the CSV File
Power Query allows you to bypass default Excel behaviors by defining custom text qualifiers and parsing rules, ensuring your bracketed data remains intact.
Opening a CSV file directly by double-clicking forces Excel to use its default parsing settings, which treat every comma as a hard column separator. By using Power Query, you gain full control over how the text is read and processed before it ever touches your spreadsheet.
Launch Excel, navigate to the 'Data' tab on the top ribbon, and select 'Get Data' > 'From File' > 'From Text/CSV'.
Locate the problematic CSV file on your computer, select it, and click 'Import'.
A preview window will appear. Do not click Load. Instead, click 'Transform Data' to open the Power Query Editor.
If your file does not use standard quotation marks as text qualifiers, use the 'Replace Values' feature to change brackets to double quotes, or adjust the delimiter settings so the comma inside the brackets is ignored during the split.
Once the data preview shows the bracketed values perfectly contained within a single column, click 'Close & Load' in the top left corner to import the corrected data into your worksheet.
Import Complex CSV Files Easily with WPS Spreadsheet
WPS Spreadsheet features an intuitive Text Import Wizard that provides precise control over delimiters and text qualifiers, making it simple to accurately import complex data like bracketed commas without advanced coding or query knowledge.
- 1. Launch WPS Spreadsheet: Open WPS Office and create a new blank spreadsheet.
- 2. Initiate data import: Navigate to the 'Data' tab and click on 'Import Data', then select your CSV file.
- 3. Configure the wizard: In the Text Import Wizard, select the 'Delimited' option and click Next.
- 4. Set delimiters and qualifiers: Check the 'Comma' box for your delimiter and choose the appropriate Text Qualifier from the dropdown to ensure your bracketed or quoted text is protected.
- 5. Finish the import: Preview your columns in the window at the bottom, then click 'Finish' to successfully load your intact data.
Frequently Asked Questions
Why does Excel split my CSV data at every comma?
By default, Excel treats the comma character as a hard column separator for CSV (Comma-Separated Values) files. Unless the text containing the comma is wrapped in standard text qualifiers like double quotes, Excel assumes it is supposed to start a new column.
Can I change the default CSV delimiter in Excel?
Yes, Excel's default list separator is tied to your Windows Regional Settings. You can change this in the Windows Control Panel under 'Region' > 'Additional settings' > 'List separator'. However, changing this affects all of Windows, so using Power Query or a Text Import Wizard is usually much safer.
How do I format my CSV so Excel opens it correctly by default?
If you are generating the CSV from a script, database, or API, make sure any field that contains a comma is wrapped entirely in double quotation marks. For example: bob, "[1,2,3]", red. Excel automatically recognizes quotes as text qualifiers and keeps the contents in one cell.
