C# and SQLite – Exporting Data (CSV)

Sometimes it can be useful to export data from a database, so that it can be analysed, or, to import in to another computer system. CSV, or Comma Separated Value files, are one such file format that allows for both of these scenarios.

Below is an example of how C# can be used to export data to a CSV file called ‘personexport.csv’, from an SQLite database table called ‘person’, which was used in the examples for selecting, inserting, updating, deleting and importing data (CSV, text, XML and JSON).

Firstly, a check is made to see if the database file actually exists. If it does, a connection to the database is established, the path to the resulting CSV file is set, along with its name and a check is made to see if the path is valid. The data is then extracted from the database and assigned to a 'reader' object. The CSV file is then opened for writing and headers are added. A ‘while‘ loop is used to iterate through the 'reader' object and write the data to the file.

A rolling seven day backup is also included. This makes a copy of the CSV file that has just been created, giving it a name that includes the index number for the day of the week, along with the day itself, for example, 'personexport-1-monday.csv', for the backup on a Monday. Here, Sunday is classed as the first day of the week, with an index value of zero. Note that the backup is only done for the first time that this is run in a given day. Backups are then overwritten each week.

Finally, confirmation of a successful export is provided. A ‘try-catch-finally’ block is used to catch any errors that may occur, as well as tidy up at the end, regardless of whether the export is successful or not.

// Database.
string database = @"C:\Demo\DemoDB.db";
string password = @"DemoPW";
SqliteConnection connect;

// Check if the database exists.
if (!File.Exists(database))
{

    // Message confirming incorrect database location.
    Console.WriteLine("Error locating database.");

    // Stop program execution.
    System.Environment.Exit(1);

}

// Database connection.
connect = new SqliteConnection("Data Source=" + database + ";" +
                                "Password=" + password);

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Message confirming unsuccessful database connection.
    Console.WriteLine("Database connection unsuccessful.");

    // Stop program execution.
    System.Environment.Exit(1);

}

// Export path and file.
string exportPath = @"C:\demo\";
string exportCsv = "personexport.csv";

// Stream writer for CSV file.
StreamWriter csvFile = null;

// Check to see if the file path exists.
if (!Directory.Exists(exportPath))
{

    // Display a message stating file path does not exist.
    Console.WriteLine("File path does not exist.");

    // Stop program execution.
    System.Environment.Exit(1);

}

try
{

    // Query text incorporated into SQL command.
    var sqlSelect = connect.CreateCommand();
    sqlSelect.CommandText = @"
        SELECT id, firstname, lastname, title, dob 
        FROM person 
        ORDER BY id
    ";

    // Execute SQL and place data in a reader object.
    var reader = sqlSelect.ExecuteReader();

    // Stream writer for CSV file.
    csvFile = new StreamWriter(@exportPath + exportCsv);

    // Add the headers to the CSV file.
    csvFile.WriteLine(String.Format("\"{0}\",\"{1}\",\"{2}\"," +
        "\"{3}\",\"{4}\"",
        reader.GetName(0), reader.GetName(1), reader.GetName(2),
        reader.GetName(3), reader.GetName(4)));

    // Construct CSV file data rows.
    while (reader.Read())
    {

        // Add line from reader object to new CSV file.
        csvFile.WriteLine(String.Format("\"{0}\",\"{1}\",\"{2}\"," +
            "\"{3}\",\"{4}\"",
            reader[0], reader[1], reader[2], reader[3], reader[4]));

    }

    // Close the file.
    csvFile.Close();

    // Today's date.
    DateTime today = DateTime.Now;

    // Construct the backup file name.
    string exportBackupCsv = exportCsv.Substring(0, exportCsv.Length - 4) +
        "-" + (int)today.DayOfWeek + "-" +
        today.DayOfWeek.ToString().ToLower() + ".csv";

    // Check if the backup file does not exist, or if it does, check that
    // today's date is different from the last modified date.
    if (!File.Exists(Path.Combine(exportPath, exportBackupCsv)) ||
        (File.Exists(Path.Combine(exportPath, exportBackupCsv)) &&
        File.GetLastWriteTime(
            Path.Combine(exportPath, exportBackupCsv)).Date !=
            today.Date))
    {

        // Copy the CSV export.
        File.Copy(Path.Combine(exportPath, exportCsv),
            Path.Combine(exportPath, exportBackupCsv), true);

    }

    // Message stating export successful.
    Console.WriteLine("Data export successful.");

}
catch (Exception e)
{

    // Message stating export unsuccessful.
    Console.WriteLine("Data export unsuccessful.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection and CSV file.
    connect.Close();
    csvFile.Close();

}

The CSV file produced contains the following data.

"id","firstname","lastname","title","dob"
"1","Bob","Smith","Mr","1980-01-20"
"3","Fred","Bloggs","Mr","1975-05-07"
"4","Alan","White","Mr","1989-03-20"
"5","Fiona","Bloggs","Mrs","1985-05-19"
"6","Zoe","Davis","Miss","1979-07-11"
"7","Tom","Ingram","Mr","1971-10-04"
"8","Karen","Thomas","Mrs","1969-03-08"
"9","Samantha","Yates","Miss","1995-08-27"

C# and SQLite – Exporting Data (CSV)

Further Resources