I have a workflow that scrapes weather data for 3 cities and 12 days from a website, creates a data table, then writes that data table to an excel file with the columns “City Name”, “Date”, “High”, “Low”, “Forecast Description”, “Chance of Rain”.
For the most part it works well, but the data table that is outputted from the Data Scraping Wizard has the columns for everything but “City Name” since that is not something that is scraped but rather a value that is being passed to the For Each activity.
What I want is to add the City Name column to my data table and make the value for each row for that column the city name (item.ToString), add the headers for each of the columns in the excel file, and to make the ‘Chance of Rain’ column be outputted as a number rather than text.
I have attached my workflow with annotations for reference. Any help would be extremely appreciated.
Here is what my excel output currently looks like. Notice that the header names are missing and that the chance of rain column is giving an error in excel cuz its reading it as a string?
I tried looking for the add headers option but I didn’t find it for the Data Scraping Wizard or Append Range. I thought I couldn’t use Write Range because I don’t want my values to be accidentally overwritten since I want to run this workflow often.
Hi, only write range has the add headers options. If you want to only use append range and keep adding to the same already existing file, you can simply manually add the headers to the file beforehand.
In order to add the city name to the table, you can use the build data table activity and build the DT with the city name column included before the extraction happens, then you can simply use a for each row to add the city name afterwards.
As for the Chance of Rain column, using the Excel Scope version of append range should allow it to automatically format the column properly, not sure why it does not happen with the workbook version.
Thanks again @Bogdan_Eusebiu_Siritanu!! I can’t believe it was such a relatively straightforward fix haha. I appreciate it. \
Since I want to keep adding to the existing file, you mentioned I should just manually add the headers to the Excel file but do you think it would be better practice to use write range and set the range to start after the last row of data.
Would it also be best practice to extract certain steps as separate workflows?
Writing the headers is something you need to do once per table, if you plan on having only one for all runs, no need to use write range. As an example, if you want to run the robot once per day and have a separate file for each run, then you would need to use write range.
Separating the process into workflows by responsibilities is definitely best practice. If the process is simple as in this case, it’s fine as it is but you can still do it to get into the habit of having them properly organized.
