locked
How to iterate through a folder with .db files and convert them to .csv? RRS feed

  • Question

  • I have around 1,186 files with .db file format. These files need to be converted to .csv in order to do further data processing.

    Until now one file was successfully converted to CSV, using SQLiteConnection and SQLiteDataAdapter, all the credit goes to Jack J Jun for helping me with it.

    In the button click event, the .db file path is set and the SQLiteConnection was used to connect to the SQLite server, as shown below

    string _filePath = @"C:\temp\Data2\DADLoggerDB_2000-01-01T00_00_15.db";
    SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + _filePath);
    m_dbConnection.Open();
    SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM DADLoggerTable;", m_dbConnection);
    DataSet set = new DataSet();
    adapter.Fill(set);
    DataTable table = set.Tables[0];
    //Where the csv file will be saved
    string filepath = @"C:\temp\Data2\CSV\";
    SaveCsv(table, filepath);
    MessageBox.Show("Converted");

    and a SaveCsv method is implemented as below:

    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();
                }
            }

    Until now I can convert only one file to .CSV at a time, Ultimately all the .db files need to be converted to .csv automatically.

    I appreciate any help in this regards.

    Tuesday, August 27, 2019 1:31 AM

Answers

  • Hi samiarja,

    Thank you for posting here.

    Since every db file has its only table, we only could convert them one by one.

    You could try the following code.

     private void Button1_Click(object sender, EventArgs e)
            {
                string path = "D:\\DB";
                var files = Directory.GetFiles(path, "*db", SearchOption.AllDirectories);
                string tablename = string.Empty;
                foreach (var item in files)
                {
                    if(item.Contains("Test.db"))
                    {
                        tablename = "Student";
                        DataTable dt = ConverttoDatatable(item, tablename);
                        SaveCsv(dt, path, tablename);
                    }
                    if (item.Contains("MyDatabase.db"))
                    {
                        tablename = "highscores";
                        DataTable dt = ConverttoDatatable(item, tablename);
                        SaveCsv(dt, path, tablename);
                    }
                }
    
                MessageBox.Show("success");
    
    
            }
            public DataTable ConverttoDatatable(string path,string tablename)
            {
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path);
                m_dbConnection.Open();
                string sql = string.Format("SELECT * FROM {0}",tablename);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, m_dbConnection);
                DataSet set = new DataSet();
                adapter.Fill(set);
                DataTable table = set.Tables[0];
                return table;
            }
    
            public void SaveCsv(DataTable dt, string filePath,string tablename)
            {
                FileStream fs = null;
                StreamWriter sw = null;
                try
                {
                    fs = new FileStream(filePath +"\\"+ 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();
                }
            }

    Note: I have modified my code about SaveCsv method.

    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 Wednesday, August 28, 2019 1:52 AM
    Tuesday, August 27, 2019 2:21 AM

All replies

  • I have tried to implement this and iterate through all the file in the folder, But It end up only converting one file to CSV and then for the next file it replace it with a new CSV file.

    DirectoryInfo d = new DirectoryInfo(@"C:\temp\Data2");
    FileInfo[] Files = d.GetFiles("*.db"); //Getting Text files
    string str = "";
    foreach (FileInfo file in Files)
    {
                        str = file.Name;
                        //MessageBox.Show(str);
                        string _filePath = @"C:\temp\Data2\";
                        SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + _filePath + str);
                        m_dbConnection.Open();
                        SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM DADLoggerTable;", m_dbConnection);
                        DataSet set = new DataSet();
                        adapter.Fill(set);
                        DataTable table = set.Tables[0];
                        string filepath = @"C:\temp\Data2\CSV\";
                        SaveCsv(table, filepath);
                        MessageBox.Show("Converted");
    }
    I am not able to create a new .csv file for each db file.

    • Edited by samiarja Tuesday, August 27, 2019 2:11 AM
    Tuesday, August 27, 2019 2:10 AM
  • Hi samiarja,

    Thank you for posting here.

    Since every db file has its only table, we only could convert them one by one.

    You could try the following code.

     private void Button1_Click(object sender, EventArgs e)
            {
                string path = "D:\\DB";
                var files = Directory.GetFiles(path, "*db", SearchOption.AllDirectories);
                string tablename = string.Empty;
                foreach (var item in files)
                {
                    if(item.Contains("Test.db"))
                    {
                        tablename = "Student";
                        DataTable dt = ConverttoDatatable(item, tablename);
                        SaveCsv(dt, path, tablename);
                    }
                    if (item.Contains("MyDatabase.db"))
                    {
                        tablename = "highscores";
                        DataTable dt = ConverttoDatatable(item, tablename);
                        SaveCsv(dt, path, tablename);
                    }
                }
    
                MessageBox.Show("success");
    
    
            }
            public DataTable ConverttoDatatable(string path,string tablename)
            {
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path);
                m_dbConnection.Open();
                string sql = string.Format("SELECT * FROM {0}",tablename);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, m_dbConnection);
                DataSet set = new DataSet();
                adapter.Fill(set);
                DataTable table = set.Tables[0];
                return table;
            }
    
            public void SaveCsv(DataTable dt, string filePath,string tablename)
            {
                FileStream fs = null;
                StreamWriter sw = null;
                try
                {
                    fs = new FileStream(filePath +"\\"+ 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();
                }
            }

    Note: I have modified my code about SaveCsv method.

    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 Wednesday, August 28, 2019 1:52 AM
    Tuesday, August 27, 2019 2:21 AM
  • Hi Jack, 

    Thank you for your solution.

    All the .db files have the same table named "DADLoggerTable". How can this be implemented such that the results will be 

    table1.csv

    table2.csv

    table3.csv

    ............

    Tuesday, August 27, 2019 2:37 AM
  • Hi samiarja,

    Thanks for the feedback.

    You could try the following code to do it.

    foreach (var item in files) { if(item.Contains("Test.db")) { tablename = "Student"; DataTable dt = ConverttoDatatable(item, tablename); SaveCsv(dt, path, "table1"); } if (item.Contains("MyDatabase.db")) { tablename = "highscores"; DataTable dt = ConverttoDatatable(item, tablename); SaveCsv(dt, path, "table2"); }

    .....

    }

    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 2:41 AM
  • Hi Jack,

    Thanks for your solutions. This helps a lot.

    using your code (Million thanks for SaveCsv and Convertodatatable implementation) I did some minor modification to download all the data in one go by taking the last three characters in each file, in my case they all end up with".db" and if the condition met then the files will be converted to .CSV automatically and with a different name using (string.Format("DADLoggerTable{0}", i)) where i is the number of files in the folder.

    That way I don't have to write code for every file as they are now 1.1K+ files and they might be more than that in the future.

    I am gonna include the code in case anyone encounter this problem and looking for similar implementation.

    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;
    
                                    string Folder_path = item.destfolder;
                                    var files_path = Directory.GetFiles(Folder_path, "*db", SearchOption.AllDirectories);
                                    string tablename = string.Empty;
                                    string path = remoteDirectory + "/" + remoteFileName;
                                    if ((file.Name.EndsWith(filextension)) || (file.Name.EndsWith(filextension.ToLower())) || (file.Name.EndsWith(filextension.ToUpper())))
                                    {
                                        using (Stream file1 = File.OpenWrite(Path.Combine(localDirectory, remoteFileName)))
                                        {
    
                                            foreach (var item_db in files_path)
                                            {
                                                var result = item_db.Substring(item_db.Length - 3);
                                                for (var i = 0; i < files_path.Count() + 1; i++)
                                                {
                                                    if (result == ".db")
                                                    {
                                                        sftp.DownloadFile(path, file1);
                                                        tablename = string.Format("DADLoggerTable{0}", i);
                                                        DataTable dt = ConverttoDatatable(item_db, tablename);
                                                        SaveCsv(dt, Folder_path + "/CSV", tablename);
                                                    }
                                                }
                                            }

    public DataTable ConverttoDatatable(string path, string tablename)
            {
                SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=" + path);
                m_dbConnection.Open();
                string sql = string.Format("SELECT * FROM DADLoggerTable");
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(sql, m_dbConnection);
                DataSet set = new DataSet();
                adapter.Fill(set);
                DataTable table = set.Tables[0];
                return table;
            }
    
            public void SaveCsv(DataTable dt, string filePath, string tablename)
            {
                FileStream fs = null;
                StreamWriter sw = null;
                try
                {
                    fs = new FileStream(filePath + "\\" + 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();
                //}
            }

    Again thank you very much Jack for you help.

    Wednesday, August 28, 2019 1:39 AM
  • Hi samiarja,

    Thanks for the feedback.

    I glad that your problem has been solved. You could mark your reply or mine as an answer, which will help other members to find the solution quickly if they face the similar issue.

    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.

    Wednesday, August 28, 2019 1:43 AM