import this data into SQL Server

[Guest]

Could you please help me in this project. I have aTab delemeted file. It
has 20 columns and some data. I have to import this data into SQL Server. I
am using sql express edition. I am using Window forms to load the files It
doesn’t have import or export wizard. Do you have any idea how can I do that.

 

[Nicholas Paldino [.NET/C# MVP]]

bobby,

You can use the bulk insert statement in SQL Server to do this:

http://msdn2.microsoft.com/en-us/library/ms175937.aspx

Specifically:

http://msdn2.microsoft.com/en-us/library/ms188365.aspx

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You can use either Bulk Insert or DTS to import the data directly into the
SQL.
If you need to make some adjustment to the data you can always read the file
into your program and then use ADo.NET to insert the data. This variant is
slower that the direct ways though.

 

[Guest]

I want to make it user friendily. I want to use window forms and select
files. Is this possible. But after selecting tab delimeted file what should
I do.

 

[Ashot Geodakov]

Open the file as a StreamReader, read it line after line. For each line
read, split it ( string[] arrParams = strLine.Split( new char[] {
'\t' } ) ). Then create a SQL Insert statement with parameters and execute
it using SqlCommand object:

1: SqlCommand cmd = new SqlCommand( "insert into table1( field1, field2 )
values( @field1, @field2 )", connection );
2: cmd.Parameters.Add( new SqlParameter( "@field1", arrParams[0] );
3: cmd.Parameters.Add( new SqlParameter( "@field2", arrParams[1] );
4: cmd.ExecuteNonQuery();

For each next line of text, lines 2 and 3 of the sample will change to:

cmd.Parameters["@field1"].Value = arrParams[0];
cmd.Parameters["@field2"].Value = arrParams[1];

 

[Nicholas Paldino [.NET/C# MVP]]

That's a VERY, VERY roundabout way. The bulk importer is a much, much
better option.

If there is a need to manipulate the data, an easy (and faster) way to
do it in SQL Server would be to have a stored procedure which will create
the temporary table, and then use the bulk importer to import into the
temporary table (you can create a string to execute the bulk importer and
pass it to sp_executesql). Once the data is imported into the temporary
table, you can issue queries to manipulate the data, and then insert it from
the temp table into the destination table.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Open the file as a StreamReader, read it line after line. For each line
read, split it ( string[] arrParams = strLine.Split( new char[] {
'\t' } ) ). Then create a SQL Insert statement with parameters and execute
it using SqlCommand object:

1: SqlCommand cmd = new SqlCommand( "insert into table1( field1, field2 )
values( @field1, @field2 )", connection );
2: cmd.Parameters.Add( new SqlParameter( "@field1", arrParams[0] );
3: cmd.Parameters.Add( new SqlParameter( "@field2", arrParams[1] );
4: cmd.ExecuteNonQuery();

For each next line of text, lines 2 and 3 of the sample will change to:

cmd.Parameters["@field1"].Value = arrParams[0];
cmd.Parameters["@field2"].Value = arrParams[1];

I want to make it user friendily. I want to use window forms and select
files. Is this possible. But after selecting tab delimeted file what
should
I do.

 

[Ashot Geodakov]

Well, I agree these methods are faster. Especially when it's presumed that
all data in the flat file are valid, types all match, etc.

That's a VERY, VERY roundabout way. The bulk importer is a much, much
better option.

If there is a need to manipulate the data, an easy (and faster) way to
do it in SQL Server would be to have a stored procedure which will create
the temporary table, and then use the bulk importer to import into the
temporary table (you can create a string to execute the bulk importer and
pass it to sp_executesql). Once the data is imported into the temporary
table, you can issue queries to manipulate the data, and then insert it
from the temp table into the destination table.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Open the file as a StreamReader, read it line after line. For each line
read, split it ( string[] arrParams = strLine.Split( new char[] {
'\t' } ) ). Then create a SQL Insert statement with parameters and
execute it using SqlCommand object:

1: SqlCommand cmd = new SqlCommand( "insert into table1( field1, field2 )
values( @field1, @field2 )", connection );
2: cmd.Parameters.Add( new SqlParameter( "@field1", arrParams[0] );
3: cmd.Parameters.Add( new SqlParameter( "@field2", arrParams[1] );
4: cmd.ExecuteNonQuery();

For each next line of text, lines 2 and 3 of the sample will change to:

cmd.Parameters["@field1"].Value = arrParams[0];
cmd.Parameters["@field2"].Value = arrParams[1];

I want to make it user friendily. I want to use window forms and select
files. Is this possible. But after selecting tab delimeted file what
should
I do.

:

Hi,

You can use either Bulk Insert or DTS to import the data directly into
the
SQL.
If you need to make some adjustment to the data you can always read the
file
into your program and then use ADo.NET to insert the data. This variant
is
slower that the direct ways though.

--
Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.

Could you please help me in this project. I have aTab delemeted
file. It
has 20 columns and some data. I have to import this data into SQL
Server.
I
am using sql express edition. I am using Window forms to load the
files It
doesn't have import or export wizard. Do you have any idea how can I
do
that.

 

[Andrew Faust]

You can set options on SqlBulkCopy to make sure all these are checked.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopyoptions.aspx

--
Andrew Faust
andrew[at]andrewfaust.com
http://www.andrewfaust.com

Well, I agree these methods are faster. Especially when it's presumed
that all data in the flat file are valid, types all match, etc.

That's a VERY, VERY roundabout way. The bulk importer is a much,
much better option.

If there is a need to manipulate the data, an easy (and faster) way
to do it in SQL Server would be to have a stored procedure which will
create the temporary table, and then use the bulk importer to import
into the temporary table (you can create a string to execute the bulk
importer and pass it to sp_executesql). Once the data is imported into
the temporary table, you can issue queries to manipulate the data, and
then insert it from the temp table into the destination table.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Open the file as a StreamReader, read it line after line. For each line
read, split it ( string[] arrParams = strLine.Split( new char[] {
'\t' } ) ). Then create a SQL Insert statement with parameters and
execute it using SqlCommand object:

1: SqlCommand cmd = new SqlCommand( "insert into table1( field1,
field2 ) values( @field1, @field2 )", connection );
2: cmd.Parameters.Add( new SqlParameter( "@field1", arrParams[0] );
3: cmd.Parameters.Add( new SqlParameter( "@field2", arrParams[1] );
4: cmd.ExecuteNonQuery();

For each next line of text, lines 2 and 3 of the sample will change to:

cmd.Parameters["@field1"].Value = arrParams[0];
cmd.Parameters["@field2"].Value = arrParams[1];

I want to make it user friendily. I want to use window forms and
select
files. Is this possible. But after selecting tab delimeted file what
should
I do.

:

Hi,

You can use either Bulk Insert or DTS to import the data directly
into the
SQL.
If you need to make some adjustment to the data you can always read
the file
into your program and then use ADo.NET to insert the data. This
variant is
slower that the direct ways though.

--
Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.

Could you please help me in this project. I have aTab delemeted
file. It
has 20 columns and some data. I have to import this data into SQL
Server.
I
am using sql express edition. I am using Window forms to load the
files It
doesn't have import or export wizard. Do you have any idea how can
I do
that.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,


That would be the last resource. Bulk cp is the best way to go.

--
Ignacio Machin
http://www.laceupsolutions.com
Mobile & warehouse Solutions.

Open the file as a StreamReader, read it line after line. For each line
read, split it ( string[] arrParams = strLine.Split( new char[] {
'\t' } ) ). Then create a SQL Insert statement with parameters and execute
it using SqlCommand object:

1: SqlCommand cmd = new SqlCommand( "insert into table1( field1, field2 )
values( @field1, @field2 )", connection );
2: cmd.Parameters.Add( new SqlParameter( "@field1", arrParams[0] );
3: cmd.Parameters.Add( new SqlParameter( "@field2", arrParams[1] );
4: cmd.ExecuteNonQuery();

For each next line of text, lines 2 and 3 of the sample will change to:

cmd.Parameters["@field1"].Value = arrParams[0];
cmd.Parameters["@field2"].Value = arrParams[1];

I want to make it user friendily. I want to use window forms and select
files. Is this possible. But after selecting tab delimeted file what
should
I do.