SQL To Text - C# Programming Exercise

// Importing necessary namespaces
using System;
using System.Collections.Generic;
using System.IO;
using System.Text.RegularExpressions;

class SqlToText
{
    // Main method where the program execution begins
    static void Main()
    {
        // Path to the input SQL file
        string inputFilePath = "input.sql";

        // Path to the output text file where the result will be saved
        string outputFilePath = "output.txt";

        // Call the ParseSqlInsert method to process the SQL file and extract the data
        ParseSqlInsert(inputFilePath, outputFilePath);
    }

    // Method to parse the SQL INSERT statements and extract the data into a text format
    static void ParseSqlInsert(string inputFilePath, string outputFilePath)
    {
        try
        {
            // Read the entire SQL file
            string sqlContent = File.ReadAllText(inputFilePath);

            // Regex pattern to match INSERT INTO SQL statements
            string insertPattern = @"INSERT INTO\s+`?(\w+)`?\s?\((.*?)\)\s+VALUES\s?\((.*?)\);";

            // Match all the INSERT statements in the SQL content
            var matches = Regex.Matches(sqlContent, insertPattern, RegexOptions.IgnoreCase);

            // List to store the formatted output
            List outputLines = new List();

            foreach (Match match in matches)
            {
                // Get the table name
                string tableName = match.Groups[1].Value;

                // Get the field names as a comma-separated list
                string fieldNames = match.Groups[2].Value;

                // Get the values to be inserted
                string values = match.Groups[3].Value;

                // Split the field names and values by commas
                string[] fields = fieldNames.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                string[] valuesArray = values.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

                // Clean up extra spaces or quotes around field names and values
                for (int i = 0; i < fields.Length; i++)
                {
                    fields[i] = fields[i].Trim().Trim('`').Trim();
                    valuesArray[i] = valuesArray[i].Trim().Trim('\'', '"').Trim();
                }

                // Add the table name to the output
                outputLines.Add($"Table: {tableName}\n");

                // Add each field and its corresponding value to the output
                for (int i = 0; i < fields.Length; i++)
                {
                    outputLines.Add($"{fields[i]}: {valuesArray[i]}");
                }

                // Add a blank line after each record
                outputLines.Add("");
            }

            // Write the result to the output file
            File.WriteAllLines(outputFilePath, outputLines);

            Console.WriteLine("SQL has been parsed and saved to the output file.");
        }
        catch (Exception ex)
        {
            // Catch any errors (e.g., file not found or read/write issues) and display an error message
            Console.WriteLine("Error: " + ex.Message);
        }
    }
}