Forum Discussion
Importing text values with commas
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
let 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"}) in SplitColumnSergeiBaklan 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.
