Forum Discussion
Solved
Import a Text file not working
I have the following data in a text file and I want to import it using M language : "country": "afghanistan", "capital": "kabul", "currency": "afghani", "native_language": ["dari persian", "pash...
Hello amirabedhiafi
using here Csv.Document is already the wrong approach. Use Lines.FromBinary instead. After that you need some transformation to get to the result you need. Especially because you have sometimes a second level.
Here an example how to transform your data into a real table. Be aware that I used here some Lines.FromText that you are able to simple copy paste my code into the advanced editor. You have to change the source step into Lines.FromBinary(File.Contents(...),QuoteStyle.CSV, null, 1252),
let Source = Lines.FromText("""country"": ""afghanistan"",#(cr)#(lf)""capital"": ""kabul"",#(cr)#(lf)""currency"": ""afghani"",#(cr)#(lf)""native_language"": [""dari persian"", ""pashto""],#(cr)#(lf)""famous_for"": ""rugs, taliban"",#(cr)#(lf)""phone_code"": ""+93"",#(cr)#(lf)""flag"": ""https://flagpedia.net/data/flags/h80/af.png"",#(cr)#(lf)""drive_direction"": ""right"",#(cr)#(lf)""alcohol_prohibition"": ""nationwide"",#(cr)#(lf)""area"":#(cr)#(lf)""km2"": 652864,#(cr)#(lf)""mi2"": 252072#(cr)#(lf)#(cr)#(lf)""continent"": ""as"",#(cr)#(lf)""iso"":#(cr)#(lf)""numeric"": ""004"",#(cr)#(lf)""alpha_2"": ""af"",#(cr)#(lf)""alpha_3"": ""afg""#(cr)#(lf)#(cr)#(lf)""tld"": "".af"",#(cr)#(lf)""constitutional_form"": ""republic"",#(cr)#(lf)""language_codes"": [""fa-AF"", ""ps-AF""],#(cr)#(lf)""is_landlocked"": true,#(cr)#(lf)""neighbors"": [""cn"", ""ir"", ""pk"", ""tj"", ""tm"", ""uz""],#(cr)#(lf)#(cr)#(lf)""country"": ""afghanistan"",#(cr)#(lf)""capital"": ""kabul"",#(cr)#(lf)""currency"": ""afghani"",#(cr)#(lf)""native_language"": [""dari persian"", ""pashto""],#(cr)#(lf)""famous_for"": ""rugs, taliban"",#(cr)#(lf)""phone_code"": ""+93"",#(cr)#(lf)""flag"": ""https://flagpedia.net/data/flags/h80/af.png"",#(cr)#(lf)""drive_direction"": ""right"",#(cr)#(lf)""alcohol_prohibition"": ""nationwide"",#(cr)#(lf)""area"":#(cr)#(lf)""km2"": 652864,#(cr)#(lf)""mi2"": 252072#(cr)#(lf)#(cr)#(lf)""continent"": ""as"",#(cr)#(lf)""iso"":#(cr)#(lf)""numeric"": ""004"",#(cr)#(lf)""alpha_2"": ""af"",#(cr)#(lf)""alpha_3"": ""afg""#(cr)#(lf)#(cr)#(lf)""tld"": "".af"",#(cr)#(lf)""constitutional_form"": ""republic"",#(cr)#(lf)""language_codes"": [""fa-AF"", ""ps-AF""],#(cr)#(lf)""is_landlocked"": true,#(cr)#(lf)""neighbors"": [""cn"", ""ir"", ""pk"", ""tj"", ""tm"", ""uz""]"), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","#(cr,lf)","",Replacer.ReplaceText,{"Column1.1", "Column1.2"}), #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column1.2]="" then [Column1.1] else if [Column1.1]="" then "" else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1.1] <> "") and ([Column1.2] <> "")), #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Custom]=null or [Custom]="" then [Column1.1] else [Custom]&": "&[Column1.1]), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Column1.2", "Custom.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Custom.1", "Column1.2"}), #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom.1"}, {{"AllRows", each Table.PromoteHeaders(Table.Transpose(_)) }}, GroupKind.Local, (g,c)=> if g[#"Custom.1"]= c[#"Custom.1"] then -1 else 0), #"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows",{"AllRows"}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns1", "AllRows", {"country", "capital", "currency", "native_language", "famous_for", "phone_code", "flag", "drive_direction", "alcohol_prohibition", "area: km2", "area: mi2", "continent", "iso: numeric", "iso: alpha_2", "iso: alpha_3", "tld", "constitutional_form", "language_codes", "is_landlocked", "neighbors"}, {"country", "capital", "currency", "native_language", "famous_for", "phone_code", "flag", "drive_direction", "alcohol_prohibition", "area: km2", "area: mi2", "continent", "iso: numeric", "iso: alpha_2", "iso: alpha_3", "tld", "constitutional_form", "language_codes", "is_landlocked", "neighbors"}) in #"Expanded AllRows"Outcome:
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Jimmy801 When I change the source to :
Source=Lines.FromBinary(File.Contents(MyPath),QuoteStyle.CSV, null, 1252)
I get empty data starting from the groupedrows step
Hello amirabedhiafi
I've put your provided code two times into one text file and applied this code
let
Source = Lines.FromBinary(File.Contents("YOURTEXTFILEWITH2TIMESYOURTEXTINIT"), QuoteStyle.Csv, true, 1252),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","#(cr,lf)","",Replacer.ReplaceText,{"Column1.1", "Column1.2"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column1.2]="" then [Column1.1] else if [Column1.1]="" then "" else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column1.1] <> "") and ([Column1.2] <> "")),
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Custom.1", each if [Custom]=null or [Custom]="" then [Column1.1] else [Custom]&": "&[Column1.1]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Column1.2", "Custom.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Custom.1", "Column1.2"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Custom.1"}, {{"AllRows", each Table.PromoteHeaders(Table.Transpose(_)) }}, GroupKind.Local, (g,c)=> if g[#"Custom.1"]= c[#"Custom.1"] then -1 else 0),
#"Removed Other Columns1" = Table.SelectColumns(#"Grouped Rows",{"AllRows"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns1", "AllRows", {"country", "capital", "currency", "native_language", "famous_for", "phone_code", "flag", "drive_direction", "alcohol_prohibition", "area: km2", "area: mi2", "continent", "iso: numeric", "iso: alpha_2", "iso: alpha_3", "tld", "constitutional_form", "language_codes", "is_landlocked", "neighbors"}, {"country", "capital", "currency", "native_language", "famous_for", "phone_code", "flag", "drive_direction", "alcohol_prohibition", "area: km2", "area: mi2", "continent", "iso: numeric", "iso: alpha_2", "iso: alpha_3", "tld", "constitutional_form", "language_codes", "is_landlocked", "neighbors"})
in
#"Expanded AllRows"and this is the outcome
if this is not working, your textfile doesn't contain the text you provided. In case post your text-file here, then I can have another look
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy