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. 8 2423
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.
"bobby" <bo***@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.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.
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.
"Ignacio Machin ( .NET/ C# MVP )" wrote:
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.
"bobby" <bo***@discussions.microsoft.comwrote in message
news:CA**********************************@microsof t.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.
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];
"bobby" <bo***@discussions.microsoft.comwrote in message
news:B5**********************************@microsof t.com...
>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.
"Ignacio Machin ( .NET/ C# MVP )" wrote:
>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. "bobby" <bo***@discussions.microsoft.comwrote in message news:CA**********************************@microso ft.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.
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]
- mv*@spam.guard.caspershouse.com
"Ashot Geodakov" <a_********@nospam.hotmail.comwrote in message
news:u3****************@TK2MSFTNGP03.phx.gbl...
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];
"bobby" <bo***@discussions.microsoft.comwrote in message
news:B5**********************************@microsof t.com...
>>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.
"Ignacio Machin ( .NET/ C# MVP )" wrote:
>>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. "bobby" <bo***@discussions.microsoft.comwrote in message news:CA**********************************@micros oft.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.
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.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote in
message news:Oe**************@TK2MSFTNGP04.phx.gbl...
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]
- mv*@spam.guard.caspershouse.com
"Ashot Geodakov" <a_********@nospam.hotmail.comwrote in message
news:u3****************@TK2MSFTNGP03.phx.gbl...
>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];
"bobby" <bo***@discussions.microsoft.comwrote in message news:B5**********************************@microso ft.com...
>>>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.
"Ignacio Machin ( .NET/ C# MVP )" wrote:
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. "bobby" <bo***@discussions.microsoft.comwrote in message news:CA**********************************@micro soft.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.
You can set options on SqlBulkCopy to make sure all these are checked. http://msdn2.microsoft.com/en-us/lib...pyoptions.aspx
--
Andrew Faust
andrew[at]andrewfaust.com http://www.andrewfaust.com
"Ashot Geodakov" <a_********@nospam.hotmail.comwrote in message
news:uS**************@TK2MSFTNGP02.phx.gbl...
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.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.comwrote
in message news:Oe**************@TK2MSFTNGP04.phx.gbl...
> 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] - mv*@spam.guard.caspershouse.com
"Ashot Geodakov" <a_********@nospam.hotmail.comwrote in message news:u3****************@TK2MSFTNGP03.phx.gbl...
>>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];
"bobby" <bo***@discussions.microsoft.comwrote in message news:B5**********************************@micros oft.com... 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.
"Ignacio Machin ( .NET/ C# MVP )" wrote:
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. "bobby" <bo***@discussions.microsoft.comwrote in message news:CA**********************************@micr osoft.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.
> > >
Hi,
That would be the last resource. Bulk cp is the best way to go.
--
Ignacio Machin http://www.laceupsolutions.com
Mobile & warehouse Solutions.
"Ashot Geodakov" <a_********@nospam.hotmail.comwrote in message
news:u3****************@TK2MSFTNGP03.phx.gbl...
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];
"bobby" <bo***@discussions.microsoft.comwrote in message
news:B5**********************************@microsof t.com...
>>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.
"Ignacio Machin ( .NET/ C# MVP )" wrote:
>>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. "bobby" <bo***@discussions.microsoft.comwrote in message news:CA**********************************@micros oft.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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Benny |
last post by:
Dear All,
I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing...
|
by: Doug Baroter |
last post by:
Hi,
One of my clients has the following situation. They use Access DB for
data update etc. some business functions while they also want to view
the Access data quickly and more efficiently in...
|
by: jet |
last post by:
Hi,
Maybe this is an easy task, but I'm having a really hard time figuring
out how to do this. I'm a complete newbie to SQL Server.
I have a database dump file from MySQL that's in .sql...
|
by: Ralph Noble |
last post by:
I have two avenues to access my primary SQL Server. (I work remotely
using a VPN connection.)
Usually, I hit the server from my local machine but I also login to a
desktop machine inside the...
|
by: Bruce W. Roeser |
last post by:
All,
I'm reading a book by Charles Petzold (Programming VS.Net). Pretty good
content but am confused about the difference. From the text:
...
|
by: David Berry |
last post by:
Hi All. I'm looking for any help or sample code that can show me how to make a file import wizard in ASP.NET (VB preferred) like the one that MS Access uses. I'm working on a web site where the...
|
by: Randy |
last post by:
Folks:
We have a web-based app that's _really_ slowing down because multiple
clients are writing their own private data into a single, central
database. I guess the previous programmer did...
|
by: mattc66 via SQLMonster.com |
last post by:
I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.
What I would like to do is...
|
by: Eric |
last post by:
In MS Excel, the ability exists to run a "web query." This function
is accessed via the data menu's import external data option. The web
query wizard accepts a URL address, and then is able to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |