Importing text values with commas

New Contributor

Got a new laptop top and with it Office 365.  I am trying to import text from a comma delimited file into Excel.  The problem is that some text values have commas, therefore they have quotes quotes around all the text.  For example, a record like 'something','company, inc',1,2,3 would parse into five columns.  Instead, the value 'company' is in one column and 'inc' is in another.  They should be in together in one column as 'company, inc'.  I have looked everywhere regarding this stupid data shaping non-sense.  I can not be the only person who has had this problem.  Previously, you could select weather text values had quotes or not.

2 Replies

@greg_wallace , here are at least two ways.


You may use legacy file import wizard which is switched off by default. To switch it on File->Options


and use it from Data->Get data->Legacy wizard. On the second step of the wizard select single quote as text qualifier



If import by default From Text/CSV it recognises double quote as text qualifier, i.e. text like

"something","company, inc",1,2,3

it will be split correctly. I don't know the way to define single quote as text qualifier. You may modify a script a bit from user interface. On first step instead of Load click transform, in formula bar modify first step of the query as import without delimiter, after that replace single quote on double one and split column with comma as delimiter.


Generated script looks like

    Source = Csv.Document(File.Contents("C:\Test\commas.txt"),[Delimiter=""""]),
    ReplaceQuote = Table.ReplaceValue(Source,"'","""",Replacer.ReplaceText,{"Column1"}),
    SplitColumn = Table.SplitColumn(ReplaceQuote, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
        {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"})




@Sergei Baklan Thanks for the heads up about the legacy wizards under the Get Data menu.  To get my job done yesterday, I copied and pasted the data and the old wizard appeared.  I will test if I can get my source to use double quotes.  I'll also test the scritps.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies