Forum Discussion
How to import csv file starting after certain mark
amitchandak Thank you for the real quick response. It is not a fixed number. The file looks like the following:
"HYDAC SMU1270 V03.00 Data File
Start 09.03.2020 04:21:17
RecordCount 14283
Interval
DeviceCount 1
Device 0
Name SMU1270 V03.00
SerNumber 15151
MeasPoint MPNT00
Port 1
Address a
Protocol 0
ChannelCount 10
Channel 0 1 2 3 4 5 6 7 8 9
LowerRange -32768 0 0 0 0 0 0 -60 0 -25
UpperRange 32767 2147483647 2147483647 2147483647 2147483647 2147483647 2147483647 150 100 100
Unit 200-350 350-500 >500 550-750 750-1K >1K ?C % ?C
Comment
*Data*
Date Time State FE A FE B FE C NFE D NFE E NFE F Temp SatLevel Temp
09.03.2020 4:21:17 0 0 0 0 0 0 0 28 27.3 24.68
09.03.2020 4:22:17 0 0 0 0 0 0 0 28 26.28 24.66
09.03.2020 4:23:17 0 0 0 0 0 0 0 28 26.34 24.64
09.03.2020 4:24:17 0 0 0 0 0 0 0 28 27.52 24.58
09.03.2020 4:25:17 0 0 0 0 0 0 0 27.9 26.42 24.63
09.03.2020 4:26:17 0 0 0 0 0 0 0 27.9 26.65 24.61
09.03.2020 4:27:17 0 0 0 0 0 0 0 27.9 26.53 24.58
09.03.2020 4:28:17 0 0 0 0 0 0 0 27.9 26.83 24.59
09.03.2020 4:29:17 0 0 0 0 0 0 0 27.9 26.27 24.58
09.03.2020 4:30:17 0 0 0 0 0 0 0 27.7 25.69 24.56
09.03.2020 4:31:17 0 0 0 0 0 0 0 27.7 25.86 24.55
09.03.2020 4:32:17 0 0 0 0 0 0 0 27.7 25.77 24.54"
So the real data I want to import is after the red marked part "*Data*". So I was thinking if there is a way to let powerBI search for this part "*Data*" and then start to import the data after this.
Here is one way to do it. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. The first couple steps may not be relevant as I had to copy the text from your post and get it in the right format to show the approach. This adds a custom column with = if [Column1] = "*Data*" then 1 else null , and then do a Fill down on the new column, and then filter for only 1s to get rid of your header rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZLda+MwDMD/FTG4l0GCv53sZXTpysatpazdwVj34LXmLtAkxXV7//5ZTrr7SPJyBAlZ0k9SbL29XT28TicFrOYvlGkC3whPCYGp8QZm5d5u3KZGWXnjPJA8DXFGGAEibhi9oRqDz3bbuF3RnGoPVLCMo/Ox9tadzR7tqT2XW9slXEq2TiBoL0xl/5khdrVucao+rAMqw4euuTXHZVOGQvPlYt2mLRvX1Z3sds4ej2Ci2zW+2Tb7tkXxw9S13XdDkMsUnRsIUGDAQYAEBRoyyDH81Py07tnU3y0knGmVhcTfX6KiZhJTXw6HSypmamBUaJFxJf7bpDKMRaLEDnXpgRGS8OAPksgQ25wI4RYtGTw6RIIk9GsXCMZtAV+C+vzjpqps7S/Ha3zr6/ggxltYl/gSHs3ZPUxQ3aEqYBH0NOr7qGewttUBVsY/2XO4QTxhmT+WpNuRv+6MAMuA6ZQDE6nK+gQbJlSKOiCqj/AxhIuIiD4ixuaSDBE5MJgcQHSaYx8RIcX7kBqHlIwQ7UN6HJJ8bLxsHMpaKO9D+TjE9EgnTgahkC5TlUeo/0Z8aBE6KFMRkn1oaBc6SLfjiav3918=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Lines.FromText([Column1])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8", "Custom.9", "Custom.10", "Custom.11", "Custom.12", "Custom.13", "Custom.14", "Custom.15", "Custom.16", "Custom.17", "Custom.18"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}, {"Custom.4", type text}, {"Custom.5", type text}, {"Custom.6", type text}, {"Custom.7", type text}, {"Custom.8", type text}, {"Custom.9", type text}, {"Custom.10", type text}, {"Custom.11", type text}, {"Custom.12", type text}, {"Custom.13", type text}, {"Custom.14", type text}, {"Custom.15", type text}, {"Custom.16", type text}, {"Custom.17", type text}, {"Custom.18", type text}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type1", "FilterLocation", each if [Custom.1] = "*Data*" then 1 else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"FilterLocation"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([FilterLocation] = 1)),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"FilterLocation"}),
#"Removed Top Rows" = Table.Skip(#"Removed Columns1",1),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type time}, {"State", Int64.Type}, {"FE", Int64.Type}, {"A", Int64.Type}, {"FE_1", Int64.Type}, {"B", Int64.Type}, {"FE_2", Int64.Type}, {"C", Int64.Type}, {"NFE", type number}, {"D", type number}, {"NFE_3", type number}, {"E", type text}, {"NFE_4", type text}, {"F", type text}, {"Temp", type text}, {"SatLevel", type text}, {"Temp_5", type text}})
in
#"Changed Type2"
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat