This script whilst relatively basic in functionality does expand on the Database Query script by exporting the Track Played History into either a text or CSV (Comma Separated Values) file.
The script is available to download at the bottom of this article.
{************************************************************
This Script will export the History to a text or CSV file
THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE
THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING
************************************************************}
PAL.LockExecution; // Speed up the Script processing
// Declare Constants
Const Option1 : string = 'Select 1 to Export to a Text File';
Const Option2 : string = 'Select 2 to Export to a CSV File';
Const TxtExt : string = '.txt';
Const CsvExt : string = '.csv';
Const NoHist : string = 'No History Found';
// Declare Variables
var Results : TDataSet;
var TrackName : string;
var TrkCount : integer = 0;
var TempList : TStringList;
var HeadLine : string;
var HistLine : string;
var Option : integer = 0;
var FilePath : string;
var FileName : string;
var NowStr : string;
// MAIN -------------------------------------------------------------------------------------------
NowStr := DateTimeToStr(Now);
TempList := TStringList.Create;
FilePath := GetCurrentDir+'\Output\';
CreateDir(Filepath);
FileName := StringReplace(NowStr,'/','-', True);
FileName := StringReplace(FileName,' ','--', True);
FileName := StringReplace(FileName,':','-', True);
Option := StrToInt(InputBox('Options',Option1+#13#10+Option2,''));
// Export History to a TXT file (Option 1) --------------------------------------------------------
If Option = 1 then
begin
Filepath := Filepath+FileName+TxtExt;
TempList.Add('History Export for: '+DateTimeToStr(Now));
TempList.Add(' ');
HeadLine := 'Serial ID SongID File Name Date Played Duration Artist Title Album Album Year';
TempList.Add(HeadLine);
end;
// Export History to a CSV file (Option 2) --------------------------------------------------------
If Option = 2 then
begin
Filepath := Filepath+FileName+CsvExt;
TempList.Add(',,,History Export for: '+DateTimeToStr(Now));
TempList.Add(' ');
HeadLine := 'Serial,ID,SongID,File Name,Date Played,Duration,Artist,Title,Album,Album Year';
TempList.Add(HeadLine);
end;
// Query the database tables
Results := Query('SELECT * FROM historylist WHERE ID > 0 order by date_played DESC',[],True);
while not Results.EOF do
begin
TrkCount := TrkCount + 1;
HistLine := IntToStr(TrkCount)+','+FloatToStr(Results['ID'])+','+FloatToStr(Results['songID'])+','+Results['filename']+','+DateTimeToStr(Results['date_played'])+','+FloatToStr(Results['duration'])+','+Results['artist']+','+Results['title']+','+Results['album']+','+Results['albumyear'];
If Option = 1 then HistLine := StringReplace(HistLine,',',' ', True);
TempList.Add(HistLine);
Results.Next;
end;
// Check for results
if TrkCount = 0 then
begin
TempList.Clear;
TempList.Add(NoHist);
end;
TempList.SaveToFile(FilePath);
WriteLn('Output File: '+FilePath);
WriteLn(' ');
WriteLn('Number of Entries Exported: '+IntToSTr(TrkCount));
// End of Main ------------------------------------------------------------------------------------
PAL.UnLockExecution; // Return PAL Script processing to normal speed
// Housekeeping
Results.Free;
So let's break this script down in to the various sections to make it simpler to understand:
The Opening Remarks
It is always a good idea to add some comments to a script to give an indication of the script's purpose. You could also consider including dates and code revision dates:
{************************************************************
This Script will export the History to a text or CSV file
THIS SCRIPT SHOULD ONLY BE RUN WHEN SAM BROADCASTER IS IDLE
THIS WILL BY DESIGN CAUSE SAM BROADCASTER TO STOP IF RUNNING
************************************************************}
Again, the comments are very simple and they also include a warning not to run this script unless SAM Broadcaster is idle. This is because of the user interaction which will cause SAM Broadcaster to stop and wait for the user.
Script Speed
PAL.LockExecution; // Speed up the Script processing
Because SAM Broadcaster is not streaming and because querying the database can provide a lot of search results, we want the script running as quickly as possible, as soon as possible so we add this line as the first line of code after the opening comments.
Constant Declarations
// Declare Constants
Const Option1 : string = 'Select 1 to Export to a Text File';
Const Option2 : string = 'Select 2 to Export to a CSV File';
Const TxtExt : string = '.txt';
Const CsvExt : string = '.csv';
Const NoHist : string = 'No History Found';
We are using Constant Declarations for the prompts, the file extensions and a message to indicate that the script ran successfully but there were no results, i.e. the History list is empty. Although we do not specifically state the script ran successfully, the code using the Constant NoHist is towards the end of the script and would therefore indicate the script has run. If the script had failed to run then this line would not be present in the file created.
Variable Declarations
It is usual to declare variables at the start of any script or program. They are easier to find which can simplify troubleshooting.
// Declare Variables
var Results : TDataSet;
var TrackName : string;
var TrkCount : integer = 0;
var TempList : TStringList;
var HeadLine : string;
var HistLine : string;
var Option : integer = 0;
var FilePath : string;
var FileName : string;
var NowStr : string;
Here we can see a small number of variables of different types are declared. The TDataSet variable to hold the data from the database query, the TStringList is used to hold the list of strings generated from the data from the database results, and we also explicitly declare the starting number for two integer variables, TrkCount and Option. It is a good idea to use variable names which make it simpler to understand their purpose, e.g. anyone reading this script could be fairly certain that the string variable FilePath would be used to hold the directory path to the files created.
Program Main
The start of the program.
// MAIN -------------------------------------------------------------------------------------------
NowStr := DateTimeToStr(Now);
TempList := TStringList.Create;
FilePath := GetCurrentDir+'\Output\';
CreateDir(Filepath);
FileName := StringReplace(NowStr,'/','-', True);
FileName := StringReplace(FileName,' ','--', True);
FileName := StringReplace(FileName,':','-', True);
Option := StrToInt(InputBox('Options',Option1+#13#10+Option2,''));
The comment line gives a good indication that this is a separation from the previous part of the script. This makes troubleshooting simpler.
We plan on using the current date and time the script is run as the name of the file so we assign the current date and time (Now) into a string variable instead of a Date and Time variable. This makes it simpler to work with as a file name. We create the TStringList variable TempList to hold the strings we create by concatenating the database query results.
We will output the resulting files in to a folder we will create within the current directory which is obtained from the GetCurrentDIR command. To this we add the backslashes '\' to indicate a sub-folder entitled Output. The trailing backslash is included here so we know we only need to add the filename to complete the folder path.
This folder path is then created with the CreateDIR command. If the folder path already exists then no error is created and the existing folder is not over-written.
We know that the format of the results for the Now date and time includes a number of characters which cannot be included in a folder path or filename so we use the StringReplace command to replace these characters with characters we can include in a filename and folder path. These can be changed if necessary.
The last line creates a pop-up box prompting the user to enter either 1 or 2 based on the required format of the output file. Again the #13#10 inserts a carriage return and new line for a better layout.
Option 1 - Output to a Text File Format
There are only minor differences between the two different file formats with most of the code being a duplicate of the code for Option 2.
// Export History to a TXT file (Option 1) --------------------------------------------------------
If Option = 1 then
begin
Filepath := Filepath+FileName+TxtExt;
TempList.Add('History Export for: '+DateTimeToStr(Now));
TempList.Add(' ');
HeadLine := 'Serial ID SongID File Name Date Played Duration Artist Title Album Album Year';
TempList.Add(HeadLine);
end;
We start by including the comment line to make it obvious that this is a different section of the script and we check whether the user has entered a 1 which would indicate a text file is the desired output format.
If Option 1 was selected then we finalize the filepath and filename by adding the value of the Constant TxtExt to the filename. A number of strings are added to the list of strings which will be written to the file. These include the date and time the script was run as well as a list of headings to indicate the values shown.
Option 2 - Output to a CSV File Format
This type of file format is useful for data manipulation as when imported into a spreadsheet, each entry is assigned to a different cell and is easily tabulated.
As mentioned, the code is very similar to Option 1.
// Export History to a CSV file (Option 2) --------------------------------------------------------
If Option = 2 then
begin
Filepath := Filepath+FileName+CsvExt;
TempList.Add(',,,History Export for: '+DateTimeToStr(Now));
TempList.Add(' ');
HeadLine := 'Serial,ID,SongID,File Name,Date Played,Duration,Artist,Title,Album,Album Year';
TempList.Add(HeadLine);
end;
The only differences are the addition of the .csv file extension stored in the Constant CsvExt and the addition of commas to separate the values and to give a better layout to the data in a spreadsheet.
Querying the Database
As both options query the same database table and output the same data we can use the same code for both however, the commas used in the CSV output can be harder to look at in a text file and as such, we use the StringReplace command to replace any commas with a single space if the user selected option 1.
// Query the database tables
Results := Query('SELECT * FROM historylist WHERE ID > 0 order by date_played DESC',[],True);
while not Results.EOF do
begin
TrkCount := TrkCount + 1;
HistLine := IntToStr(TrkCount)+','+FloatToStr(Results['ID'])+','+FloatToStr(Results['songID'])+','+Results['filename']+','+DateTimeToStr(Results['date_played'])+','+FloatToStr(Results['duration'])+','+Results['artist']+','+Results['title']+','+Results['album']+','+Results['albumyear'];
If Option = 1 then HistLine := StringReplace(HistLine,',',' ', True);
TempList.Add(HistLine);
Results.Next;
end;
Whilst we have not reached the end of the data in the database table, create the Track Played History entry from the required options available, separated by commas but if the user has selected the text file format, replace the commas with single spaces.
Add these strings to the string list, increment the Track Count by 1 each time and move to the next record.
Checking for Valid Results
As you saw, we set the variable TrkCount to 0 when we declared the variable at the start of the script. If the Track History list is empty then the TrkCount will remain at 0 and not increment. This is one way we can check for any results.
// Check for results
if TrkCount = 0 then
begin
TempList.Clear;
TempList.Add(NoHist);
end;
If TrkCount is 0 then no records have been found and we can remove the headings we previously added to the list of strings with TempList.Clear which we then replace with the message contained within the Constant NoHist. This will display the line 'No History Found' in either file.
Results
This is where we output the results.
TempList.SaveToFile(FilePath);
WriteLn('Output File: '+FilePath);
WriteLn(' ');
WriteLn('Number of Entries Exported: '+IntToSTr(TrkCount));
// End of Main ------------------------------------------------------------------------------------
Firstly we write the contents of the string list to the output file. This will either be the Track Played History or the message 'No History Found'.
We also display the output directory and filename to the PAL Script IDE Output window along with the number of records found.
Finally
We return the PAL Script execution speed to normal and carry out some housekeeping by freeing up the memory used to store the database query results.
PAL.UnLockExecution; // Return PAL Script processing to normal speed
// Housekeeping
Results.Free;
Comments