none
Dynamically Insert CSV to SQL Server RRS feed

  • Question

  • Hi Experts, 

    I have 3-4 types of CSV each containing different attributes. Let say CSV name

    1. Employees_2019_11_19.csv , Employees_2019_12_19.csv , Employees_2019_09_19.csv

    2. Members_2019_11_19.csv, Members_2019_12_19.csv

    3. Address_2019_12_01.csv, Address_2020_01_01.csv

    So, the first initial name in this case Employees, Memebers, Address directly map to a table name in SQL Server. Am a bit new to C# and using SQL Server I could have used SSIS/ BulkCopy dynamic stored procedure but not sure how to do same from C# to dynamically based on the file name

    1. Perform a bulk load "SQLBulkCopy"

    2. Delete the file once insertion is complete. 

    Could you please share an example that dynamically based on file name adjust the destination mapped table and load it.

    Thanks

    Priya

    Tuesday, November 19, 2019 8:18 AM

Answers

  • Hi Priya,

    Thanks for your feedback.

    I modified my code by using SQLBulkCopy, this is the code:

            static void Main(string[] args)
            {
                String[] files = Directory.GetFiles(@"d:\test");
                
                foreach (var file in files)
                {
                   DataTable table = ReadFile(file);
                   FileInfo fileInfo = new FileInfo(file);
                   String name = fileInfo.Name.Split(new char[] { '_' })[0];
                    InsertIntoSqlServer(table, name);
                    //File.Delete(file);
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            public static DataTable ReadFile(String path)
            {
                using (var reader = new StreamReader(path))
                using (var csv = new CsvReader(reader))
                {
                    using (var dr = new CsvDataReader(csv))
                    {
                        var dt = new DataTable();
                        dt.Load(dr);
                        return dt;
                    }
                }
            }
            public static void InsertIntoSqlServer(DataTable table,String tableName)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = $"dbo.{tableName}";
                        DataTableReader reader = table.CreateDataReader();
                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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 Priya Bange Friday, November 22, 2019 11:04 AM
    Thursday, November 21, 2019 8:40 AM

All replies

  • Hi Experts, 

    I found something that I can use to perform the data operation

    1. I can read the csv file name which will be the target in SQL Server

    2. The below code can do dynamic reference to csv using the csvhelper directive

    Am still trying to find out, how can I dynamically switch the table name while do insert and use the data in records variable from the below code  to perform insert into SQL Server..

    INSERT INTO (dyanimic based on File Name) Values records variable 

        public void Process()
            {
                using (StreamReader input = File.OpenText(InputFilePath))
                using (CsvReader csvReader = new CsvReader(input))
                {
                    IEnumerable<dynamic> records = csvReader.GetRecords<dynamic>();
    
                    
    
                    foreach (var record in records)
                    {
                        Console.WriteLine(record.OrderNumber);
                        Console.WriteLine(record.CustomerNumber);
                        Console.WriteLine(record.Description);
                        Console.WriteLine(record.Quantity);
                    }
                }
            }

    Tuesday, November 19, 2019 9:20 AM
  • Hi Priya,

    Thank you for posting here.

    According to your description, you want to insert data into different database tables based on the file name.

    I wrote a sample code, but you should follow the rules below to get it working.

    First, the name of the csv file must correspond to the table name.

    For example: the file name: Employee_1.csv; table name: Employee.

    Second, the header of the csv file must correspond to the column name of the table.

    For example:

    Csv file header:

    Table column name:

    This is the code:

    class Program
        {
            static void Main(string[] args)
            {
                String[] files = Directory.GetFiles(@"d:\test");
                
                foreach (var file in files)
                {
                    ReadFile(file);
                    File.Delete(file);
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            public static void ReadFile(String path)
            {
                using (StreamReader input = File.OpenText(path))
                {
                    FileInfo fileInfo = new FileInfo(path);
                    String name = fileInfo.Name.Split(new char[] { '_'})[0];
    
                    using (CsvReader csvReader = new CsvReader(input))
                   {
                        csvReader.Read();
                        csvReader.ReadHeader();
    
                        IEnumerable<dynamic> records = csvReader.GetRecords<dynamic>();
    
                        List <String> sqls = ConstructSql(name, records);
                        foreach (var sql in sqls)
                        {
                            InsertIntoSqlServer(sql);
                        }
                    }
                }
            }
            public static List<String> ConstructSql(String tableName,  IEnumerable<dynamic> values)
            {
                List<String> sqls = new List<string>();
              
                foreach (var record in values)
                {
                    String sql = "insert into " + tableName + " values('";
                    if (record != null)
                    {
                        var valorCampos = "";
                        foreach (var item in record)
                        {
                            valorCampos = item.Value.ToString();
                            sql += valorCampos + "','";
                        }
                        sql += ")";
                        String realSql = sql.Remove(sql.Length - 3, 2);
                        sqls.Add(realSql);
                    }
                }
             
                return sqls;
            }
    
            public static void InsertIntoSqlServer(String sql)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    SqlCommand sqlCommand = new SqlCommand(sql,connection);
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

    Another problem is that I didn't use SQLBulkCopy. If you insist on using it, let me know and I will modify it.

    Hope this could be helpful.

    Best Regards, 

    Timon


    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, November 20, 2019 5:21 AM
  • Hi Priya,

    Thank you for posting here.

    According to your description, you want to insert data into different database tables based on the file name.

    I wrote a sample code, but you should follow the rules below to get it working.

    First, the name of the csv file must correspond to the table name.

    For example: the file name: Employee_1.csv; table name: Employee.

    Second, the header of the csv file must correspond to the column name of the table.

    For example:

    Csv file header:

    Table column name:

    This is the code:

    class Program
        {
            static void Main(string[] args)
            {
                String[] files = Directory.GetFiles(@"d:\test");
                
                foreach (var file in files)
                {
                    ReadFile(file);
                    File.Delete(file);
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            public static void ReadFile(String path)
            {
                using (StreamReader input = File.OpenText(path))
                {
                    FileInfo fileInfo = new FileInfo(path);
                    String name = fileInfo.Name.Split(new char[] { '_'})[0];
    
                    using (CsvReader csvReader = new CsvReader(input))
                   {
                        csvReader.Read();
                        csvReader.ReadHeader();
    
                        IEnumerable<dynamic> records = csvReader.GetRecords<dynamic>();
    
                        List <String> sqls = ConstructSql(name, records);
                        foreach (var sql in sqls)
                        {
                            InsertIntoSqlServer(sql);
                        }
                    }
                }
            }
            public static List<String> ConstructSql(String tableName,  IEnumerable<dynamic> values)
            {
                List<String> sqls = new List<string>();
              
                foreach (var record in values)
                {
                    String sql = "insert into " + tableName + " values('";
                    if (record != null)
                    {
                        var valorCampos = "";
                        foreach (var item in record)
                        {
                            valorCampos = item.Value.ToString();
                            sql += valorCampos + "','";
                        }
                        sql += ")";
                        String realSql = sql.Remove(sql.Length - 3, 2);
                        sqls.Add(realSql);
                    }
                }
             
                return sqls;
            }
    
            public static void InsertIntoSqlServer(String sql)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    SqlCommand sqlCommand = new SqlCommand(sql,connection);
                    sqlCommand.ExecuteNonQuery();
                }
            }
        }

    Another problem is that I didn't use SQLBulkCopy. If you insist on using it, let me know and I will modify it.

    Hope this could be helpful.

    Best Regards, 

    Timon


    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.


    Thank you so much.. This is really very supportive is it possible to share the SQLBulkCopy as there will be multiple files need to processed with high frequency. 

    Thanks again

    Priya

    Wednesday, November 20, 2019 4:00 PM
  • Hi Priya,

    Thanks for your feedback.

    I modified my code by using SQLBulkCopy, this is the code:

            static void Main(string[] args)
            {
                String[] files = Directory.GetFiles(@"d:\test");
                
                foreach (var file in files)
                {
                   DataTable table = ReadFile(file);
                   FileInfo fileInfo = new FileInfo(file);
                   String name = fileInfo.Name.Split(new char[] { '_' })[0];
                    InsertIntoSqlServer(table, name);
                    //File.Delete(file);
                }
                Console.WriteLine("Press any key to continue...");
                Console.ReadKey();
            }
            public static DataTable ReadFile(String path)
            {
                using (var reader = new StreamReader(path))
                using (var csv = new CsvReader(reader))
                {
                    using (var dr = new CsvDataReader(csv))
                    {
                        var dt = new DataTable();
                        dt.Load(dr);
                        return dt;
                    }
                }
            }
            public static void InsertIntoSqlServer(DataTable table,String tableName)
            {
                using (SqlConnection connection = new SqlConnection(@"ConnString"))
                {
                    connection.Open();
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                    {
                        bulkCopy.DestinationTableName = $"dbo.{tableName}";
                        DataTableReader reader = table.CreateDataReader();
                        try
                        {
                            // Write from the source to the destination.
                            bulkCopy.WriteToServer(reader);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
            }
    

    Hope this could be helpful.

    Best Regards,

    Timon


    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 Priya Bange Friday, November 22, 2019 11:04 AM
    Thursday, November 21, 2019 8:40 AM