Importing Text File into SQL Database

RedDragonXXX · Jun 13, 2012

First of all I'm using C#.

I wanted to check if there is anyone in here familiar with doing this. I'm basically trying to import text file into a specific table in the DB. I've never done this before so trying to gather as much resources as possible before I proceed with doing this.

Right now I'm focusing on opening the connection the the SQL itself. I found this code example:

Code:

SqlConnection thisConnection = new SqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=a;Database=Test;

That seems to open connection to a specific DB. But if I wanted to connect to a specific table in the DB where I will be importing the text file, do I need to expand the parameter to specify that table?

Any help is appreciated. Thanks :thup:

Realee · Jun 13, 2012

Here's what I've used in my connection / update strings before now.

Code:

SqlConnection connection = new SqlConnection("user id=USERNAME;" +
                                                         "password=PASSWORD;" +
                                                         "server=SERVER;" +
                                                         "database=DATABASE; " +
                                                         "connection timeout=30");

try
            {
                connection.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

sql = new SqlCommand("INSERT INTO [TABLE] (FIELD1, FIELD2 etc..) VALUES (VALUE1, VALUE2 etc..);
                                    sql.ExecuteNonQuery();

You specify the connection details as an SqlConnection then use the SqlCommand to decide which table you're putting the data.

RedDragonXXX · Jun 14, 2012

That's pretty much how I did it, but can't seem to get any data inserted into a specified table for some reason. Not sure what I'm doing wrong.

Code:

SqlConnection myConnection = new SqlConnection("Data Source=frost; Initial Catalog=Test; Integrated Security=SSPI; Persist Security Info=false; Trusted_Connection=Yes");
            SqlCommand sqlCommand = myConnection.CreateCommand();

            try
            {
                myConnection.Open();
                MessageBox.Show(myConnection.State.ToString());

                sqlCommand.CommandText = "INSERT INTO EuropeCountries VALUES (@CountryName, @Capital, @Region, @Section, @Population)";

                sqlCommand.Parameters["@CountryName"].Value = "USA";
                sqlCommand.Parameters["@Capital"].Value = "Washington";
                sqlCommand.Parameters["@Region"].Value = "North American";
                sqlCommand.Parameters["@Section"].Value = 20;
                sqlCommand.Parameters.AddWithValue("@Population", 654987654);
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception exceptionError)
            {
                Console.WriteLine(exceptionError.ToString());
            }
            myConnection.Close();

Jmtyra · Jun 14, 2012

Finished a similar PowerShell script last week, except that I was exporting data instead of importing it. It's in C#, but I'm not very good with object oriented languages and the outline of this code was taken from a technet post.

Anyway, maybe it will help.

Code:

# Create SqlConnection object and define connection string
$con = New-Object System.Data.SqlClient.SqlConnection
$con.ConnectionString = "Server=localhost; Database=<database>; UID=<user id>; PWD=<user id>"

# Create SqlCommand object, define command text, and set the connection
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.CommandText = "SELECT * FROM [<database>].[<user>].[<table>] WHERE [<ColumnValue>] = 1"
$cmd.Connection = $con

# Create SqlDataAdapter object and set the command
$da = New-Object System.Data.SqlClient.SqlDataAdapter
$da.SelectCommand = $cmd

# Create and fill the DataSet object
$ds = New-Object System.Data.DataSet
$da.Fill($ds) | Out-Null

# Close the connection
$con.close()

Jmtyra · Jun 14, 2012

RedDragonXXX said:
I'm basically trying to import text file into a specific table in the DB.

Can you elaborate please? Are you wanting to take a text file and use it to create a table in a DB? Or create records in an existing table using a given text file? Or something else?

RedDragonXXX · Jun 14, 2012

Jmtyra said:
Can you elaborate please? Are you wanting to take a text file and use it to create a table in a DB? Or create records in an existing table using a given text file? Or something else?

Creating records in an existing table.

Right now I'm just trying to enter anything into a table, but can't get it to work.

RedDragonXXX · Jun 14, 2012

I got it to insert data. I created a new table and changed the column names. This time i didn't include spaces and used slightly different Syntax using SqlDataAdapter.

Code:

SqlConnection myConnection = new SqlConnection("Data Source=frost; Initial Catalog=Test; Integrated Security=SSPI; Persist Security Info=false; Trusted_Connection=Yes");
            SqlDataAdapter da = new SqlDataAdapter();

            try
            {
                da.InsertCommand = new SqlCommand("INSERT INTO Countries(country_name,capital,region,section,population)" + "VALUES(@country_name,@capital,@region,@section,@population)", myConnection);
                
                da.InsertCommand.Parameters.Add("@country_name", SqlDbType.NChar).Value = textBox1.Text;
                da.InsertCommand.Parameters.Add("@capital", SqlDbType.NChar).Value = textBox2.Text;
                da.InsertCommand.Parameters.Add("@region", SqlDbType.NChar).Value = textBox3.Text;
                da.InsertCommand.Parameters.Add("@section", SqlDbType.Int).Value = textBox4.Text;
                da.InsertCommand.Parameters.Add("@population", SqlDbType.Int).Value = textBox5.Text;
                myConnection.Open();
                da.InsertCommand.ExecuteNonQuery();

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

Now I need to figure out how to clear all the data in the table when I do a new insert.

Jmtyra · Jun 14, 2012

Nice, making progress. I looked at some examples from Technet but couldn't figure out how to do what you have posted above.

Importing Text File into SQL Database

RedDragonXXX

Senior RAM Pornographer

Realee

loss.jpg

RedDragonXXX

Senior RAM Pornographer

Jmtyra

Member

Jmtyra

Member

RedDragonXXX

Senior RAM Pornographer

RedDragonXXX

Senior RAM Pornographer

Jmtyra

Member

Similar threads