Export Timetable data to Excel

I have a lot of rainfall data which has been uploaded to thingspeak. I want to download some of that data and analyse it in Excel. I was hoping to use the MATLab analysis to do this.

Bearing in mind I am fairly new to this and programming isn't my thing.

I was hoping to use the following code which as far as I understand extracts the data in TimeTable format and then use the writetimetable function to create an Excel spreadsheet. When I run it it extracts the data and displays it as I want without error but I don't know what happens to the xlsx file.

I'm probably missing something

readChannelID = ******; 
readAPIKey = '******';

RainFieldID = 1; 

RainFall = thingSpeakRead(readChannelID,'Fields',RainFieldID,'NumDays',7,'ReadKey',readAPIKey,OutputFormat='TimeTable');

display(RainFall,'Rainfall');

writetimetable(RainFall,'TT.xlsx')

[Stephen23]

Using WRITETIMETABLE is a better approach than converting to table and then using WRITETABLE.

Answer 1

[Umar]

Hi @chris weedon ,

After reviewing the “writetimetable” function at the link provided below

https://www.mathworks.com/help/matlab/ref/writetimetable.html

Based on your requirements, let me clarify the code you have provided and make sure it works effectively for your needs. The key steps in your approach involve reading data from ThingSpeak, displaying it, and writing it to an Excel file. Below is an updated version of your MATLAB code, along with explanations.

% Define your channel ID and API key
readChannelID = ******; 
readAPIKey = '******';

% Specify the field ID for rainfall data
RainFieldID = 1;

% Read the rainfall data into a timetable format for the last 7 days
RainFall = thingSpeakRead(readChannelID, 'Fields', RainFieldID, ...
  'NumDays', 7, 'ReadKey', readAPIKey, 'OutputFormat', 'TimeTable');

% Display the retrieved rainfall data
display(RainFall, 'Rainfall');

% Specify the filename for the output Excel file
outputFileName = 'RainfallData.xlsx';

% Write the timetable to an Excel spreadsheet
writetimetable(RainFall, outputFileName);

% Display a message indicating where the file has been saved
fprintf('The rainfall data has been saved to %s\n', outputFileName);

Now, let me explain the key component used in above modified code,

Reading Data

The thingSpeakRead function retrieves rainfall data from your specified ThingSpeak channel over a period of 7 days. The OutputFormat parameter is set to TimeTable, which formats your data as a timetable.

Displaying Data

The display function shows the retrieved rainfall data in your MATLAB command window.

Writing to Excel

The writetimetable function which was used in your provided code is being called with two arguments: the timetable variable (`RainFall`) and the desired filename (‘RainfallData.xlsx'). This makes sure that your data is saved correctly. Also, make sure that you have write permissions in your current working directory or specify an absolute path if needed (e.g., C:\path\to\your\folder\RainfallData.xlsx)

Output Confirmation

A simple fprintf statement at the end confirms where the file has been saved. This message will help you locate your file easily.

Please bear in mind that by default, MATLAB saves files in its current working directory. You can check or change this directory using the pwd command to print the current directory or cd('desired_path') to change it.

Hope this helps resolve your problem. Please let me know if you have any further questions for us.

[chris weedon]

Thanks for all your help.

I am running MATLab analysis through the App on Thingspeak.com rather than on my PC so I am guessing thats why I can't see the file.

If I run pwd the location given is:

'/users/mss.system.7f44d'

[Umar]

Hi @chris weedon,

Now that you mentioned that you are operating within a web app, you need to download the file directly after it is created. To ensure clarity in your code and facilitate downloading, consider adding a notification or confirmation message indicating that the file has been created. Here’s an improved version of your existing code that includes a message about where to find or how to download the file:

   % Define your channel ID and API key
   readChannelID = ******; 
   readAPIKey = '******';

   % Specify the field ID for rainfall data
   RainFieldID = 1;

   % Read the rainfall data into a timetable format for the 
   %last 7 days
   RainFall = thingSpeakRead(readChannelID, 'Fields', RainFieldID, 
   ...'NumDays', 7, 'ReadKey', readAPIKey,'OutputFormat',
   'TimeTable');

   % Display the retrieved rainfall data
   display(RainFall, 'Rainfall');

   % Specify the filename for the output Excel file
   outputFileName = 'RainfallData.xlsx';

   % Write the timetable to an Excel spreadsheet
   writetimetable(RainFall, outputFileName);

   % Display a message indicating where the file has been saved
   fprintf('The rainfall data has been saved as %s. Please download                it from your ThingSpeak account.\n', outputFileName);

Hope this helps. If you have further questions or need assistance with another aspect of MATLAB or data analysis, feel free to ask!

[chris weedon]

I have found a way to do this more by accident. I discovered MATLab Drive where I can run the code and the Excel file is created in the same folder which I can then simply download.

Thanks again for your help and answers.

Chris

[Umar]

Hi @chris weedon,

Thanks for sharing this information. If all your questions have been answered, please don’t forget to click “Accept Answer” and vote for @Stephen 23 and @Jaimin for contributing their efforts to help resolve the problem. If you still have any further questions for us, please let us know.

Answer 2

[Jaimin]

Hi @chris weedon

Since I don't have API credentials, I am unable to reproduce the issue. However, I do have a workaround that you might try.

You can convert the “timetable” data to a “table” format using the “timetable2table” function. After that, you can store the table data in an Excel format using the “writetable” function..

Please refer following code snippet for better understanding.

% Convert the timetable to a table
RainFallTable = timetable2table(RainFall);

% Write the table to an Excel file
writetable(RainFallTable, 'TT.xlsx');

Refer following MathWorks documentation for more information on “timetable2table” function

https://www.mathworks.com/help/matlab/ref/timetable2table.html

Refer following MathWorks documentation for more information on “writetable” function

https://www.mathworks.com/help/matlab/ref/writetable.html

I hope this will be helpful.

[chris weedon]

Thanks for the response. This seems to convert the output timetable to another. See data extract below

The issue seems to be what happens to the excel file I can't find it even if I add an output path such as:

writetable(RainFallTable, 'C:\Users\chris\Downloads\TT.xlsx');

Where does the excel file end up??

RainfallTable =

46x2 table

Timestamps HourlyRainfall

____________________ ______________

21-Sep-2024 10:01:15 2.504

21-Sep-2024 11:00:08 4.695

21-Sep-2024 12:00:32 1.565

[Jaimin]

Modify the line from "writetable(RainFallTable,'C:\Users\chris\Downloads\TT.xlsx');" to "writetable(RainFallTable, 'TT.xlsx');". After execution, type the "pwd" command in the Command Window. Navigate to the path displayed as the output, and you will find the "TT.xlsx" file there.

[Stephen23]

"Where does the excel file end up??"

In C:\Users\chris\Downloads, if that is the location you told it to use.