Forum Discussion
Imported web csv data not able to transform
M Code when just imported :
let
Source = Csv.Document(Web.Contents("http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv&Action=DOWNLOAD", [Headers=[Referer="http://"IP adress"/Portal/Portal.mwsl?PriNav=DataLogs"]]),null,{0, 117, 164, 185, 206},ExtraValues.Ignore,1252),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}})
in
#"Changed Type"
M Code after delimiter option
let
Source = Csv.Document(Web.Contents("http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv&Action=DOWNLOAD", [Headers=[Referer="http://"IP adres"/Portal/Portal.mwsl?PriNav=DataLogs"]]),null,{0, 117, 164, 185, 206},ExtraValues.Ignore,1252),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
#"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}, {"Column1.7", type text}, {"Column1.8", type text}})
in
#"Changed Type1"
These are the first few lines from a CSV file
| SeqNo,Date,UTC Time,Medium,Vehicle,Vehicle Source,Vehicle Type,Vehicle Type Source,Index Select,Buffering Time,Cycle Time [ms],Cycle Status,Last Step,Last Volume 1 Set [ml],Last Volume 1 Actual [ml],Last 1 Volume Tolerance Plus [ml],Last Volume 1 Tolerance Minus [ml],Last Volume 1 MaxMax [ml],Last Volume 2 Set [g],Last Volume 2 Actual [g],Last 2 Volume Tolerance Plus [g],Last Volume 2 Tolerance Minus [g],Last Volume 2 MaxMax [g],Evacuation Vacuumpipe Set [x0.1 mbar],Evacuation Vacuumpippe Actual [x0.1 mbar],System Test Pressure Set [mbar],System Test Pressure Actual [mbar],Compressed Air Fill Set [mbar],Compressed Air Fill Actual [mbar],Compressed Air Stabilize Set [mbar],Compressed Air Stabilize Actual [mbar],Compressed Air Test Set [mbar],Compressed Air Test Delta Set [mbar],Compressed Air Test Actual [mbar],Compressed Air Test Delta Actual [mbar],Compressed Air Relive 1 Set [mbar],Compressed Air Relive 1 Actual [mbar],Compressed Air Relive 2 Set [mbar],Compressed Air Relive 2 Actual [mbar],Test Schrader Valve 1 Set [x0.1 mbar],Test Schrader Valve 1 Actual [x0.1 mbar],Evacuation 1 Set [x0.1 mbar],Evacuation 1 Actual [x0.1 mbar],Evacuation 2 Set [x0.1 mbar],Evacuation 2 Actual [x0.1 mbar],Evacuation Stabilize Set [x0.1 mbar],Evacuation Stabilize Actual [x0.1 mbar],Vacuum Test Set [x0.1 mbar],Vacuum Test Delta Set [x0.1 mbar],Vacuum Test Actual [x0.1 mbar],Vacuum Test Delta Actual [x0.1 mbar],Evacuation 3 Actual [x0.1 mbar],Filling Pressure Set [mbar],Filling Pressure Actual [mbar],Filling Stabilisation Pressure Set [mbar],Filling Stabilisation Pressure Actual [mbar],Fluid Pressure Test Set [mbar],Fluid Pressure Test Delta Set [mbar],Fluid Pressure Test Actual [mbar],Fluid Pressure Test Delta Actual [mbar],Levelling Time Actual [ms], |
| 40001,2024-07-04,08:17:06.860,"ACL ","BB 482501 ", 3,"1 ", 0, 1,"04.07.24 10:17:06", 64609, 1, 62, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 14000, 9824, 14900, 16077, 0, 16049, 14500, 0, 16046, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 |
| 40002,2024-07-04,08:22:43.896,"R1234yf ","BB 482490 ", 3,"1 ", 0, 1,"04.07.24 10:22:43", 187610, 1, 62, 700, 0, 0, 0, 0, 650, 656, 25, 20, 725, 100, 35, 0, 0, 16051, 16452, 16051, 14805, 0, -2000, 15639, -412, 3000, 2972, 1300, 1037, 250, 1195, 600, 581, 30, 17, 0, 66, 0, 40, 72, 6, 18, 0, 0, 0, 0, 0, 0, 0, 0, 0 |
| 40003,2024-07-04,08:28:26.508,"ACL ","BB 482505 ", 3,"1 ", 0, 1,"04.07.24 10:28:26", 64601, 1, 62, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 14000, 10175, 14900, 16085, 0, 16057, 14500, 0, 16053, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 |
| 40004,2024-07-04,08:30:43.255,"R1234yf ","BB 482492 ", 3,"1 ", 0, 1,"04.07.24 10:30:42", 180081, 1, 62, 700, 0, 0, 0, 0, 650, 659, 25, 20, 725, 100, 39, 0, 0, 16039, 16438, 16039, 15739, 0, -2000, 15769, -270, 3000, 2964, 1300, 1031, 250, 1195, 600, 596, 30, 15, 0, 49, 0, 40, 54, 5, 15, 0, 0, 0, 0, 0, 0, 0, 0, 0 |
| 40005,2024-07-04,08:39:04.916,"ACL ","BB 482507 ", 3,"1 ", 0, 1,"04.07.24 10:39:04", 64624, 1, 62, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 14000, 10369, 14900, 16076, 0, 16048, 14500, 0, 16045, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 |
Hi DieterD
Based on the code you have offered, your url should be the path that the csv file located.
it sholuld be "https://..../ACF2.csv", you can try the following
Source = Csv.Document(Web.Contents("http://"IP adres"/DataLogs?Path=/DataLogs/ResultDataFillingEquipment_ACF2.csv", [Headers=[Referer="http://"IP adress"/Portal/Portal.mwsl?PriNav=DataLogs"]]),null,{0, 117, 164, 185, 206},ExtraValues.Ignore,1252)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.