locked
Update/Insert data from text file to sql server db

    Question

  • I am having one text file like below:

    test.txt

    abc||abc1||abc2

    ddc||change||aff2

    newdata||newdata1||newdata2

    Now I need to

    1. Insert if new records are there in the text file.

    2. Update if there are any changes in the already existsing field.

    to sql server data base how can I achive this using c#.

    Data Base structure:

    Column1   Column2   Column3

     abc             abc1          abc2

     ddc             dfc1           aff2

    Now I need to update one field and Insert one row to this table .

     Please give me code.

    Regards,

    Santosh

    Tuesday, October 30, 2012 9:50 AM

Answers

  • Hi Santosh,

    Your question contains several steps...

    1. How can I read from a file?
    You can read a (text)file using a StreamReader. A sample implementation can be found here.

    2. How can I get the data values from a read line?
    You can use string.Split to split on the pipe ( | ). Because you have two of them to split values, you can use StringSplitOptions.RemoveEmptyEntries.

    3. How can I determine to Add or Update?
    Query the database with the ID from the line. If there's no result it's a new record, otherwise it's an existing record. Examples of database usage can be found all over the placehere for example.

    Hope this helps


    Please mark the best replies as answers - Twitter: @rickvdbosch - Blog: http://bloggingabout.net/blogs/rick

    Tuesday, October 30, 2012 10:33 AM
  • Hi Santosh,

    If your file has a lot of records, read all ID's in memory (in a List<string> for instance) and check if the ID of the line you just read from file is present in the list. Hint: check if the list contains the value.

    Hope this helps


    Please mark the best replies as answers - Twitter: @rickvdbosch - Blog: http://bloggingabout.net/blogs/rick

    Tuesday, October 30, 2012 11:17 AM
  • Santosh,

    The same could have been a lot easier if you could have manged to get CSV file. [ You already are using "||" as deliminator ]

    I've attached the text file that I used for this project click  to view.

    Anyways... I hope following helps you, if it does, mark it as an answer.

    You can also download the project by : clicking here

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    using System.IO;
    using System.Data.SqlClient;
    
    namespace TextToSQLServer
    {
        class Program
        {
            static void Main(string[] args)
            {
                //Replace with the path you've got. I've had created a brand new for this application using the Northwind database on my SQL Server
                //Query that I used : 
                
                //USE Northwind
                //SELECT TOP 10 CustomerID, ContactName, Country FROM Customers
    
                string[] file = File.ReadAllLines(@"C:\Data\TextDataPipeDeliminated.txt");
    
                // I am declaring some variables out-side the loop to prevetnt to prevent its greed for memmory.
                string[] temp;
                string[] columns;
                string QueryString;
    
                //Initiate follwing with yours
                //const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>";
    
                //SqlConnection Connection = new SqlConnection(ConnectionString);
                //SqlCommand InsertCommand;
    
                foreach (string line in file)
                {
                    //Okay now we are in the text file, and trying to devide and manipluate the records.
                    temp = line.Split('|');
    
                    //temp is has some null strings, let's remove those.
                    columns = temp.Where(s => !string.IsNullOrWhiteSpace(s)).ToArray();
    
                    //Just in the case if you want to make sure that We have got all your records, cell by cell, uncomment the following loop.
                    
                    //foreach (string col in columns)
                    //{
                    //Console.WriteLine(col);
                    //}
                    
                    //Let's check for the constraints --- assuming that all fields are requiered as per your schema
                    QueryString = string.Empty;
                    QueryString = @"INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES (";
                    if (columns.Length == 3)
                    {
                        //Now is the time to build the insert query string dynamically !
                        for (int i = 0; i < columns.Length; i++)
                        {
                            QueryString += "'" + columns[i].ToString() + "'";
                            if (i < (columns.Length) - 1)
                                QueryString += ",";
                            else
                                QueryString += ")";
                        }
                    }
                    Console.WriteLine(QueryString);
    
                    /*Output so far (I've NOT tested the database stuff, sorry for being careless, but I am running out of time :
                     * 
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('ALFKI','Maria Anders','Germany')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('ANATR','Ana Trujillo','Mexico')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('ANTON','Antonio Moreno','Mexico')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('AROUT','Thomas Hardy','UK')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BERGS','Christina Berglund','Sweden')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BLAUS','Hanna Moos','Germany')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BLONP','Frederique Citeaux','France')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BOLID','Martin Sommer','Spain')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BONAP','Laurence Lebihan','France')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BOTTM','Elizabeth Lincoln','Canada')
                     *  
                     * */
    
                    //Now that you have the query string. Use the connection string, create and open the connnection, use the query srting as a command and fire !
    
                    //Connection.Open();
                    //InsertCommand = new SqlCommand(QueryString);
                    //InsertCommand.ExecuteNonQuery();
                }
    
                Console.ReadKey();
            }
        }
    }



    Aarsh Talati



    • Proposed as answer by Aarsh (MCTS) Wednesday, October 31, 2012 2:32 PM
    • Edited by Aarsh (MCTS) Wednesday, October 31, 2012 2:34 PM to add details
    • Marked as answer by SantoshHegde Thursday, November 01, 2012 3:34 PM
    Wednesday, October 31, 2012 2:30 PM

All replies

  • Hi Santosh,

    Your question contains several steps...

    1. How can I read from a file?
    You can read a (text)file using a StreamReader. A sample implementation can be found here.

    2. How can I get the data values from a read line?
    You can use string.Split to split on the pipe ( | ). Because you have two of them to split values, you can use StringSplitOptions.RemoveEmptyEntries.

    3. How can I determine to Add or Update?
    Query the database with the ID from the line. If there's no result it's a new record, otherwise it's an existing record. Examples of database usage can be found all over the placehere for example.

    Hope this helps


    Please mark the best replies as answers - Twitter: @rickvdbosch - Blog: http://bloggingabout.net/blogs/rick

    Tuesday, October 30, 2012 10:33 AM
  • Hi,

    From the above point Query the database with the ID from the line --> It is costlier right because I have text file which contains more than 5000 line.Each line if I call DB it will be very slow.

    If I am correct can you please send alternative.

    Regards,

    Santosh

    Tuesday, October 30, 2012 10:59 AM
  • Hi,

    From the above point Query the database with the ID from the line --> It is costlier right because I have text file which contains more than 5000 line.Each line if I call DB it will be very slow.

    If I am correct can you please send alternative.


    Or you do this, or when you insert you create a new txt file, that will have just inserted records, then when you make insert again, you compare both, and do what you need.

    If you get your question answered, please come back and Alternate TextMark As Answer.
    Web Developer

    Tuesday, October 30, 2012 11:01 AM
  • Hi Santosh,

    If your file has a lot of records, read all ID's in memory (in a List<string> for instance) and check if the ID of the line you just read from file is present in the list. Hint: check if the list contains the value.

    Hope this helps


    Please mark the best replies as answers - Twitter: @rickvdbosch - Blog: http://bloggingabout.net/blogs/rick

    Tuesday, October 30, 2012 11:17 AM
  • Santosh,

    The same could have been a lot easier if you could have manged to get CSV file. [ You already are using "||" as deliminator ]

    I've attached the text file that I used for this project click  to view.

    Anyways... I hope following helps you, if it does, mark it as an answer.

    You can also download the project by : clicking here

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    using System.IO;
    using System.Data.SqlClient;
    
    namespace TextToSQLServer
    {
        class Program
        {
            static void Main(string[] args)
            {
                //Replace with the path you've got. I've had created a brand new for this application using the Northwind database on my SQL Server
                //Query that I used : 
                
                //USE Northwind
                //SELECT TOP 10 CustomerID, ContactName, Country FROM Customers
    
                string[] file = File.ReadAllLines(@"C:\Data\TextDataPipeDeliminated.txt");
    
                // I am declaring some variables out-side the loop to prevetnt to prevent its greed for memmory.
                string[] temp;
                string[] columns;
                string QueryString;
    
                //Initiate follwing with yours
                //const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>";
    
                //SqlConnection Connection = new SqlConnection(ConnectionString);
                //SqlCommand InsertCommand;
    
                foreach (string line in file)
                {
                    //Okay now we are in the text file, and trying to devide and manipluate the records.
                    temp = line.Split('|');
    
                    //temp is has some null strings, let's remove those.
                    columns = temp.Where(s => !string.IsNullOrWhiteSpace(s)).ToArray();
    
                    //Just in the case if you want to make sure that We have got all your records, cell by cell, uncomment the following loop.
                    
                    //foreach (string col in columns)
                    //{
                    //Console.WriteLine(col);
                    //}
                    
                    //Let's check for the constraints --- assuming that all fields are requiered as per your schema
                    QueryString = string.Empty;
                    QueryString = @"INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES (";
                    if (columns.Length == 3)
                    {
                        //Now is the time to build the insert query string dynamically !
                        for (int i = 0; i < columns.Length; i++)
                        {
                            QueryString += "'" + columns[i].ToString() + "'";
                            if (i < (columns.Length) - 1)
                                QueryString += ",";
                            else
                                QueryString += ")";
                        }
                    }
                    Console.WriteLine(QueryString);
    
                    /*Output so far (I've NOT tested the database stuff, sorry for being careless, but I am running out of time :
                     * 
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('ALFKI','Maria Anders','Germany')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('ANATR','Ana Trujillo','Mexico')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('ANTON','Antonio Moreno','Mexico')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('AROUT','Thomas Hardy','UK')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BERGS','Christina Berglund','Sweden')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BLAUS','Hanna Moos','Germany')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BLONP','Frederique Citeaux','France')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BOLID','Martin Sommer','Spain')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BONAP','Laurence Lebihan','France')
                     *  INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ('BOTTM','Elizabeth Lincoln','Canada')
                     *  
                     * */
    
                    //Now that you have the query string. Use the connection string, create and open the connnection, use the query srting as a command and fire !
    
                    //Connection.Open();
                    //InsertCommand = new SqlCommand(QueryString);
                    //InsertCommand.ExecuteNonQuery();
                }
    
                Console.ReadKey();
            }
        }
    }



    Aarsh Talati



    • Proposed as answer by Aarsh (MCTS) Wednesday, October 31, 2012 2:32 PM
    • Edited by Aarsh (MCTS) Wednesday, October 31, 2012 2:34 PM to add details
    • Marked as answer by SantoshHegde Thursday, November 01, 2012 3:34 PM
    Wednesday, October 31, 2012 2:30 PM
  • For your requirement to update the database, I guess you can check using a simple query if the record already exists, if so, just modify the program and instead firing the insert query, use

    UPDATE 

    You may check for existing records using something like this (just in the case if you are wondering)

    IF EXISTS (SELECT * FROM YourTable WHERE Key = 123)
       PRINT 'Exists'
    ELSE
       PRINT 'Does not Exist'

    .

    .

    .

    This is a similar story.


    Aarsh Talati



    • Edited by Aarsh (MCTS) Wednesday, October 31, 2012 2:47 PM
    • Proposed as answer by Aarsh (MCTS) Thursday, November 01, 2012 5:53 PM
    Wednesday, October 31, 2012 2:46 PM