Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / Languages / C# / November 2007

Tip: Looking for answers? Try searching our database.

import this data into SQL Server

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
bobby - 07 Nov 2007 19:17 GMT
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] - 07 Nov 2007 19:23 GMT
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

Signature

         - Nicholas Paldino [.NET/C# MVP]
         - mvp@spam.guard.caspershouse.com

> 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 ) - 07 Nov 2007 19:30 GMT
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.

Signature

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

>
> Could you please help me in this project.  I have aTab delemeted file.  It
[quoted text clipped - 3 lines]
> doesn't have import or export wizard.  Do you have any idea how can I do
> that.
bobby - 07 Nov 2007 20:17 GMT
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,
>
[quoted text clipped - 10 lines]
> > doesn't have import or export wizard.  Do you have any idea how can I do
> > that.
Ashot Geodakov - 07 Nov 2007 21:30 GMT
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
[quoted text clipped - 22 lines]
>> > do
>> > that.
Nicholas Paldino [.NET/C# MVP] - 07 Nov 2007 22:23 GMT
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.

Signature

         - Nicholas Paldino [.NET/C# MVP]
         - mvp@spam.guard.caspershouse.com

> Open the file as a StreamReader, read it line after line. For each line
> read, split it ( string[] arrParams = strLine.Split( new char[] {
[quoted text clipped - 38 lines]
>>> > do
>>> > that.
Ashot Geodakov - 07 Nov 2007 23:15 GMT
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.
[quoted text clipped - 49 lines]
>>>> > do
>>>> > that.
Andrew Faust - 08 Nov 2007 02:04 GMT
You can set options on SqlBulkCopy to make sure all these are checked.

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


Signature

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.
[quoted text clipped - 53 lines]
>>>>> > I do
>>>>> > that.
Ignacio Machin ( .NET/ C# MVP ) - 08 Nov 2007 16:35 GMT
Hi,

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

Signature

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[] {
[quoted text clipped - 38 lines]
>>> > do
>>> > that.

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.