Forum Discussion
Solved
Import and extract data from text files
Hi there, I'm writing first time on this forum and I need an idea or support to import/extract data from text files. 1. Import all the text files from specific location and update at regular ti...
mahe,
Firstly, do you put all the text files in a specific folder? If so, use Folder connector in Power BI Desktop to connect to your source and you can import all text files from the folder. And you can click Refresh button in Power BI Desktop every 5 min to get updated data.
Secondly, does the data that you want to extract exist in a single field? If so, you can perform a series of steps(split column, remove columns, merge columns ) in Query Editor to extract data. The steps generate the following code in Advanced Editor, you can copy it and paste it to the Advanced Editor of a blank query to test the process.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOrRAodohyD/csCagKD8rNbmkxtPUwMCgJszQKCYPKAmTNwKJAJGhkZmjaQ1QQUpiSWpNSWZuKkxZba1SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}, {"Column1.2.4", type text}}), #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Column1.4", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.4.1", "Column1.4.2", "Column1.4.3", "Column1.4.4", "Column1.4.5"}), #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Column1.1", "Column1.2.1", "Column1.2.3", "Column1.2.4", "Column1.3", "Column1.4.1", "Column1.4.4", "Column1.4.5", "Column1.5", "Column1.6"}), #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column1.4.2", "Column1.4.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged") in #"Merged Columns"
Thirdly, please review this article about creating a real-time analysis of data in Power BI. You would need to create a stream dataset in your scenario.
Regards,
Lydia
mahe,
Firstly, do you put all the text files in a specific folder? If so, use Folder connector in Power BI Desktop to connect to your source and you can import all text files from the folder. And you can click Refresh button in Power BI Desktop every 5 min to get updated data.
Secondly, does the data that you want to extract exist in a single field? If so, you can perform a series of steps(split column, remove columns, merge columns ) in Query Editor to extract data. The steps generate the following code in Advanced Editor, you can copy it and paste it to the Advanced Editor of a blank query to test the process.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WOrRAodohyD/csCagKD8rNbmkxtPUwMCgJszQKCYPKAmTNwKJAJGhkZmjaQ1QQUpiSWpNSWZuKkxZba1SbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}, {"Column1.2.3", type text}, {"Column1.2.4", type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Column1.4", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.4.1", "Column1.4.2", "Column1.4.3", "Column1.4.4", "Column1.4.5"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter2",{"Column1.1", "Column1.2.1", "Column1.2.3", "Column1.2.4", "Column1.3", "Column1.4.1", "Column1.4.4", "Column1.4.5", "Column1.5", "Column1.6"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column1.4.2", "Column1.4.3"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged")
in
#"Merged Columns"
Thirdly, please review this article about creating a real-time analysis of data in Power BI. You would need to create a stream dataset in your scenario.
Regards,
Lydia
Hi there,
I'm back with my feedback and many thanks Lydia.
Please, see my code and of course can be shorter but at the moment it's ok for me and my priority is data accuracy, now.
let
Source = Folder.Files("Folder\trial"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "Transform File from trial (2)", each #"Transform File from trial (2)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from trial (2)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from trial (2)", Table.ColumnNames(#"Transform File from trial (2)"(#"Sample File (5)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", Int64.Type}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type number}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}, {"Column1.13", type text}, {"Column1.14", type text}, {"Column1.15", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"",Replacer.ReplaceValue,{"Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15"}),
#"Filtered Rows" = Table.SelectRows(#"Replaced Value", each not Text.StartsWith([Source.Name], "0")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Column1.2], "W") or Text.StartsWith([Column1.2], "V") or Text.StartsWith([Column1.2], "U")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.12", "Column1.13", "Column1.14", "Column1.15"}),
#"Split Column by Position" = Table.SplitColumn(#"Removed Columns1", "Column1.11", Splitter.SplitTextByRepeatedLengths(6), {"Column1.11.1", "Column1.11.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.11.1", Int64.Type}, {"Column1.11.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1.2", "SerisalNumber"}, {"Column1.3", "Status"}, {"Column1.11.1", "Date"}, {"Column1.11.2", "Time"}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Renamed Columns",{{"Source.Name", type text}, {"SerisalNumber", type text}, {"Status", type text}, {"Date", type text}, {"Time", type text}}),
#"Replaced Value1" = Table.ReplaceValue(#"Changed Type3","00","Pass",Replacer.ReplaceText,{"Status"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","02","Fail1",Replacer.ReplaceText,{"Status"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","03","Fail2",Replacer.ReplaceText,{"Status"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","04","Fail3",Replacer.ReplaceText,{"Status"}),
#"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","06","Fail4",Replacer.ReplaceText,{"Status"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","07","Fail5",Replacer.ReplaceText,{"Status"}),
#"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","08","Fail6",Replacer.ReplaceText,{"Status"}),
#"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","09","Fail7",Replacer.ReplaceText,{"Status"}),
#"Replaced Value9" = Table.ReplaceValue(#"Replaced Value8","10","Fail8",Replacer.ReplaceText,{"Status"}),
#"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","80","Error",Replacer.ReplaceText,{"Status"}),
#"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","82","Break",Replacer.ReplaceText,{"Status"}),
#"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","14","Fail9",Replacer.ReplaceText,{"Status"}),
#"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","12","Fail10",Replacer.ReplaceText,{"Status"}),
#"Replaced Value14" = Table.ReplaceValue(#"Replaced Value13","24","Fail11",Replacer.ReplaceText,{"Status"}),
#"Renamed Columns2" = Table.RenameColumns(#"Replaced Value14",{{"SerisalNumber", "SerialNumber"}})
in
#"Renamed Columns2"
The next step is to create a stream dataset but I need to read about this or give me a short advice.
Thanks,
Mahe