CODEWITHSUNDEEP
c#appendtext-files
Bob Gatto
Bob Gatto

Reputation: 131

How do I add records to a table from a text file?

I have a table called statelist that looks like this:

abbrev nvarchar(2);
name   nvarchar(50);

I have a text file called state_list.txt which has a list of states in this format:

'AL','Alabama'
'AK','Alaska'
'AR','Arkansas'

I want to get the data from the text file into the statelist table. Is there some kind of SQL Insert or other code to do this?

Upvotes: 0

Views: 1049

Answers (5)

loneshark99
loneshark99

Reputation: 714

Remove the ' from the file and then run below command.

Bulk Insert [dbo].[TableName]
From 'C:\FileName.csv'
with
(
FIELDTERMINATOR = ',', -- For Comma Seperated File
RowTerminator = '\n' -- RowTernimator i.e new Line
)

Did this a while back and added to blog at that time -- http://dotnetdevblog.blogspot.com/2007/10/bulk-inserting-from-csv-file-into.html

Upvotes: 0

Iman
Iman

Reputation: 145

One way I can think of is reading each line from text file using ReadLine(), and then use Split(',') on that line to get the abbreviation and name in a string array, and then you can easily add them to your database in a while loop.

Upvotes: 0

Justin Loveless
Justin Loveless

Reputation: 522

If you are using Entity Framework, you can use this approach.

   using(var context = new YourContext())
   {
        List<YourEntity> entities = new List<YourEntity>();
        File.ReadAllLines("PathToFile")
            .ToList()
            .ForEach(s =>
            {
               string[] split = s.Split(',');
               someList.Add(new YourEntity(split[0], split[1])); // Or set properties if not using a constructor.
            });

        context.YourTable.AddRange(entities);
        context.SaveChanges();
   }

Upvotes: 0

Robert Green MBA
Robert Green MBA

Reputation: 1876

(1)Parse the .txt file and (2)add the records to the DB. You can use entity framework, or any other ORM, or use ADO like @Jurriaan suggested.

Upvotes: 0

Jurriaan Buitenweg
Jurriaan Buitenweg

Reputation: 422

Well you didnt specify things like your database etc but this would be my best guess. i build this for a MsSql databse but you can adjust it for mySql in a few simple steps(First install mySQL connector). I dont have a MsSQl database at my disposal atm so i didnt try it at home but it should work like this. Ofcourse edit the connection string so it fits your database

        string fileName = "state_list.txt";
        var lines = File.ReadLines(fileName);

        Dictionary<string,string> splitted = new Dictionary<string,string>();
        foreach (var line in lines)
        {
            string[] splitter = line.Split(',');
            splitted.Add(splitter[0], splitter[1]); //Add eatch splitted line to dictionary so you can use key and value to insert into table
        }


        string connStr ="server = localhost; user = root; database = yourdb; port = 3306; password = xxxxxxxxx;"; // CREATE CONNECTION WITH YOUR DATABASE INFORMATION
        SqlConnection conn = new SqlConnection(connStr);
        try
        {
            conn.Open();
            SqlCommand comm = conn.CreateCommand();
            comm.CommandText = "INSERT INTO state_list(code,area) VALUES(@code, @area)";

            foreach (KeyValuePair<string, string> pair in splitted)
            {
                comm.Parameters.Add("@code", pair.Key);
                comm.Parameters.Add("@areas", pair.Value);
                comm.ExecuteNonQuery(); // INSERT EACH PAIR INTO DATABASE
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        conn.Close(); //CLOSE CONNECTION

Upvotes: 1

Related Questions