Listing Messages to CSV

import Tabs from @theme/Tabs; import TabItem from @theme/TabItem; ## Overview These snippets show how you can use SignalWires Compatibility API to filter the messages in your project by a number of parameters and then insert the message data into a CSV for your own record keeping.

Full code example: Export Messages to CSV

python from datetime import datetime from signalwire.rest import Client as signalwire_client import pandas as pd client = signalwire_client(ProjectID, AuthToken, signalwire_space_url = SpaceUrl) messages = client.messages.list(date_sent_after=datetime(2021, 1, 1, 0, 0), # date_sent_before=datetime(2021, 3, 1, 0, 0), from_=+15552308945, ) d = [] # Appends all data from messages into an array for record in messages: d.append((record.from_, record.to, record.date_sent, record.status, record.sid)) print(d) # Puts message log array into dataframe with headers for easier reading. df = pd.DataFrame(d, columns=(From, To, Date, Status, MessageSID)) print(dataframe) print(\n) print(df) # Exports dataframe to csv, index=False turns off the indexing for each row df.to_csv(messages.csv, index=False, encoding=utf-8) js const { RestClient } = require(@signalwire/compatibility-api); const fs = require(fs); const client = RestClient( ProjectID, AuthToken, { signalwireSpaceUrl: YOURSPACE.signalwire.com } ); // create a file an open it, create new line array let writeStream = fs.createWriteStream(CompanyNameMessages.csv); let newLine = []; // set headers, make sure order matches order below where elements are pushed to array const header = [ Message Sid, To, From, Date Sent, Status, Direction, Price, ]; // push headers to new line array newLine.push(header); // list messages filtered by date and status and then loop through each record to push the corresponding parameters to the headers into each row one by one // remember that in Javascript, datetime objects start at 0 where 0 equals January client.messages .list({ status: delivered, dateSentAfter: new Date(Date.UTC(2021, 4, 1, 0, 0, 0)), }) .then((messages) => { messages.forEach((c) => { newLine.push( c.sid, c.to, c.from, c.dateSent, c.status, c.direction, c.price ); }); writeStream.write(newLine.join(,) + \n, () => {}); writeStream.end(); }); php YOURSPACE.signalwire.com)); // filters by whatever parameters you need, simply uncomment to enable more or comment to enable less $messages = $client->messages->read([ dateSentAfter => 2021-04-01, dateSentBefore => 2021-04-20, //Status => busy, // filter by Status //From => +1xxxxxxxxxx, // filter by From //To => +1xxxxxxxxxx, // filter by To ]); // Write Headers $fields = array(Message SID, From, To, Date Sent, Status, Direction, Price); echo .implode(,, $fields)..\n; // Open File named TodaysDate_messageReport $fp = fopen(date(Y-m-d)._messageReport.csv, w); // Insert headers fputcsv($fp, $fields); // Write rows foreach ($messages as $message) { $row = array( $message->sid, $message->from, $message->to, $message->dateSent->format(Y-m-d H:i:s), $message->status, $message->direction, $message->price, ); // Insert rows into CSV fputcsv($fp, $row); echo .implode(,, $row)..\n; } // close file fclose($fp); ruby # For the following code to work, you will need to have Active Support and the SignalWire Ruby SDK installed. require signalwire/sdk require active_support/time require csv # Replace these values with your Project ID, Auth Token, and Space URL. @client = Signalwire::REST::Client.new ProjectID, AuthToken, signalwire_space_url: YOURSPACE.signalwire.com # Choose what parameters you want to filter by - this example filters all messages sent after the specified date and specifies page size of 100. # Hour, minute, and second are left blank here. If you want to specify a time as well as date, you can fill these in. messages = @client.messages.list(page_size: 100, date_sent_after: Date.new(2021, 5, 1, 0, 0, 0)) print(messages) # Create headers headers = [MessageSID,Date Sent,Direction, From, To, Price, Status] messages.each do |record| # Create and open a CSV CSV.open(AccountMessages.csv, w+) do |csv| # Insert headers first csv << headers # For each record in messages, insert the data one by one into CSV. Make sure the order of parameters here matches the order of the headers, or the data will be mismatched. messages.each do |record| csv << [record.sid, record.date_sent, record.direction, record.from,record.to, record.price, record.status] end end end java import io.github.signalwirecommunity.SignalWireClient; import io.github.signalwirecommunity.model.message.Message; import io.github.signalwirecommunity.model.message.Messages; import io.github.signalwirecommunity.model.phone.AvailableNumbers; import io.github.signalwirecommunity.model.phone.NumberResponse; import java.io.File; import java.io.IOException; import java.io.PrintWriter; import java.nio.file.Paths; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.stream.Collectors; import java.util.stream.Stream; public class MessagesList { public static List dataLines = new ArrayList<>(); public static SignalWireClient client() { return new SignalWireClient(PROJECT-ID-FROM-SPACE, API-TOKEN-FROM-SW, SPACE-NAME); } public static void main(String[] args) { // Load messages from space List messages = getMessages(); dataLines.add(new String[]{From, To, Date, Status, MessageSID}); // Loop through messages and add to the Array for (Message message : messages) { dataLines.add(new String[]{message.from, message.to, message.date_created, message.status, message.sid}); } try { // Generate the CSV file createAndPopulateCSV(); } catch (IOException exception) { exception.printStackTrace(); } } private static List getMessages() { // Using the Java Wrapper API call the messages within the project ID List messages = client().message().getMessages().messages; return messages; } public static String convertToCsv(String[] data) { return Stream.of(data) .map(MessagesList::escapeSpecialCharacters) .collect(Collectors.joining(,)); } public static void createAndPopulateCSV() throws IOException { String filename = message_result + System.currentTimeMillis(); File file = new File(Paths.get(csv).toAbsolutePath().toUri()); if (!file.exists()) { boolean isFolder = file.mkdir(); if (isFolder){ System.out.println(New folder created!); }else{ System.out.println(Couldn\t create folder); } } File csvOutputFile = new File(Paths.get(csv).toAbsolutePath() + \\ + filename); try (PrintWriter printWriter = new PrintWriter(csvOutputFile)) { dataLines.stream() .map(MessagesList::convertToCsv) .forEach(printWriter::println); System.out.println(Write operation done successfully!); } } public static String escapeSpecialCharacters(String data) { String escapeData = data.replace(\\R, ); if (data.contains(,) || data.contains(\)) { data = data.replace(\, \\); escapeData = \ + data + \; } return escapeData; } }

Step by step walkthroughs will be provided in further detail below as the steps and methods vary with each language. The possible parameters that you can filter by are DateSent, Status, To, From, and PageSize. ## Python ### What do I need to run this code? For the following code to work, you will need to have pandas, DateTime, and the SignalWire Python SDK installed. Read about the different ways to install pandas [here](https://pandas.pydata.org/docs/getting_started/install.html). Read about datetime and how to install using pip [here](https://pypi.org/project/DateTime/). Read about the SignalWire Python SDK and how to install [here](/compatibility-api/sdks). ** How to get API Credentials ** The API also requires that you authenticate yourself using your Project ID, API Token, and Space URL. If you do not know where to find these values, check out our guide [here](/guides/navigating-your-space#api)! ### How to Run Snippet? Assuming youve named your script listMessagesToCSV.py you would need to do the following to run the script python3 listMessagesToCSV.py. ### Step by Step Code Walkthrough First, we need to import the necessary resources. In this example, that is datetime, pandas, and the SignalWire Client. We also need to instantiate the SignalWire client using the project ID, auth token, and Space URL. python from datetime import datetime from signalwire.rest import Client as signalwire_client import pandas as pd client = signalwire_client(ProjectID, AuthToken, signalwire_space_url = SpaceURL) Next, we need to choose what parameters wed like to filter by. In this example, I have included how to filter by a date range and from number. DateSent is a datetime object where the order for the arguments is Year, Month, Date, Hour, Minute, Seconds. You can leave hour, minute, and seconds at 0, unless you have a specific time of day you would like to filter by. :::info Octal Literals in Some Python Versions For months Jan - September, A slight change was made because python version (3.9) does not support leading 0s in datetime anymore. You must use the 0o prefix for octal literals now. That is reflected below in the code. If your version doesn’t include that limitation, you can switch it back to 01 or whatever month you need. ::: python messages = client.messages.list(date_sent_after=datetime(2021, 1, 1, 0, 0), # date_sent_before=datetime(2021, 3, 1, 0, 0), from_=+15552308945, ) We now need to insert the data from messages into an array. Here we set up an empty array. This will contain all of the data that we will gather from the messages. We can loop through messages and append each of the following parameters to our empty array: from_formatted, to_formatted, date_sent, status, and sid. You can expand this to include as many or as few parameters as youd like. To see all of the parameters returned in the JSON response, you can view our API documentation here: [List Messages API](/rest/compatibility-api/endpoints/list-messages) After that, we print the array so that we can see what we have inserted for debugging purposes. python d = [] # Appends all data from messages into an array for record in messages: d.append((record.from_, record.to, record.date_sent, record.status, record.sid)) print(d) Next, we create a dataframe and export it to CSV. The next section of this code is to create a dataframe using pandas with column name headers. Its important to make sure that the order of the headers matches the order of the parameters you inserted into the array above. If you choose to add more or remove parameters, make sure to double-check that the order matches or your data will be mismatched in the CSV. python df = pd.DataFrame(d, columns=(From, To, Date, Status, MessageSID)) print(dataframe) print(\n) print(df) df.to_csv(messages.csv, index=False, encoding=utf-8) We then print the dataframe for debugging purposes and export the dataframe to CSV. Using the parameter index=False turns off the indexing for each row. You can rename messages.csv to be as specific or general as youd like. ## PHP ### What do I need to run this code? For the following code to work, you will need to have the SignalWire PHP SDK installed. Read about the SignalWire PHP SDK and how to install [here](/compatibility-api/sdks). You will also need to make sure that the vendor/autoload.php path points to the correct location on your computer, as we can’t determine that for you. However, if you want to run this script exactly, install the SDK from within the folder that contains this PHP script. ** How to get API Credentials ** The API also requires that you authenticate yourself using your Project ID, API Token, and Space URL. If you do not know where to find these values, check out our guide [here](/guides/navigating-your-space#api)! ### How to Run Snippet? Assuming youve named your script listMessagesToCSV.php you would need to do the following to run the script php listMessagesToCSV.php. ### Step by Step Code Walkthrough First, we need to import the necessary resources. In this example, that just means we need to point to the correct path of the vendor autoload file on your computer. We also need to instantiate the SignalWire client using the project ID, auth token, and Space URL. php YOURSPACE.signalwire.com)); Next, we need to choose what parameters wed like to filter by. In this example, I have included how to filter by a date range, from number, and to number. php $messages = $client->messages->read([ dateSentAfter => 2021-04-01, dateSentBefore => 2021-04-20, //From => +1xxxxxxxxxx, // filter by From //To => +1xxxxxxxxxx, // filter by To ]); In the next section, we need to create the file to insert the message data into. Begin by writing headers and storing them in a variable called $fields. We also use implode to join the elements of the array with a string. We will then use fopen to create and open a file named **TodaysDate_MessageReport**. After that, we use fputcsv to insert our headers stored in $fields into our file. php $fields = array(Message SID, From, To, Date Sent, Status, Direction, Price); echo .implode(,, $fields)..\n; $fp = fopen(date(Y-m-d)._messageReport.csv, w); fputcsv($fp, $fields); Next, we need to loop through our $messages array in order to gather the necessary message data and insert it into our CSV. It is important to make sure the order of the headers is the same as the order of the values we will be gathering. As we loop through each message record, we will insert it into the CSV one by one and use implode to join the elements of the array with a string. The last step is to close the file! php foreach ($messages as $message) { $row = array( $message->sid, $message->from, $message->to, $message->dateSent->format(Y-m-d H:i:s), $message->status, $message->direction, $message->price, ); fputcsv($fp, $row); echo .implode(,, $row)..\n; } fclose($fp); ## Ruby ### What do I need to run this code? For the following code to work, you will need to have Active Support, CSV, and the SignalWire Ruby SDK installed. Read about the SignalWire Ruby SDK and how to install [here](/compatibility-api/sdks). Read about Active Support and how to install [here](https://rubygems.org/gems/activesupport/versions/5.0.0.1). Read about CSV and how to install [here](https://rubygems.org/gems/csv/versions/0.0.1). ** How to get API Credentials ** The API also requires that you authenticate yourself using your Project ID, API Token, and Space URL. If you do not know where to find these values, check out our guide [here](/guides/navigating-your-space#api)! ### How to Run Snippet? Assuming youve named your script listMessagesToCSV.rb you would need to do the following to run the script ruby listMessagesToCSV.rb. ### Step by Step Code Walkthrough First, we need to import the necessary resources. In this case, we need to import signalwire/sdk, active_support/time, and csv. We also need to instantiate the SignalWire client using the project ID, auth token, and Space URL. ruby require signalwire/sdk require active_support/time require csv @client = Signalwire::REST::Client.new ProjectID, Auth Token, signalwire_space_url: YOURSPACE.signalwire.com Next, we need to choose what parameters wed like to filter by. In this example, I have included how to filter by a date range and specify the page size of results you want to see returned. ruby messages = @client.messages.list(page_size: 100, date_sent_after: Date.new(2021, 5, 1, 0, 0, 0)) Next, we need to create headers for our CSV and insert the headers/message records into the CSV. Its important to make sure that the headers are in the exact same order as the messagerecord parameters. In the code section below, we create an array of headers. Next, we open a file **AccountMessages.csv**. We insert the headers before we begin the loop, otherwise, the headers will precede every single record. After the headers have been inserted, we loop through each of the message records in messages and insert them one by one with all the listed parameters into the CSV. ruby headers = [MessageSID,Date Sent,Direction, From, To, Price, Status] messages.each do |record| CSV.open(AccountMessages.csv, w+) do |csv| csv << headers messages.each do |record| csv << [record.sid, record.date_sent, record.direction, record.from,record.to, record.price, record.status] end end end ## Node.js ### What do I need to run this code? For the following code to work, you will need to have the SignalWire NodeJS SDK installed. Read about the SignalWire NodeJS SDK and how to install [here](/compatibility-api/sdks). First, we need to import the necessary resources. In this case, we need to import @signalwire/compatibility-api and fs. ** How to get API Credentials ** The API also requires that you authenticate yourself using your Project ID, API Token, and Space URL. If you do not know where to find these values, check out our guide [here](/guides/navigating-your-space#api)! ### How to Run Snippet? Assuming youve named your script listMessagesToCSV.js you would need to do the following to run the script node listMessagesToCSV.js. ### Step by Step Code Walkthrough We also need to instantiate the SignalWire client using the project ID, auth token, and Space URL. javascript const { RestClient } = require(@signalwire/compatibility-api); const fs = require(fs); const client = RestClient(ProjectID, Auth Token, { signalwireSpaceUrl: YOURSPACE.signalwire.com, }); Next, we need to create our CSV file, an empty array to put our records into, and an array of headers. Its important to make sure that later when we loop through the message records, we push the parameters into the CSV in the same order as the headers here. Lastly, we push the headers into the CSV as the first row. javascript let writeStream = fs.createWriteStream(CompanyNameMessages.csv); let newLine = []; const header = [Message Sid, To, From, Date Sent, Status, Direction, Price]; newLine.push(header); Next, we determine which parameters wed like to filter by and what parameters we expect to be pushed into the CSV. The filters used in this example are status and start time. When using date time objects in Javascript, its important to remember that it starts at 0 with 0 being January. Then as we loop through messages, we push each parameter into the CSV one by one. After we are finished looping through messages We use writeStream.write to separate these values by commas and add a new line at the end. Lastly, we use writeStream.end to close the file. javascript client.messages .list({ status: delivered, dateSentAfter: new Date(Date.UTC(2021, 4, 1, 0, 0, 0)), }) .then((messages) => { messages.forEach((c) => { newLine.push(c.sid, c.to, c.from, c.dateSent, c.status, c.direction, c.price); }); writeStream.write(newLine.join(,) + \n, () => {}); writeStream.end(); }); ## Java ### What do I need to run this code? For the following code to work, you will need to have a Maven project, The Java Wrapper Library and a Signalwire account. Read about how to get started with the Java Wrapper API [here](https://github.com/signalwire-community/compatibility-api-java) ** How to get API Credentials ** The API also requires that you authenticate yourself using your Project ID, API Token, and Space URL. If you do not know where to find these values, check out our guide [here](/guides/navigating-your-space#api)! ### How to Run Snippet? Assuming youve named your script MessageList.java you would need to do the following to run the javac MessageList.java then java MessageList.java. ### Step by Step Code Walkthrough We also need to instantiate the SignalWire client using the project ID, auth token, and Space URL java //Array of message to convert to CSV public static List dataLines = new ArrayList<>(); public static SignalWireClient client() { return new SignalWireClient(PROJECT-ID-FROM-SPACE, API-TOKEN-FROM-SW, SPACE-NAME); } In the main method we need to get our messages using the static client method, In the full code we have an helper method called getMessages which uses the client().message().getMessages().messages method to populate messages from your SignalWire Space. java // Load messages from space List messages = getMessages(); The getMessages() method looks like below: java private static List getMessages() { // Using the Java Wrapper API call the messages within the project ID List messages = client().message().getMessages().messages; return messages; } Then we make use of the List to populate and create a new CSV file using the following lines of code java // Add the header of the CSV file dataLines.add(new String[]{From, To, Date, Status, MessageSID}); // Populate the item of each message for (Message message : messages) { dataLines.add(new String[]{message.from, message.to, message.date_created, message.status, message.sid}); } Now all the messages are added to the Variable dataLines. In the full code we have some helper methods like convertToCsv(String[] data) java public static String convertToCsv(String[] data) { return Stream.of(data) .map(MessagesList::escapeSpecialCharacters) .collect(Collectors.joining(,)); } This takes the dataLine array and passes it to createAndPopulateCSV() which creates a CSV file and then populate the messages java public static void createAndPopulateCSV() throws IOException { // Auto-generate the name of the file String filename = message_result + System.currentTimeMillis(); // Get the absolute path to the folder you would like to create in the project directory File file = new File(Paths.get(csv).toAbsolutePath().toUri()); // if file doesnt exist. create a new folder if (!file.exists()) { boolean isFolder = file.mkdir(); if (isFolder){ System.out.println(New folder created!); }else{ System.out.println(Couldn\t create folder); } } // create new csv file with the autogenerated name File csvOutputFile = new File(Paths.get(csv).toAbsolutePath() + \\ + filename); try (PrintWriter printWriter = new PrintWriter(csvOutputFile)) { dataLines.stream() .map(MessagesList::convertToCsv) .forEach(printWriter::println); // Write item into the CSV file System.out.println(Write operation done successfully!); } } to format message in the CSV correctly and handle escape keywords, we make use of the escapeSpecialCharacters() method java public static String escapeSpecialCharacters(String data) { String escapeData = data.replace(\\R, ); if (data.contains(,) || data.contains(\)) { data = data.replace(\, \\); escapeData = \ + data + \; } return escapeData; } In the main method we call the createAndPopulateCSV() method to start the process of creating the CSV file and populating the file with the message data. java // Generate the CSV file try { createAndPopulateCSV(); } catch (IOException exception) { exception.printStackTrace(); } Full java code: java import io.github.signalwirecommunity.SignalWireClient; import io.github.signalwirecommunity.model.message.Message; import io.github.signalwirecommunity.model.message.Messages; import io.github.signalwirecommunity.model.phone.AvailableNumbers; import io.github.signalwirecommunity.model.phone.NumberResponse; import java.io.File; import java.io.IOException; import java.io.PrintWriter; import java.nio.file.Paths; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.stream.Collectors; import java.util.stream.Stream; public class MessagesList { public static List dataLines = new ArrayList<>(); public static SignalWireClient client() { return new SignalWireClient(PROJECT-ID-FROM-SPACE, API-TOKEN-FROM-SW, SPACE-NAME); } public static void main(String[] args) { // Load messages from space List messages = getMessages(); dataLines.add(new String[]{From, To, Date, Status, MessageSID}); // Loop through messages and add to the Array for (Message message : messages) { dataLines.add(new String[]{message.from, message.to, message.date_created, message.status, message.sid}); } try { // Generate the CSV file createAndPopulateCSV(); } catch (IOException exception) { exception.printStackTrace(); } } private static List getMessages() { // Using the Java Wrapper API call the messages within the project ID List messages = client().message().getMessages().messages; return messages; } public static String convertToCsv(String[] data) { return Stream.of(data) .map(MessagesList::escapeSpecialCharacters) .collect(Collectors.joining(,)); } public static void createAndPopulateCSV() throws IOException { String filename = message_result + System.currentTimeMillis(); File file = new File(Paths.get(csv).toAbsolutePath().toUri()); if (!file.exists()) { boolean isFolder = file.mkdir(); if (isFolder){ System.out.println(New folder created!); }else{ System.out.println(Couldn\t create folder); } } File csvOutputFile = new File(Paths.get(csv).toAbsolutePath() + \\ + filename); try (PrintWriter printWriter = new PrintWriter(csvOutputFile)) { dataLines.stream() .map(MessagesList::convertToCsv) .forEach(printWriter::println); System.out.println(Write operation done successfully!); } } public static String escapeSpecialCharacters(String data) { String escapeData = data.replace(\\R, ); if (data.contains(,) || data.contains(\)) { data = data.replace(\, \\); escapeData = \ + data + \; } return escapeData; } } ## Wrap up Record keeping is important, so we hope these examples help make it easier to filter your messages and export them to an external CSV with ease. All of these examples will accomplish the same goal and can be built upon to expand or further drill down the amount of data returned. Please feel free to reach out to us on our Community Slack or create a Support ticket if you need guidance! ## Sign Up Here If you would like to test this example out, [create a SignalWire account and Space](https://m.signalwire.com/signups/new?s=1). Please feel free to reach out to us on our [Community Slack](https://signalwire.community/) or create a Support ticket if you need guidance!