Door 19 CSV and TSV

Welcome to door 19. In previous doors, we have already seen some alternatives to pure text output. Today, I would like to show you another one, which is output as CSV.

A CSV (Comma-Separated Values) is a simple, text-based file format for storing tabular data. Each row corresponds to a data record, and the individual values are separated by a delimiter (usually a comma or semicolon). It is easily readable by both - humans and machines. We now want to output our data from the database as CSV.

The FM.SQL.CSV function is available for this purpose. In the parameters, we first define our SQL query. That's all we need for the function, but like many functions, we can also optionally specify a range in the SQL result we want to get back, allowing us to specify the first and last rows. In the same way, we can now limit the columns by specifying the first and last columns. If we want to deviate from the line break as the line separator and the semicolon as the column separator, we can also define this in the parameters. Last but not least, we can specify a flag that determines whether our values should all be returned as text. If we want this, we write 1 in the flags.

MBS( "FM.SQL.CSV"; SQLref { ; FirstRow; LastRow; firstCol; lastCol; 
   rowSeparator; colSeparator; Flags } )  

Let's try it out on our database. We would like to output the movies released between 2000 and 2010 as CSV. First, we develop the query for this and then insert it into the function just described. We would like to keep all specified rows and columns, use the standard separators, and not set the flag. Therefore, we can ignore the optional parameters in this example.

Set Variable [ $SQLRes ; Value: MBS("FM.SQL.Execute" ; Get(FileName);
   "SELECT Name,Launch,Director 
   FROM Movie 
   WHERE Launch>=2000 AND Launch<=2010 ORDER BY Launch" ) ]
Set Variable [ $r ; Value: MBS("FM.SQL.CSV"; $SQLRes) ]
Show Custom Dialog [ $r ]
Set Variable [ $r ; Value: MBS("FM.SQL.ReleaseAll") ]

But we can not only generate CSV text from our FM database, we can also add new records with CSV text. To do this, we use the FM.InsertRecordCSV function. Here, we first indicate in the parameters the file in which we want to insert the data, as well as the table name. Then we list the field names we want to fill with data, followed by the CSV text. It is important to ensure that the CSV text has exactly as many columns as the fields we specify, otherwise an error will occur. If there is a field in the CSV that should remain empty, the separators must still be set, but the space between the separators remains empty. Optionally, we can now specify the separator and one or more fields that do not appear in the CSV but are to be filled in the database and remain the same for all inserted records. Let's look at a concrete example of how this function is used.

Here we have four movies that we want to add to our Movie table:

Richie Rich's Christmas Wish;John Murlowski;1998;
A Dennis the Menace Christmas;Ron Oliver; 2007;
Beethoven’s Christmas Adventure;John Putch;2011;
The Santa Clause 3: The Escape Clause;Michael Lembeck; 2006;110800000

We see that the first three films do not specify a generated sale, so we leave the information blank.

Now we can insert our values into the function:

# CSV Import
Set Variable [ $Import ; Value: MBS("FM.InsertRecordCSV"; Get(FileName); "Movie"; 
   "Name¶Director¶Launch¶Generated_Sales"; 
   "Richie Rich's Christmas Wish;John Murlowski;1998;¶
   A Dennis the Menace Christmas;Ron Oliver; 2007;¶
   Beethoven’s Christmas Adventure;John Putch;2011;¶
   The Santa Clause 3: The Escape Clause;Michael Lembeck; 2006;110800000";
   ";"; "Genre"; "Christmas comedy") ]
Show Custom Dialog [ $import ]

First, we see that the file name is specified again with Get(FileName), just like our table name Movie. This is followed by the list of fields that we want to fill in the database. Here, we select Name, Director, Launch, and Generated_Sales. Now comes the CSV-formatted text we saw earlier. Please pay attention to the line breaks here, otherwise it will be seen as a single line and an error will occur. We use the standard semicolon as the separator and then we want to specify a field that should be set for all CSV-imported records in the database. We set the Genre field to Christmas comedy for each data record. We also see that a dialog box is displayed after the call, showing us the result of the function. This result is the number of imported data records and should return a 4 if everything works. This is what the new entries in the database look like:

Now there is one last feature I would like to introduce to you today, because we can import data not only as CSV, but also as TSV. TSV (Tab-Separated Values) separates the values with a tab character. Because tabs hardly ever occur in normal text content, TSV is often more robust and easier to process in practice. The data is easy for humans to read. However, TSV is not as well known as CSV.

The biggest difference between CSV and TSV is therefore the separator.

So we also want to import our data as TSV. The data then looks like this:

Richie Rich's Christmas Wish	John Murlowski	1998
A Dennis the Menace Christmas	Ron Oliver	 2007
Beethoven’s Christmas Adventure	John Putch	2011
The Santa Clause 3: The Escape Clause	Michael Lembeck	 2006	110800000

The separator is a tab. We already mentioned that we can have empty fields in CSV by simply omitting the value between the separators. The same applies to TSV. However, one difficulty is that you cannot see the last tab, because in the entries where the generated sale is missing, there is still a tab after the year. The function has a similar parameter assignment to the previous function. Here, too, the file name and table name are specified first. Then come the fields as a list and the TSV-formatted text. We cannot specify a different separator here. So we stay with the tab. Again, if necessary, we can specify one or more fields with the corresponding value that should apply to all imported TSV records. This function also returns the number of imported records.

# TSV Import 
Set Variable [ $importTSV ; Value: MBS( "FM.InsertRecordTSV"; Get(FileName); "Movie"; 
   "Name¶Director¶Launch¶Generated_Sales"; 
   "Richie Rich's Christmas Wish	John Murlowski	1998	 ¶
   A Dennis the Menace Christmas	Ron Oliver	 2007	 ¶
   Beethoven’s Christmas Adventure	John Putch	2011	 ¶
   The Santa Clause 3: The Escape Clause	Michael Lembeck	 2006	110800000" ; "Genre"; "Christmas comedy" ]
 
Show Custom Dialog [ "TSV Import" ; $importTSV ]

That's all for today. I hope you enjoyed it, and let's meet again tomorrow.