Hazael Estrella Hazael Estrella - 2 months ago 12
SQL Question

How to import text file and save data into database?

I have this project:


  1. Import a
    .txt
    file

  2. Connect to a SQL Server database

  3. Transfer all data into the database



The text file is divided by tab in four fields so as the database.

I've done the first step using a rich text box and saving all the data on a string. My idea is to split the string every line and save it on an array but then: How do I split every row so I can save the fields properly? How do I connect a database on SQL Server to my project on C#?

Answer

Let's tackle this one step at a time...

Getting the data:


Get the text file into a string variable.

string readText = File.ReadAllText("path to my file.txt");

More on this here

Now that we have it in a string we can parse it

List<string> listStrLineElements = List<string> listStrLineElements = line.Split(new string[] { Environment.NewLine }, StringSplitOptions.None).ToList();// You need using System.Linq at the top.

More options for splitting at a new line can be found here

Now, you need to split each element into its own row; you said you had it tab delimited which is good (something like comma seperated (CSV) is more common modernly, but tabs will work!)... For that we can do something like this:

List<string> rowList = listStrLineElements.SelectMany(s => s.Split('\t')).ToList();// The \t is an *escape character* meaning tab.

now, you need something like a loop to go through each one of these entries and insert it into the database, which means we now need our database connection...

Connecting to the database


Code Projects (another helpful site) has this tutorial on making a SQL connection from C#; below is a code summary (please read the article, this website is not here to have people write/find code on your behalf!)

Make sure you are using the SQL namespace...

using System.Data.SqlClient;

    private void sqlCon(List<string> x)
    {
        //Replace with your server credentials/info
        SqlConnection myConnection = new SqlConnection("user id=username;" +"password=password;server=serverurl;" +"Trusted_Connection=yes;" +"database=database; " + "connection timeout=30");
        try
        {
            myConnection.Open();
            for (int i = 0; i <= x.Count -4; i += 4)//Implement by 3...
            {
                //Replace table_name with your table name, and Column1 with your column names (replace for all).
                SqlCommand myCommand = new SqlCommand("INSERT INTO table_name (Column1, Column2, Column3, Column4) " +
                                     String.Format("Values ('{0}','{1}','{2}','{3}')", x[i], x[i + 1], x[i + 2], x[i + 3]), myConnection);
                myCommand.ExecuteNonQuery();
            }

        }
        catch (Exception e){Console.WriteLine(e.ToString());}
        try{myConnection.Close();}
        catch (Exception e){Console.WriteLine(e.ToString());}
    }

Note, you may very likely have to change/edit my loop; the logic behind it is to implement i by 4 so you can read each of your columns, so the columns Count (number of entries) must be 4 less so you don't get an index out of bounds (typing this all in notepad I'm not sure if I got all the logic right, but again, if it's wrong that is something you will have to fix, we don't just write code for people).

PLEASE NOTE!

things like string.format() leave you open for an SQL injection, you really should lookup SQL parameters, but this will get the job done. Here are some posts that I consider a must read:

Preventing SQL Injections in C#

What is an SQL Injection (W3-schools)

Comments