cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mahe Frequent Visitor
Frequent Visitor

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 time intervals ( import every 5 min)

2. Extract data from each file and you can see the format, below:

    {@ROW1|Project|I5000|V12
    {@ROW2|V12V12126A5|00|date|time

    }}

  a. Extract data from row 1 ( second field ) and fields are delimited by pipe.

  b. Extract data from row 2 ( second field and the third field).

 3. I would like to create a real-time analysis of data extracted from text files.

 

I'm sorry if my post is not accepted but it's worth trying

 

Thnak you in advance.

Mahe 

 

   

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Import and extract data from text files

@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.
1.JPG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Import and extract data from text files

@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.
1.JPG

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mahe Frequent Visitor
Frequent Visitor

Re: Import and extract data from text files

Many thanks for feedback, Lydia and I'll try and keep in touch with you.

mahe Frequent Visitor
Frequent Visitor

Re: Import and extract data from text files

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