Forum Discussion
Get Data
I'm not sure if I'm wording this right. and I'm also new to the following. I am using the "get data from the web" in Excel. I am receiving the data no problem. but as the file gets larger it takes t...
I might have misunderstood you. Re-reading, Andytcc said I'm actually getting the data from a csv file on an SD card in a PLC. I'm doing it with an IP address and the name of the file
So, where you trying to say he actually uses Web.Contents (probably) and not File.Contents?
(that would explain the confusion re. I am using the "get data from the web")
Perhaps that's me was confused with "get data from the web". Yes, assumed web connector.
Anyway, the task is not clear enough, at least for me. If refresh is once per week that's one story, if every hour that's another. Do we do refresh manually (e.g. on file opening) or Power Automate triggered on csv file update could work. Could we modify csv file update not to keep entire history in it (or keep it separately). Etc.
- Refresh is done every time file is opened could be multiple times a day to weekly.
What I mean by hourly is: PLC will do an hourly write to the csv file of the 201 values.
The reason for hourly is: The logging is for temperature in a Grain Bin. So if I do an hourly log that gives me a good deviation in a 24hr period between min/max temp and therefore I can see which sensors are in the grain and with that the number of Bushels still in the Bin (not accurately to the bushel but a very good idea).If you want to post your Query code here...
In Excel
- Data (tab) > Queries & Connections (the corresponding pane opens on the right
- Right-click on your query > CopySwitch to this site
- Reply on this Discussion > Open full text editor
- In the Toolbar: Insert/Edit code sample
- Language: 'Power Query'
- In the text box below: Paste > OK// Bins 5-8 Temp let Source = Csv.Document(Web.Contents("ftp://10.0.0.196/TempLog.csv"),[Delimiter=",", Columns=201, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Change Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type number}, {"Column7", type number}, {"Column8", type number}, {"Column9", type number}, {"Column10", type number}, {"Column11", type number}, {"Column12", type number}, {"Column13", type number}, {"Column14", type number}, {"Column15", type number}, {"Column16", type number}, {"Column17", type number}, {"Column18", type number}, {"Column19", type number}, {"Column20", type number}, {"Column21", type number}, {"Column22", type number}, {"Column23", type number}, {"Column24", type number}, {"Column25", type number}, {"Column26", type number}, {"Column27", type number}, {"Column28", type number}, {"Column29", type number}, {"Column30", type number}, {"Column31", type number}, {"Column32", type number}, {"Column33", type number}, {"Column34", type number}, {"Column35", type number}, {"Column36", type number}, {"Column37", type number}, {"Column38", type number}, {"Column39", type number}, {"Column40", type number}, {"Column41", type number}, {"Column42", type number}, {"Column43", type number}, {"Column44", type number}, {"Column45", type number}, {"Column46", type number}, {"Column47", type number}, {"Column48", type number}, {"Column49", type number}, {"Column50", type number}, {"Column51", type number}, {"Column52", type number}, {"Column53", type number}, {"Column54", type number}, {"Column55", type number}, {"Column56", type number}, {"Column57", type number}, {"Column58", type number}, {"Column59", type number}, {"Column60", type number}, {"Column61", type number}, {"Column62", type number}, {"Column63", type number}, {"Column64", type number}, {"Column65", type number}, {"Column66", type number}, {"Column67", type number}, {"Column68", type number}, {"Column69", type number}, {"Column70", type number}, {"Column71", type number}, {"Column72", type number}, {"Column73", type number}, {"Column74", type number}, {"Column75", type number}, {"Column76", type number}, {"Column77", type number}, {"Column78", type number}, {"Column79", type number}, {"Column80", type number}, {"Column81", type number}, {"Column82", type number}, {"Column83", type number}, {"Column84", type number}, {"Column85", type number}, {"Column86", type number}, {"Column87", type number}, {"Column88", type number}, {"Column89", type number}, {"Column90", type number}, {"Column91", type number}, {"Column92", type number}, {"Column93", type number}, {"Column94", type number}, {"Column95", type number}, {"Column96", type number}, {"Column97", type number}, {"Column98", type number}, {"Column99", type number}, {"Column100", type number}, {"Column101", type number}, {"Column102", type number}, {"Column103", type number}, {"Column104", type number}, {"Column105", type number}, {"Column106", type number}, {"Column107", type number}, {"Column108", type number}, {"Column109", type number}, {"Column110", type number}, {"Column111", type number}, {"Column112", type number}, {"Column113", type number}, {"Column114", type number}, {"Column115", type number}, {"Column116", type number}, {"Column117", type number}, {"Column118", type number}, {"Column119", type number}, {"Column120", type number}, {"Column121", type number}, {"Column122", type number}, {"Column123", type number}, {"Column124", type number}, {"Column125", type number}, {"Column126", type number}, {"Column127", type number}, {"Column128", type number}, {"Column129", type number}, {"Column130", type number}, {"Column131", type number}, {"Column132", type number}, {"Column133", type number}, {"Column134", type number}, {"Column135", type number}, {"Column136", type number}, {"Column137", type number}, {"Column138", type number}, {"Column139", type number}, {"Column140", type number}, {"Column141", type number}, {"Column142", type number}, {"Column143", type number}, {"Column144", type number}, {"Column145", type number}, {"Column146", type number}, {"Column147", type number}, {"Column148", type number}, {"Column149", type number}, {"Column150", type number}, {"Column151", type number}, {"Column152", type number}, {"Column153", type number}, {"Column154", type number}, {"Column155", type number}, {"Column156", type number}, {"Column157", type number}, {"Column158", type number}, {"Column159", type number}, {"Column160", type number}, {"Column161", type number}, {"Column162", type number}, {"Column163", type number}, {"Column164", type number}, {"Column165", type number}, {"Column166", type number}, {"Column167", type number}, {"Column168", type number}, {"Column169", type number}, {"Column170", type number}, {"Column171", type number}, {"Column172", type number}, {"Column173", type number}, {"Column174", type number}, {"Column175", type number}, {"Column176", type number}, {"Column177", type number}, {"Column178", type number}, {"Column179", type number}, {"Column180", type number}, {"Column181", type number}, {"Column182", type number}, {"Column183", type number}, {"Column184", type number}, {"Column185", type number}, {"Column186", type number}, {"Column187", type number}, {"Column188", type number}, {"Column189", type number}, {"Column190", type number}, {"Column191", type number}, {"Column192", type number}, {"Column193", type number}, {"Column194", type number}, {"Column195", type number}, {"Column196", type number}, {"Column197", Int64.Type}, {"Column198", Int64.Type}, {"Column199", Int64.Type}, {"Column200", Int64.Type}, {"Column201", Int64.Type}}), #"Kept Last Rows" = Table.LastN(#"Change Type", 4395), #"Reversed Rows" = Table.ReverseRows(#"Kept Last Rows") in #"Reversed Rows"
Perhaps that's me was confused with "get data from the web". Yes, assumed web connector
And you're probably right as after re-reading (I overlooked on 1st reading): ...with an IP address
Anyway, the task is not clear enough, at least for me
Same here. Let's see how it develops...
- I reread your reply and refer to Question 4 in you first reply.
Q4: Not sure I understand the logic here. Shouldn't this be "Reverse Rows" and "Keep first Rows"?
Not sure (to be tested and with Power Query expect 'surprises') but instinctively I would do - only - Keep Bottom Rows (201) instead.
I actually "Keep last Rows" then "Reverse Rows" - Thanks everyone for all the input.
Sorry for not replying sooner but I was out all day yesterday until very late. Just so you know I'm still here I'll give a quick reply.
I have read all your comments (not all the links though) was not sure to who to reply so I picked the last one.
I don't understand some of what was posed since I'm new to power query also a new member to the community and not sure how things are done here so, please bear with me.
You had a few questions in your first reply.
Q1: So CSV only, correct? A: Yes ("Get data from the web through FTP/IP Address/.CSV)
Q2: Does PLC mean 'Programmable Logic Controller? (not necessarily important but curious) A: Yes
Q3: As I understand there would be no way/chance to Filter data at the Source (PLC), correct. A: No (no way possible. loads 201 columns (not rows -my mistake-) in the next row down, limited by size of SD card.
Q4: Not sure I understand the logic here. Shouldn't this be "Reverse Rows" and "Keep first Rows"? A: I could post the actual file but you'll' have to help me. I am limited online so the easiest way would be to somehow post it right here but I don't know how.
