Export Timetable data to Excel

Question

chris weedon 2024-9-27，7:59

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2155825-export-timetable-data-to-excel

评论： Umar 2024-9-28，2:28

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')

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

Stephen23 2024-9-27，11:01

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

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Umar 2024-9-27，11:40

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2155825-export-timetable-data-to-excel#answer_1523700

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.

4 个评论
显示 2更早的评论隐藏 2更早的评论

Umar 2024-9-27，15:02

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 2024-9-27，16:13

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 2024-9-28，2:28

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 2024-9-27，8:47

1
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/2155825-export-timetable-data-to-excel#answer_1523550

在 MATLAB Online 中打开

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.

3 个评论
显示 1更早的评论隐藏 1更早的评论

Jaimin 2024-9-27，10:42

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.