Need to update certain columns in table from csv file [closed]

Question

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Guide the asker to update the question so it focuses on a single, specific problem. Narrowing the question will help others answer the question concisely. You may edit the question if you feel you can improve it yourself. If edited, the question will be reviewed and might be reopened.

Closed 9 years ago.

Improve this question

I have a csv file and I need to update data in only a few columns in my sql database table. What is the best way to do this? I was thinking bulk import however, it will not let me do this without using all of the columns. I was thinking of using format file, but I wanted to know if this is the most efficient way.

Here is how I was trying it from my C# class:

/// <summary>
        /// Update all of the PropertyDefinitions
        /// </summary>
        internal static void InsertPropertyDefinitions()
        {

            //
            //  Define the connection
            //
            SqlConnection insertConnection = null;

            try
            {
                RetryStrategy retryStrategy = new Incremental(5, TimeSpan.FromMilliseconds(500), TimeSpan.FromMilliseconds(3000));
                RetryPolicy retryPolicy = new RetryPolicy<SqlDatabaseTransientErrorDetectionStrategy>(retryStrategy);

                retryPolicy.ExecuteAction(() =>
                {
                    //
                    //  Try to connect to the database
                    //
                    using (insertConnection = new SqlConnection(m_ConnectionString))
                    {
                        //
                        //  Open the connection
                        //
                        insertConnection.Open();

                        //
                        //  Get the insert command ready
                        //
                        using (SqlCommand insertRecordCmd = insertConnection.CreateCommand())
                        {
                            //
                            // Define the Insert command
                            //
                            insertRecordCmd.CommandText = @"
                            BULK INSERT dbo.[PropertyDefinition] 
                            FROM '//my file path'
                            WITH(
                                    FIRSTROW = 2,
                                    FIELDTERMINATOR = ',',
                                    ROWTERMINATOR = '\n'        
                                )
                            ";

                            // Execute the INSERT command
                            insertRecordCmd.ExecuteNonQuery();
                        }

                        insertConnection.Close();
                    }
                });
            }
            catch (Exception ex)
            {
                //
                //  This is unexpected so display full exception information
                //
                m_Log.WriteLine("Exception while creating table");
                m_Log.WriteLine(ex.Message.ToString());
                throw;
            }
        }

Bulk import into a temporary table, and then use just the columns you need from that table to update your own tables. — Ken White
– Ken White, Commented Jun 22, 2016 at 18:27
That is one option. The only thing that may prove difficult with this is if I am trying to continuously update this table by republishing the database and creating a new tenant. — Tim
– Tim, Commented Jun 22, 2016 at 18:30
So basically I am going to send the csv file off to another team in our company. They will update some of the data, send the file back to me and then I want to be able to simply import that data into our current table. — Tim
– Tim, Commented Jun 22, 2016 at 18:31
Why don't you load the CSV into memory and remove the columns you don't want. Then use SqlBulkCopy. The following SO post has an example of creating a DataTable and using SqlBulkCopy. You could modify the code to filter out columns you don't want. stackoverflow.com/questions/20759302/… — William Xifaras
– William Xifaras, Commented Jun 22, 2016 at 18:42

William Xifaras · Accepted Answer · 2016-06-22 20:11:01Z

1

A recommendation would be to put the csv data into memory and filter out the columns you don't want.

The following SO article has an example of how to populate a DataTable from a CSV and use SqlBulkCopy to bulk insert into SQL Server. You can modify the code to filter out the columns you don't want.

Upload CSV file to SQL server

answered Jun 22, 2016 at 20:11

William Xifaras

5,3422 gold badges21 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Need to update certain columns in table from csv file [closed]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related