none
How to convert .db files to .csv using C# windForms? RRS feed

  • Question

  • I am working on an application that download .db from a remote server to a local server. These files are in .db format, and they need to be converted to .csv once they are downloaded to the local server.

    As shown in the code below, I am reading from a json file and deserializing it, and then initialize a timer that repeat every 5 seconds. Once the button is clicked the machine IP's address is pinged and the feedback time is recorded on a text file, at the same time, the file will be transferred to the PC using SFTP. (code is successfully working)

    private void button1_Click(object sender, EventArgs e)
            {
                if (MessageBox.Show("Are you sure you want to Start", "STARTED", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    //Read JSON file from the directory
                    filePath = @"C:\temp\JSON\app-db.json";
                    string text = File.ReadAllText(filePath);
                    var currentList = JsonConvert.DeserializeObject<List<Datalogger>>(text);
                    //Set the timer to 5 sec
                    System.Windows.Forms.Timer Clock = new System.Windows.Forms.Timer
                    {
                        Interval = 5000 //5 seconds
                    };
                    Clock.Start();
    
                    Clock.Tick += new EventHandler(timer1_Tick);
                }
                else
                {
                    this.Activate();
                }
                
            }
            private void timer1_Tick(object sender, EventArgs e)
            {
                filePath = @"C:\temp\JSON\app-db.json";
                string text = File.ReadAllText(filePath);
                var currentList = JsonConvert.DeserializeObject<List<Datalogger>>(text);
                foreach (var item in currentList)
                {
                    List<String> hosts = new List<String>();
                    for (Int32 i = 0; i < 10; ++i) hosts.Add(item.IPaddress);
                    //Write IP time feedback to a txt file 
                    var average = hosts.AsParallel().WithDegreeOfParallelism(64).
                                  Select(h => new Ping().Send(h).RoundtripTime).Average();
                    //item.IPaddress = DateTime.Now.ToLongDateString() + "    " + DateTime.Now.ToLongTimeString();
                    Console.WriteLine("IP:" + item.IPaddress + "," + "time=" + average + "ms");
                    m_streamWriter.WriteLine("{0} {1} {2}",
                       DateTime.Now.ToLongTimeString(),
                       DateTime.Now.ToLongDateString(),
                       "IP:" + item.IPaddress + "," + "time=" + average + "ms");
                    m_streamWriter.Flush();
                    //Read JSON string values
                    string host = item.IPaddress;
                    string username = item.username;
                    string password = item.password;
                    string remoteDirectory = item.sourcefolder;
                    string localDirectory = item.destfolder;
                    string filextension = item.filextension;
                    string removedownloaded = item.removedownloaded.ToString();
                    //Connect to the SFTP server to downdload data
                    using (SftpClient sftp = new SftpClient(host, username, password))
                    {
                        try
                        {
                            sftp.Connect();
    
                            var files = sftp.ListDirectory(remoteDirectory);
    
                            foreach (var file in files)
                            {
                                try
                                {
                                    string remoteFileName = file.Name;
    
                                    if ((file.Name.EndsWith(filextension)) || (file.Name.EndsWith(filextension.ToLower())) || (file.Name.EndsWith(filextension.ToUpper())))
                                    {
                                        using (Stream file1 = File.OpenWrite(Path.Combine(localDirectory, remoteFileName)))
                                        {
    
                                            string path = remoteDirectory + "/" + remoteFileName;
                                            sftp.DownloadFile(path, file1);
                                            
    
                                            if (removedownloaded == "1")
                                            {
                                                sftp.Delete(path);
                                            }
                                            else
                                            {
                                                sftp.DownloadFile(path, file1);
                                            }
    
                                        }
                                    }
    
                                }
                                catch (Exception er1)
                                {
                                    //MessageBox.Show("An exception has been caught " + er1.ToString());
                                }
    
                            }
                        }
                        catch (Exception entry)
                        {
                            MessageBox.Show(entry.Message);
                        }
                        
                    }
    
                }
                
            }

    and this is the JSON file.

    [
      {
        "Record": 1,
        "IPaddress": "192.168.6.247",
        "Machinename": "taurus",
        "username": "root",
        "password": "root",
        "sourcefolder": "/home/root/conf",
        "destfolder": "C:/temp/Data1",
        "filextension": ".db",
        "removedownloaded": 0,
        "removecsv": 1,
        "removedb": 1
      }
    ]

    I am trying to convert to upcoming file to .csv.

    Any suggestions and thoughts on how to convert them? 

    I appreciate any help!




    • Edited by samiarja Monday, August 26, 2019 5:20 AM
    Monday, August 26, 2019 3:36 AM

Answers

  • Hi samiarja,

    Thank you for posting here.

    You could try the following code to convert db file to csv file.

    private void Button1_Click(object sender, EventArgs e)
            {
    
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.db");
                m_dbConnection.Open();
                SQLiteDataAdapter adapter=new SQLiteDataAdapter("Select * From highscores", m_dbConnection);
                DataSet set = new DataSet();
                adapter.Fill(set);
                DataTable table = set.Tables[0];
                string filepath = "D:\\";
                SaveCsv(table, filepath);
                MessageBox.Show("success");
            }
    
            public  void SaveCsv(DataTable dt, string filePath)
            {
                FileStream fs = null;
                StreamWriter sw = null;
                try
                {
                    fs = new FileStream(filePath + dt.TableName + ".csv", FileMode.Create, FileAccess.Write);
                    sw = new StreamWriter(fs, Encoding.Default);
                    var data = string.Empty;
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        data += dt.Columns[i].ColumnName;
                        if (i < dt.Columns.Count - 1)
                        {
                            data += ",";
                        }
                    }
                    sw.WriteLine(data);
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        data = string.Empty;
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            data += dt.Rows[i][j].ToString();
                            if (j < dt.Columns.Count - 1)
                            {
                                data += ",";
                            }
                        }
                        sw.WriteLine(data);
                    }
                }
                catch (IOException ex)
                {
                    throw new IOException(ex.Message, ex);
                }
                finally
                {
                    if (sw != null) sw.Close();
                    if (fs != null) fs.Close();
                }
            }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by samiarja Tuesday, August 27, 2019 12:50 AM
    Monday, August 26, 2019 6:14 AM
    Moderator

All replies

  • Hi samiarja,

    Thank you for posting here.

    You could try the following code to convert db file to csv file.

    private void Button1_Click(object sender, EventArgs e)
            {
    
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.db");
                m_dbConnection.Open();
                SQLiteDataAdapter adapter=new SQLiteDataAdapter("Select * From highscores", m_dbConnection);
                DataSet set = new DataSet();
                adapter.Fill(set);
                DataTable table = set.Tables[0];
                string filepath = "D:\\";
                SaveCsv(table, filepath);
                MessageBox.Show("success");
            }
    
            public  void SaveCsv(DataTable dt, string filePath)
            {
                FileStream fs = null;
                StreamWriter sw = null;
                try
                {
                    fs = new FileStream(filePath + dt.TableName + ".csv", FileMode.Create, FileAccess.Write);
                    sw = new StreamWriter(fs, Encoding.Default);
                    var data = string.Empty;
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        data += dt.Columns[i].ColumnName;
                        if (i < dt.Columns.Count - 1)
                        {
                            data += ",";
                        }
                    }
                    sw.WriteLine(data);
                    for (var i = 0; i < dt.Rows.Count; i++)
                    {
                        data = string.Empty;
                        for (var j = 0; j < dt.Columns.Count; j++)
                        {
                            data += dt.Rows[i][j].ToString();
                            if (j < dt.Columns.Count - 1)
                            {
                                data += ",";
                            }
                        }
                        sw.WriteLine(data);
                    }
                }
                catch (IOException ex)
                {
                    throw new IOException(ex.Message, ex);
                }
                finally
                {
                    if (sw != null) sw.Close();
                    if (fs != null) fs.Close();
                }
            }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by samiarja Tuesday, August 27, 2019 12:50 AM
    Monday, August 26, 2019 6:14 AM
    Moderator
  • Hi Jack,

    Thanks for your solution, but I am getting this error.

    An unhandled exception of type 'System.Data.SQLite.SQLiteException' occurred in System.Data.SQLite.dll
    
    Additional information: SQL logic error
    
    no such table: highscores

    I only changed FilePath to where .db are located.

    Monday, August 26, 2019 10:31 PM
  • This the .db file structure. Do I need to consider anything from this file

    PRAGMA foreign_keys = 0; CREATE TABLE sqlitestudio_temp_table AS SELECT * FROM DADLoggerTable; DROP TABLE DADLoggerTable; CREATE TABLE DADLoggerTable ( Accelerometer_X_Axis_Data REAL, Accelerometer_Y_Axis_Data REAL, Accelerometer_Z_Axis_Data REAL, Gyroscope_X_Axis_Data REAL, Gyroscope_Y_Axis_Data REAL, Gyroscope_Z_Axis_Data REAL, Temperature_Data REAL, Accelerometer_Magnitude REAL, UTC_Time INTEGER, Local_Time TEXT, Local_Time_Seconds INTEGER, Methane_Value INTEGER, CAN_ID_0X300_Byte_2 INTEGER, CAN_ID_0X300_Byte_3 INTEGER, CAN_ID_0X300_Byte_4 INTEGER, Sensor_Status_Byte INTEGER, Status_Byte INTEGER, Sensor_State INTEGER, System_Status INTEGER, Span_Value INTEGER, Warning_Level INTEGER, Shutdown_Level INTEGER, Serial_Number INTEGER, Calibration_Date_DD INTEGER, Calibration_Date_MM INTEGER, Calibration_Date_YY INTEGER, Charger_State INTEGER, Charger_Alternator_Volts INTEGER, Charger_Battery_Volts INTEGER, Charger_Software_Type INTEGER, Charger_Compatibility_Level INTEGER, Charger_Status INTEGER, Charger_Temperature_Scrubber_1 INTEGER, Charger_Temperature_Scrubber_2 INTEGER, Charger_Temperature_Scrubber_3 INTEGER, Charger_Switch_Status INTEGER, Charger_Temperature_Supercharger INTEGER, Charger_Oil_Pressure INTEGER, Charger_Input_Status INTEGER, Battery_State INTEGER, Battery_Digital_Input_Byte INTEGER, Battery_Temperature INTEGER, Battery_Output_Voltage INTEGER, Battery_Input_Voltage INTEGER, Battery_IS_Current INTEGER, Battery_Digital_OutputSignal_Byte INTEGER, Battery_Alternator_Voltage INTEGER, Display_Fixed_1 INTEGER, Display_Fixed_2 INTEGER, Display_Software_Type INTEGER, Display_Span_Value INTEGER, Display_Status_Byte_1 INTEGER, Display_Status_Byte_2 INTEGER, Display_Compatibility_Level INTEGER ); INSERT INTO DADLoggerTable ( Accelerometer_X_Axis_Data, Accelerometer_Y_Axis_Data, Accelerometer_Z_Axis_Data, Gyroscope_X_Axis_Data, Gyroscope_Y_Axis_Data, Gyroscope_Z_Axis_Data, Temperature_Data, Accelerometer_Magnitude, UTC_Time, Local_Time, Local_Time_Seconds, Methane_Value, CAN_ID_0X300_Byte_2, CAN_ID_0X300_Byte_3, CAN_ID_0X300_Byte_4, Sensor_Status_Byte, Status_Byte, Sensor_State, System_Status, Span_Value, Warning_Level, Shutdown_Level, Serial_Number, Calibration_Date_DD, Calibration_Date_MM, Calibration_Date_YY, Charger_State, Charger_Alternator_Volts, Charger_Battery_Volts, Charger_Software_Type, Charger_Compatibility_Level, Charger_Status, Charger_Temperature_Scrubber_1, Charger_Temperature_Scrubber_2, Charger_Temperature_Scrubber_3, Charger_Switch_Status, Charger_Temperature_Supercharger, Charger_Oil_Pressure, Charger_Input_Status, Battery_State, Battery_Digital_Input_Byte, Battery_Temperature, Battery_Output_Voltage, Battery_Input_Voltage, Battery_IS_Current, Battery_Digital_OutputSignal_Byte, Battery_Alternator_Voltage, Display_Fixed_1, Display_Fixed_2, Display_Software_Type, Display_Span_Value, Display_Status_Byte_1, Display_Status_Byte_2, Display_Compatibility_Level ) SELECT Accelerometer_X_Axis_Data, Accelerometer_Y_Axis_Data, Accelerometer_Z_Axis_Data, Gyroscope_X_Axis_Data, Gyroscope_Y_Axis_Data, Gyroscope_Z_Axis_Data, Temperature_Data, Accelerometer_Magnitude, UTC_Time, Local_Time, Local_Time_Seconds, Methane_Value, CAN_ID_0X300_Byte_2, CAN_ID_0X300_Byte_3, CAN_ID_0X300_Byte_4, Sensor_Status_Byte, Status_Byte, Sensor_State, System_Status, Span_Value, Warning_Level, Shutdown_Level, Serial_Number, Calibration_Date_DD, Calibration_Date_MM, Calibration_Date_YY, Charger_State, Charger_Alternator_Volts, Charger_Battery_Volts, Charger_Software_Type, Charger_Compatibility_Level, Charger_Status, Charger_Temperature_Scrubber_1, Charger_Temperature_Scrubber_2, Charger_Temperature_Scrubber_3, Charger_Switch_Status, Charger_Temperature_Supercharger, Charger_Oil_Pressure, Charger_Input_Status, Battery_State, Battery_Digital_Input_Byte, Battery_Temperature, Battery_Output_Voltage, Battery_Input_Voltage, Battery_IS_Current, Battery_Digital_OutputSignal_Byte, Battery_Alternator_Voltage, Display_Fixed_1, Display_Fixed_2, Display_Software_Type, Display_Span_Value, Display_Status_Byte_1, Display_Status_Byte_2, Display_Compatibility_Level FROM sqlitestudio_temp_table; DROP TABLE sqlitestudio_temp_table; PRAGMA foreign_keys = 1;


    Tuesday, August 27, 2019 12:46 AM
  • Hi Jack,

    It worked I just had to replace  this

    SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM DADLoggerTable;", m_dbConnection);

    instead of this 

    SQLiteDataAdapter adapter=new SQLiteDataAdapter("Select * From highscores", m_dbConnection);

    As I am using VS2013. 

    Is there is a way to convert an entire folder form .db to .csv instead of one file at a time?

    Again Thanks heaps for your helps and valuable solutions



    • Edited by samiarja Tuesday, August 27, 2019 12:53 AM
    Tuesday, August 27, 2019 12:49 AM
  • Hi samiarja,

    Thanks for the feedback.

    For your new question, you could create a new thread.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 27, 2019 1:17 AM
    Moderator
  • Hi Jack,

    Thank you so much. No Worries, I have created a new thread.

    Tuesday, August 27, 2019 1:32 AM