none
how to upload .csv files from a local directory to SQL server? RRS feed

  • Question

  • Hi There,

    I am developing an application that takes .db files from a remote server and transfer them to a local server using SFTP. and finally convert the files to .csv format. Now I am trying to connect to the SQL server and then upload the .csv file to the server. but the operation is not working as expected. Below is how I did it.

    This is the thread responsible of converting the .db files to .csv and then transfering them to the SQL server.

    public void FileConvertThreadStart()
            {
                int filedownloadcount = 0;
                Console.WriteLine("Start: FileConvertThread");
                for (; ; )
                {
                    Thread.Sleep(1000);
                    while ((_fileDownloadCount - filedownloadcount) > 0)
                    {                    
                        string folderCSV = Path.Combine(localDirectory, "CSV1");
                        var dbFiles = Directory.GetFiles(dirDbFilesDownloadPath, "*db", SearchOption.TopDirectoryOnly);
                        string tablename = string.Empty;
    
                        foreach (var dbFile in dbFiles)
                        {
                            string fileName = Path.GetFileName(dbFile);
                            string destFile = Path.Combine(dirConvertedDBFilesPath, fileName);
    
                            
                            SQLiteConnection sqlLiteConn = new SQLiteConnection("Data Source=" + dbFile);
                            sqlLiteConn.Open();
                            string sqlQuery = string.Format("SELECT * FROM DADLoggerTableEvents");
                            SQLiteDataAdapter sqlLiteAdapter = new SQLiteDataAdapter(sqlQuery, sqlLiteConn);
                            DataSet dataSet = new DataSet();
                            sqlLiteAdapter.Fill(dataSet);
                            DataTable dataTable = dataSet.Tables[0];
    
                            
                            sqlLiteConn.Close();
                            sqlLiteConn.Dispose();
                            sqlLiteAdapter.Dispose();
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
    
                            FileStream fileStream = null;
                            StreamWriter streamWriter = null;
                            try
                            {
                                String dbFileName = Path.GetFileName(dbFile);
    
                                String csvFileName = dbFileName.Replace(".db", ".csv");
    
                                fileStream = new FileStream(folderCSV + "\\" + csvFileName, FileMode.Create, FileAccess.Write);
                                
                                streamWriter = new StreamWriter(fileStream, Encoding.Default);
    
                                var data = string.Empty;
                                for (var i = 0; i < dataTable.Columns.Count; i++)
                                {
                                    data += dataTable.Columns[i].ColumnName;
                                    if (i < dataTable.Columns.Count - 1)
                                    {
                                        data += ",";
                                    }
                                }
                                streamWriter.WriteLine(data);
    
                                for (var i = 0; i < dataTable.Rows.Count; i++)
                                {
                                    data = string.Empty;
                                    for (var j = 0; j < dataTable.Columns.Count; j++)
                                    {
                                        data += dataTable.Rows[i][j].ToString();
                                        if (j < dataTable.Columns.Count - 1)
                                        {
                                            data += ",";
                                        }
                                    }
                                    streamWriter.WriteLine(data);
                                }
                                streamWriter.Close();
                                fileStream.Close();                            
                            }
                            catch (IOException ex)
                            {
                                throw new IOException(ex.Message, ex);
                            }
                            finally
                            {
                                
                                if (removedownloaded == "1")
                                {
                                    File.Delete(dbFile);
                                    Console.WriteLine("File Deleted" + dbFile);
                                }
                                else
                                {                                
                                    if (!File.Exists(destFile))
                                    {
                                        File.Move(dbFile, destFile);
                                        Console.WriteLine("File moved: " + dbFile + "to " + destFile);
                                    }
    
                                }
                            }
    
                        }
                        filedownloadcount++;
                        Console.WriteLine("_fileDownloadCount: {0}", _fileDownloadCount);
                        _fileConvertCount++;
                    }
                }
            }


    Unfortunately, the file is never transferred. Any thought or help is much appreciate it.

    Kind regards,

    Sami


    Sami Arja

    Thursday, September 24, 2020 10:54 AM

Answers

  • Hi samiarja,
    First, you need to check if you have converted  the .db files to .csv format.
    If so, you can use SQL query to import CSV file.(csvTable is my test table name )
    Here is my test code:

          private void button2_Click(object sender, EventArgs e)
            {
                string sql = "BULK INSERT csvTable FROM 'C:\\Users\\Desktop\\cc.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";
                int  i = SqlDesigner.ExecuteNoQuery(sql);
                if (i > 0)
                {
                    MessageBox.Show("success");
                }
            }
        class SqlDesigner
        {
            private static string connStr = "your connectString";
    
            public static int ExecuteNoQuery(string sql)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
    
                    }
                }
            }
        }

    More details you can refer to SarangArd's answer.
    Here is a similar thread you can refer to. 
    Best Regards,
    Daniel Zhang


    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 Thursday, October 1, 2020 10:54 AM
    Friday, September 25, 2020 8:51 AM

All replies

  • Hello,

    Have you tried

    • The process in a single thread?
    • Using the debugger to step through the code looking for issues or incorrect code logic?

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, September 24, 2020 11:40 AM
    Moderator
  • The code you posted doesn't transfer anything. All it is doing is enumerating data from each of your files and generating a CSV file (incorrectly I might add). So are the CSV files getting generated properly? If so then the above method isn't useful here as it is working. If not then what is going wrong with the file?

    As for your CSV file you aren't taking strings into account so any values that are text and contain commas will mangle the CSV file you're trying to generate. You need to normalize the data. Additionally some of this code can be dramatically simplified down. Specifically you shouldn't be manually appending commas to strings. Use String.Join to handle that once you've got the array of data. For DataRow itself there is already the ItemArray property that contains the data in an array so you can theoretically just send that to String.Join however you'll need to encode the strings first.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, September 24, 2020 6:03 PM
    Moderator
  • Hi samiarja,
    First, you need to check if you have converted  the .db files to .csv format.
    If so, you can use SQL query to import CSV file.(csvTable is my test table name )
    Here is my test code:

          private void button2_Click(object sender, EventArgs e)
            {
                string sql = "BULK INSERT csvTable FROM 'C:\\Users\\Desktop\\cc.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";
                int  i = SqlDesigner.ExecuteNoQuery(sql);
                if (i > 0)
                {
                    MessageBox.Show("success");
                }
            }
        class SqlDesigner
        {
            private static string connStr = "your connectString";
    
            public static int ExecuteNoQuery(string sql)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = sql;
                        return cmd.ExecuteNonQuery();
    
                    }
                }
            }
        }

    More details you can refer to SarangArd's answer.
    Here is a similar thread you can refer to. 
    Best Regards,
    Daniel Zhang


    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 Thursday, October 1, 2020 10:54 AM
    Friday, September 25, 2020 8:51 AM
  • I have a directory with .csv files, and I want to iterate through all of them and transfer them to the server. Below is what I did to include the file name from the directory, but it sql query become like that "BULK INSERT csvTable FROMC:\\temp\\CSV1\\DADLoggerDB_2020-09-16T17_00_00.csvWITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

    I understand that it should be like that "BULK INSERT csvTable FROM 'C:\\temp\\CSV1\\DADLoggerDB_2020-09-16T17_00_00.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"

    The problem is that there is no spacing between the file path in the query strings.

    how to modify it so It can include the new file in each new query?

     var csvFiles = Directory.GetFiles(dirCSVPath, "*csv", System.IO.SearchOption.TopDirectoryOnly);
    foreach (var csvFile in csvFiles)
    {
        Console.WriteLine("CSV File name is: " + csvFile);
        string sql = "BULK INSERT csvTable FROM" + "" + csvFile + "" + "WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";
        int i = SqlDesigner.ExecuteNoQuery(sql);
        if (i > 0)
           {
               Console.WriteLine("csv to SQL imported successfully");
           }
    }

    Thank you very much in advance.


    Sami Arja

    Friday, September 25, 2020 10:01 AM
  • sorry for the previous silly question. I fixed it. 

    var csvFiles = Directory.GetFiles(dirCSVPath, "*csv", System.IO.SearchOption.TopDirectoryOnly);
    foreach (var csvFile in csvFiles)
    {
          Console.WriteLine("CSV File name is: " + csvFile);
          string sql = "BULK INSERT csvTable FROM" + " " + "'" + csvFile + "'" + " " + "WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";
          int i = SqlDesigner.ExecuteNoQuery(sql);
          if (i > 0)
             {
                  Console.WriteLine("csv to SQL imported successfully");
              }
    }
    But now I got this error System.Data.SqlClient.SqlException: 'Invalid object name in 

    return cmd.ExecuteNonQuery();

    What caused this error, and how to solve this problem?


    Sami Arja

    Friday, September 25, 2020 10:12 AM
  • There is no SqlDesigner in .NET. If you're using a third party library then you should post to their forums as we have no knowledge of how they work.

    Shouldn't the second parameter to GetFiles be "*.csv"?

    You can also make your SQL string more readable, and hence easier to debug, using string interpolation.

    string sql = $"BULK INSERT csvTable FROM '{csvFile}' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')";


    Michael Taylor http://www.michaeltaylorp3.net

    Friday, September 25, 2020 1:08 PM
    Moderator