NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
-
- Posts: 60
- Joined: August 11th, 2022, 10:27 pm
- Location: Central Ohio
- Grass Type: Mazama KBG
- Lawn Size: 5000-10000
- Level: Experienced
NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
Hi Folks,
Goal: I want to analyze "all" of the yearly metrics of all KBG cultivars at certain locations in the NTEP going back 30 years using Google Sheets (or Excel if necessary). I want to be able to pick a metric (e.g. Wilting) then pick individual cultivars (e.g. Award) and then analyze the 30 years of available data (if any) at each location.
Need: I need each year of NTEP KBG data for 30 years loaded into Sheets/Excel.
The NTEP has PDFs of the yearly data for KBG, but when pasting the data into Sheets/Excel, the data is placed into one cell. As far as I can tell, there is no delimiter used in the NTEP data, so I can't tell the import functions of Sheets/Excel it's comma delimited, semicolon delimited, etc.
I am aware that they have the NTEP Database, but you can only produce results from individual years for one metric at a time, which is inefficient considering my goal. Each year's progression is important to me.
Is anyone able to throw me a bone with a reasonable path? I'm somewhat technical (scripting/coding/macros, but not a database person obviously hah), but I just don't have a lot of experience with this type of task. Give me the gist and I'll take it from there.
Also, I'm open to better solutions than using Google Sheets/Excel.
Any help would be appreciated.
Goal: I want to analyze "all" of the yearly metrics of all KBG cultivars at certain locations in the NTEP going back 30 years using Google Sheets (or Excel if necessary). I want to be able to pick a metric (e.g. Wilting) then pick individual cultivars (e.g. Award) and then analyze the 30 years of available data (if any) at each location.
Need: I need each year of NTEP KBG data for 30 years loaded into Sheets/Excel.
The NTEP has PDFs of the yearly data for KBG, but when pasting the data into Sheets/Excel, the data is placed into one cell. As far as I can tell, there is no delimiter used in the NTEP data, so I can't tell the import functions of Sheets/Excel it's comma delimited, semicolon delimited, etc.
I am aware that they have the NTEP Database, but you can only produce results from individual years for one metric at a time, which is inefficient considering my goal. Each year's progression is important to me.
Is anyone able to throw me a bone with a reasonable path? I'm somewhat technical (scripting/coding/macros, but not a database person obviously hah), but I just don't have a lot of experience with this type of task. Give me the gist and I'll take it from there.
Also, I'm open to better solutions than using Google Sheets/Excel.
Any help would be appreciated.
- turf_toes
- Posts: 6045
- Joined: December 17th, 2008, 8:46 pm
- Location: Central NJ
- Grass Type: 77% Blueberry/23% Midnight Star KBG in front. Bewitched KBG monostand in back.
- Lawn Size: Not Specified
- Level: Not Specified
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
Aside from Midnight and a few others, you’ll probably find that most of the cultivars are in the NTEP trials for only a few years. They them are replaced with newer cultivars.
Nice idea. But the data won’t be there for most of the cultivars across such a long time frame.
Nice idea. But the data won’t be there for most of the cultivars across such a long time frame.
-
- Posts: 60
- Joined: August 11th, 2022, 10:27 pm
- Location: Central Ohio
- Grass Type: Mazama KBG
- Lawn Size: 5000-10000
- Level: Experienced
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
Agree. I wouldn't be analyzing each and every cultivar, which I kinda said earlier. These are the ones that have caught my attention.
Acoustic
After Midnight
Award (soldout)
Bewitched
Beyond
Blue Gem
Blueberry
Everest
Impact
Mazama
Midnight
Moonlight SLT
NuGlade
I already have put together a sheet of the summaries of each trial, but I want that granular data for each year because it breaks out the locations.
Where I'm at currently is looking at the summaries of each trial for these cultivars and ranking metrics against each other.
I use Midnight as the reference point and compare it to other cultivars across different trials that didn't get to directly compete against each other. E.g. Mazama and Moonlight SLT - I can use Midnight as the reference point to see how Mazama did against Midnight in its respective trial and then how Moonlight SLT did against Midnight in its respective trial and then from that conclusion, I can judge how Mazama and Moonlight SLT would compete against each other.
These cultivars are all very strong against each other. I'm wanting to take it to the next level and use the individual locations to see how they perform close to me, but I want to analyze the location data in a similar way to the picture above. Unfortunately, I live in between all the NTEP locations and the locations surrounding me don't mirror my cutting height or fertilization practices. That's why I want to get all the location metrics in front of me and analyze things closely.
Just seeing if there's a more efficient way of getting that data into a spreadsheet other than manually entering it. Feels like I'm cutting the grass one blade at a time with scissors...it'll get done, but there's gotta be a better way.
Oh btw, can you post a current year Bewitched/Blueberry pic? I lost my 1-2 year Bewitched mono stand to grubs a couple of years ago and seeded a TTTF blend as a quick fix. It's very nice, but I didn't get to see Bewitched's potential.
Acoustic
After Midnight
Award (soldout)
Bewitched
Beyond
Blue Gem
Blueberry
Everest
Impact
Mazama
Midnight
Moonlight SLT
NuGlade
I already have put together a sheet of the summaries of each trial, but I want that granular data for each year because it breaks out the locations.
Where I'm at currently is looking at the summaries of each trial for these cultivars and ranking metrics against each other.
I use Midnight as the reference point and compare it to other cultivars across different trials that didn't get to directly compete against each other. E.g. Mazama and Moonlight SLT - I can use Midnight as the reference point to see how Mazama did against Midnight in its respective trial and then how Moonlight SLT did against Midnight in its respective trial and then from that conclusion, I can judge how Mazama and Moonlight SLT would compete against each other.
These cultivars are all very strong against each other. I'm wanting to take it to the next level and use the individual locations to see how they perform close to me, but I want to analyze the location data in a similar way to the picture above. Unfortunately, I live in between all the NTEP locations and the locations surrounding me don't mirror my cutting height or fertilization practices. That's why I want to get all the location metrics in front of me and analyze things closely.
Just seeing if there's a more efficient way of getting that data into a spreadsheet other than manually entering it. Feels like I'm cutting the grass one blade at a time with scissors...it'll get done, but there's gotta be a better way.
Oh btw, can you post a current year Bewitched/Blueberry pic? I lost my 1-2 year Bewitched mono stand to grubs a couple of years ago and seeded a TTTF blend as a quick fix. It's very nice, but I didn't get to see Bewitched's potential.
- turf_toes
- Posts: 6045
- Joined: December 17th, 2008, 8:46 pm
- Location: Central NJ
- Grass Type: 77% Blueberry/23% Midnight Star KBG in front. Bewitched KBG monostand in back.
- Lawn Size: Not Specified
- Level: Not Specified
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
I suspect your results will be sketchy at best. You’ll be comparing how one cultivar did against midnight in one time period against another cultivar vs midnight in completely different time frame.
The thing you aren’t accounting for are other variables that are unique to each time frame.
Again, I understand what you are trying to do. But anyone who works with data will tell you that this test won’t provide a valid set of comparisons.
Good luck.
The thing you aren’t accounting for are other variables that are unique to each time frame.
Again, I understand what you are trying to do. But anyone who works with data will tell you that this test won’t provide a valid set of comparisons.
Good luck.
-
- Posts: 60
- Joined: August 11th, 2022, 10:27 pm
- Location: Central Ohio
- Grass Type: Mazama KBG
- Lawn Size: 5000-10000
- Level: Experienced
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
Thanks!
Sketchy is better than nothing. I still like it.
Sketchy is better than nothing. I still like it.
-
- Posts: 60
- Joined: August 11th, 2022, 10:27 pm
- Location: Central Ohio
- Grass Type: Mazama KBG
- Lawn Size: 5000-10000
- Level: Experienced
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
I loaded up Excel and gave the import a try and the web connection query is updated massively from the last time I've used it. It imports the tables pretty well. Problem solved!
- turf_toes
- Posts: 6045
- Joined: December 17th, 2008, 8:46 pm
- Location: Central NJ
- Grass Type: 77% Blueberry/23% Midnight Star KBG in front. Bewitched KBG monostand in back.
- Lawn Size: Not Specified
- Level: Not Specified
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
You’ll want to avoid posting your results anywhere online. Your project is a violation of NTEP’s terms of service and might violate copyright.
-
- Posts: 60
- Joined: August 11th, 2022, 10:27 pm
- Location: Central Ohio
- Grass Type: Mazama KBG
- Lawn Size: 5000-10000
- Level: Experienced
Re: NTEP Data Import Into Google Sheets/Excel - Is there an efficient way?
Thanks. It's for my personal use only. I deleted the screenshot just in case.
I am keeping your criticism in mind about the trial-over-trial comparisons.
I'm going to start a new thread about picking NTEP cultivars for my specific situation. I hope you'll chime in.
I am keeping your criticism in mind about the trial-over-trial comparisons.
I'm going to start a new thread about picking NTEP cultivars for my specific situation. I hope you'll chime in.
Who is online
Users browsing this forum: No registered users and 21 guests