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 2438
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***@discussi ons.microsoft.c omwrote in message
news:CA******** *************** ***********@mic rosoft.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***@discussi ons.microsoft.c omwrote in message
news:CA******** *************** ***********@mic rosoft.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.ExecuteNonQ uery();
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***@discussi ons.microsoft.c omwrote in message
news:B5******** *************** ***********@mic rosoft.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***@discussi ons.microsoft.c omwrote in message news:CA******* *************** ************@mi crosoft.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.co m
"Ashot Geodakov" <a_********@nos pam.hotmail.com wrote in message
news:u3******** ********@TK2MSF TNGP03.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.ExecuteNonQ uery();
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***@discussi ons.microsoft.c omwrote in message
news:B5******** *************** ***********@mic rosoft.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***@discussi ons.microsoft.c omwrote in message news:CA****** *************** *************@m icrosoft.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.c omwrote in
message news:Oe******** ******@TK2MSFTN GP04.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.co m
"Ashot Geodakov" <a_********@nos pam.hotmail.com wrote in message
news:u3******** ********@TK2MSF TNGP03.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.ExecuteNonQ uery();
For each next line of text, lines 2 and 3 of the sample will change to:
cmd.Paramete rs["@field1"].Value = arrParams[0]; cmd.Paramete rs["@field2"].Value = arrParams[1];
"bobby" <bo***@discussi ons.microsoft.c omwrote in message news:B5******* *************** ************@mi crosoft.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***@discussi ons.microsoft.c omwrote in message news:CA***** *************** **************@ microsoft.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_********@nos pam.hotmail.com wrote in message
news:uS******** ******@TK2MSFTN GP02.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.c omwrote
in message news:Oe******** ******@TK2MSFTN GP04.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.co m
"Ashot Geodakov" <a_********@nos pam.hotmail.com wrote in message news:u3******* *********@TK2MS FTNGP03.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.ExecuteNonQ uery();
For each next line of text, lines 2 and 3 of the sample will change to:
cmd.Parameter s["@field1"].Value = arrParams[0]; cmd.Parameter s["@field2"].Value = arrParams[1];
"bobby" <bo***@discussi ons.microsoft.c omwrote in message news:B5****** *************** *************@m icrosoft.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***@discussi ons.microsoft.c omwrote in message news:CA**** *************** *************** @microsoft.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_********@nos pam.hotmail.com wrote in message
news:u3******** ********@TK2MSF TNGP03.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.ExecuteNonQ uery();
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***@discussi ons.microsoft.c omwrote in message
news:B5******** *************** ***********@mic rosoft.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***@discussi ons.microsoft.c omwrote in message news:CA****** *************** *************@m icrosoft.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 into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the...
|
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 SQL Server 2000. Huge
Access db with over 100 user tables, over 60 MB data.
The DTS package that comes with SQL Server 2000 seems pretty "messy"
in the sense that it assumes that one needs to do one time import only
or accurately it does not...
|
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 format. I'm
trying to figure out how to import that into SQL Server 2000 so that
I'll be able to manipulate it in a gui format, rather than command
line. I can't find any import that takes a .sql file. I've been
trying to load it into the query...
|
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 main building using a terminal ap and hit
the server from that desktop.
Anyway, when I import files locally using the Enterprise Manager's DTS
import wizard, the process is incredibly slow. It doesn't matter if
the file is stored on my local...
|
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:
----------------------------------------------------------------------------------------------------------------------------------------------------------
The @ Import Directive
Next to @ Page, the directive that ASP.NET programmers use the most is @
Import. The @ Import directive is ASP.NET's...
| |
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 user has the ability to upload a file (.txt or .csv). The data in the file may be comma delaminated, tab delaminated, fixed width etc (we don't know).
What I'd like to create is something like MS Access uses to import an Excel file into 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 things this way because it
made things easy. Well, I'm the person that has to put up with
the long-term headache.
Anywho, someone at work wants things sped up, so what I'm looking
at doing is this for each client:
|
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 create an automated import function in SQL.
I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?
Using SQL 2005.
|
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 import the
data from that address into an excel worksheet.
What I would like to do is use SSIS to import data from the same web
site. In other words, I now use Excel's web query functionality to
import data from a website with a url of xyz.asp. ...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |