2

My company recently upgraded from Excel 2016 to Microsoft 365. Before the change, when importing from Text/CSV the wizard would have an option for the delimiter and the quote character. Now, there is an option for specifying the delemiter, but anything more requires opening PowerQuery to tweak from there (quote handling, encoding, headers, column type, etc).

So far I have found no way to specify that the CSV file (an export from another system I have no control over) uses single quotes.

  • I do not want to blindly replace or remove quotes, this could have unintended consequences depending on the actual CSV content
  • Including the quotes in the delimiter kind of works ('|'), but ignore the first and last of the row and, again, will break if there are quotes/commas in the content of the CSV
  • I have other tools that could handle this conversion as a preprocessing step but I would much rather an Excel-only solution. If for no other reason than others at my company will need to do this also and distributing tools and training them to import a CSV seems excessive.

How can the quote character be specified using Excel for Microsoft 365?

Improve this question

2 Answers 2

Reset to default
2

Excel 365 has both options for import. The option is called "Text qualifier":

Text Import Wizard

This importer can be enabled in the Excel Options screen

Options Screen

Improve this answer
4
  • Yes, that is the wizard I'm familiar with. How did you get there? If I go Data->From Text/CSV That is not what comes up Commented Sep 23, 2021 at 15:12
  • It's called the classic external data import. You will need to customize the ribbon and add the icon into some pane. You find it under "All commands" named "Import External Data". Sorry for not remembering that Microsoft squirreled it away. Commented Sep 23, 2021 at 15:37
  • Perfect. Thank you. I've added that step to the answer (pending review approval). Commented Sep 23, 2021 at 16:03
  • You're welcome. I have approved your edit. I see you have found a setting for it, which is better than customizing the ribbon. Commented Sep 23, 2021 at 16:26
0

An addendum to the answer above, for Excel newbies.
Once you've enabled a legacy wizard, you can then find it via the Data tab --> Get Data dropdown --> Legacy Wizards.

Accessing Legacy Wizard:
Accessing Legacy Wizard

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.