Importing text file

HI and TIA. I posted this to a vs.net NG but after looking thought it might
fit in here better.
Using VS.Net 2003 I built an ASP.Net Web application using
VB.Net. I connect to an SQL db(SQL Server 2000) and am setup to
write/read/edit data no problem. I'm having a problems with the following
scenario. Any help, advice, or if you could point me in the right direction
is highly appreciated.
1. The user clicks a button which opens the Find file dialogue box.
2. They navigate to the text(or possible dbf file) and click ok.
3. The a temp table is created and the text(dbf) file is imported
creating the temp table
4. A procedure is run the takes my active data(an existing table) and
archives(appends) that data to an archive table. Before this though the
data in the archive table needs to be deleted.
5. The data from my live table is deleted.
6. Data from my imported temp table is appended to the live table.
7. Temp table is deleted.
I read some stuff about DTS packages but I'm not real familiar with SQL
Server and don't know how exactly to call the DTS package from my
application and/or if DTS is actually the way to go. The text file is
delimited and contains field names that will map directly to my existing
tables. Not looking for a hand out so if you could get me pointed in the
right direction I'm not against doing more research, but at this time I'm
not exactly sure where to start. Thanks again for your time.Hi
The following web site has lots of information on DTS. There is also alot of
information in Books online (which can be downloaded from
http://www.microsoft.com/downloads/...&displaylang=en
if you don't have it!). You may also want to look up BCP or Bulk Insert as a
alternative method.
John
"Reggie" wrote:

> HI and TIA. I posted this to a vs.net NG but after looking thought it mig
ht
> fit in here better.
> Using VS.Net 2003 I built an ASP.Net Web application using
> VB.Net. I connect to an SQL db(SQL Server 2000) and am setup to
> write/read/edit data no problem. I'm having a problems with the following
> scenario. Any help, advice, or if you could point me in the right directi
on
> is highly appreciated.
> 1. The user clicks a button which opens the Find file dialogue box.
> 2. They navigate to the text(or possible dbf file) and click ok.
> 3. The a temp table is created and the text(dbf) file is imported
> creating the temp table
> 4. A procedure is run the takes my active data(an existing table) and
> archives(appends) that data to an archive table. Before this though the
> data in the archive table needs to be deleted.
> 5. The data from my live table is deleted.
> 6. Data from my imported temp table is appended to the live table.
> 7. Temp table is deleted.
> I read some stuff about DTS packages but I'm not real familiar with SQL
> Server and don't know how exactly to call the DTS package from my
> application and/or if DTS is actually the way to go. The text file is
> delimited and contains field names that will map directly to my existing
> tables. Not looking for a hand out so if you could get me pointed in the
> right direction I'm not against doing more research, but at this time I'm
> not exactly sure where to start. Thanks again for your time.
>
>|||John, Thanks very much for you quick response. I will look BOL some more.
You mention a website with lots of info but forgot the link. Take care!
Reggie
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:4097B1F5-B1F6-434F-A14B-593AC227135F@.microsoft.com...
> Hi
> The following web site has lots of information on DTS. There is also alot
> of
> information in Books online (which can be downloaded from
> http://www.microsoft.com/downloads/...&displaylang=en
> if you don't have it!). You may also want to look up BCP or Bulk Insert as
> a
> alternative method.
> John
> "Reggie" wrote:
>|||Hi
Sorry anbout the missing link! http://www.sqldts.com/default.aspx?6
If you don't want the user to wait around for the file to load then you can
schedule your DTS package to run at intervals. Look at the looping example o
n
how to load all files in a given directory.
If you want to load immediately and know the destination file name, bulk
insert may also be an option. You may need use UNC addresses to get/put the
file into an accessible location.
John
"Reggie" wrote:

> John, Thanks very much for you quick response. I will look BOL some more
.
> You mention a website with lots of info but forgot the link. Take care!
> Reggie
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:4097B1F5-B1F6-434F-A14B-593AC227135F@.microsoft.com...
>
>

importing excel file into sql server 2005 in asp .net using c#

Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me?

rgds,
RK

try the link below [SqlBulkCopy]

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Just a little bit of code transfers the data from the Excel Spreadsheet into the SQL Server Database Table:

 
// Connection String to Excel Workbookstring excelConnectionString = @."Provider=Microsoft .Jet.OLEDB.4.0;Data Source=Book1.xls;Extended Properties=""Excel 8.0;HDR=YES;""";// Create Connection to Excel Workbookusing (OleDbConnection connection =new OleDbConnection(excelConnectionString)){ OleDbCommand command =new OleDbCommand ("Select ID,Data FROM [Data$]", connection); connection.Open();// Create DbDataReader to Data Worksheetusing (DbDataReader dr = command.ExecuteReader()) {// SQL Server Connection Stringstring sqlConnectionString ="Data Source=.; Initial Catalog=Test;Integrated Security=True";// Bulk Copy to SQL Serverusing (SqlBulkCopy bulkCopy =new SqlBulkCopy(sqlConnectionString)) { bulkCopy.DestinationTableName ="ExcelData"; bulkCopy.WriteToServer(dr); } }}
 

Good Luck./.

|||

Hi

you have to first read the XL content then you have to insert into Sql server.

you can transfer your XL to dataset by Oledb provider by this code

string conection_string ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=YOUR XL PATH; Extended Properties='Excel 8.0;IMEX=1;HDR=YES;'";

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

DataSet ds =newDataSet();

adp.Fill(ds);

now your xl content would be in dataset ds , then you can access this dataset as well and read dataset line by line and insert into Sql

add namespace :using System.Data.OleDb;

|||

Dear what is Sheet1$ can u tell me detail?

|||

ramu778:

Dear what is Sheet1$ can u tell me detail?

sorry to interrupt

in ConnectionString; for DataSource we give the XLS file path...

and in below code line... we have to give the Sheet name in the Select Query from which we have to read the date from, as there would be more than one Sheet in an excel file...

OleDbDataAdapter adp =newOleDbDataAdapter("SELECT * FROM [Sheet1$] ", conection_string);

hope it clears./.

|||

Hi

sheet1 is nothing but the name of the XL sheet , there are some name at bottom of XL like sheet1,sheet2 and so, we have to give that name

Importing Data Throught ASP

Hello,
I've been trying to work with code to import data from a "flat file" (.csv)
into a SQL 2000 table.
I'm not that well versed in SQL and was wondering is it was possible using
OPENROWSET or something similar?
I've tried using OPENROWSET and encountered the error "Ad hoc access to OLE
DB provider 'MSDASQL' has been denied. You must access this provider through
a linked server."
Any help or resources to explain the process would be fantastic.
-SteveIf you're trying to upload through an ASP page you don't want to use
OPENROWSET. That's really for accessing OLE DB data sources. For an example
of uploading a file via ASP check out:
http://msdn.microsoft.com/library/d...br />
0900.asp
Mike O.
"Anubis" <anubis2003@.bluestreem.com.au> wrote in message
news:ucZJcoO1DHA.208@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Hello,
> I've been trying to work with code to import data from a "flat file"

---

(.csv)

quote:

---

> into a SQL 2000 table.
> I'm not that well versed in SQL and was wondering is it was possible using
> OPENROWSET or something similar?
> I've tried using OPENROWSET and encountered the error "Ad hoc access to

---

OLE

quote:

---

> DB provider 'MSDASQL' has been denied. You must access this provider

---

through

quote:

---

> a linked server."
> Any help or resources to explain the process would be fantastic.
> -Steve
>

---

|||Hi Mike,
No, actually I wanted to upload data from a flat file (.csv) into a SQL
Database like to following
Flat File (x.csv) contains:
Col1, Col2, Col3
Value 0, Value1, Value 2,
Value 0, Value1, Value 2
Then do a command something like the following:
INSERT INTO dbo.table
SELECT Col1, Col2, Col3
FROM x.csv
Basically I want to open a rowset to insert into a SQL table but I cannot
open the rowset from the .csv source through an ASP command.
Any idea's?
Thanks
-Steve
"Michael Otey" <mikeo@.teca.com> wrote in message
news:eEOx90a1DHA.2336@.TK2MSFTNGP09.phx.gbl...

quote:

---

> If you're trying to upload through an ASP page you don't want to use
> OPENROWSET. That's really for accessing OLE DB data sources. For an

---

example

quote:

---

> of uploading a file via ASP check out:
>

---

http://msdn.microsoft.com/library/d...tml/asp0900.asp

quote:

---

[
color=darkred]
> Mike O.
>
> "Anubis" <anubis2003@.bluestreem.com.au> wrote in message
> news:ucZJcoO1DHA.208@.TK2MSFTNGP12.phx.gbl...
> (.csv)
using[QUOTE]
> OLE
> through
>

---

|||If you're running this on the SQL Server system and just want to import the
test file then probably the simplest thing is to use the BULK INSERT
statement something like the following:
BULK INSERT mydb..mytable FROM 'c:\mycsvfile.csv'
WITH (DATAFILETYPE = 'char',FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
Mike O.
"Anubis" <anubis2003@.bluestreem.com.au> wrote in message
news:%23eXL8Kb1DHA.2396@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hi Mike,
> No, actually I wanted to upload data from a flat file (.csv) into a SQL
> Database like to following
>
> Flat File (x.csv) contains:
> Col1, Col2, Col3
> Value 0, Value1, Value 2,
> Value 0, Value1, Value 2
> Then do a command something like the following:
> INSERT INTO dbo.table
> SELECT Col1, Col2, Col3
> FROM x.csv
> Basically I want to open a rowset to insert into a SQL table but I cannot
> open the rowset from the .csv source through an ASP command.
> Any idea's?
> Thanks
> -Steve
>
> "Michael Otey" <mikeo@.teca.com> wrote in message
> news:eEOx90a1DHA.2336@.TK2MSFTNGP09.phx.gbl...
> example
>

---

http://msdn.microsoft.com/library/d...tml/asp0900.asp

quote:

---

[
color=darkred]
> using
to[QUOTE]
>

---

Importing big data with exception.

Hi, there;

I use ASP.NEP to create a SSIS package to import data from .dbf file. When I import data from a big file (216,173KB) my package throw exception:

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available.
".
The "input "OLE DB Destination Input" (71)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (71)" specifies failure on error. An error occurred on the specified object of the specified component.
The ProcessInput method on component "OLE DB Destination" (58) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "WorkThread0" has exited with error code 0xC0209029.
Thread "SourceThread0" has exited with error code 0xC0047038.

An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: The specified network name is no longer available.
".
The "input "OLE DB Destination Input" (77)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (77)" specifies failure on error. An error occurred on the specified object of the specified component.
The ProcessInput method on component "OLE DB Destination" (64) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Thread "WorkThread0" has exited with error code 0xC0209029.
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "SourceThread0" has exited with error code 0xC0047038

Does anybody know what the problem is?

Thanks.

Sounds like the connection to your destination database timed out and was closed. Most likely the SQL Server instance severed the connection. Are you performing a large sort or an aggregate in the dataflow? You might first try going to a raw file instead of the database table and then create a second data flow that uses the raw file as the source straight into the database table.|||Thanks.

My structure is: my data source files (*.dbf file)are under another machine, some files are very big (216,173KB, 283,845KB, some are even bigger as time goes on). I want to import these data to my new SQL2005 server which is empty now. I create a table before importing data (Works now). In my source component I set " oSrcInstance.SetComponentProperty("SqlCommand", "SELECT * FROM [" + sourceTable+"]");", there is no other aggregate operation between source and destination, just simply pump all data from source to destination. I can do this with my SQL2000 DTS (same as a ASP.NEP project).
So simple as Source (*.dbf)--> Destination (SQL2005).

Thanks.
|||

Are you creating the new table through an Exec SQL task? Is it using the same connection manager as your data flow task? Trying to verify that the package is connecting successfully. Also, how long is the package running before you received this error? Is it occuring if you try a smaller file?

You might try setting the OLE DB Destination data access mode property to one of the fast load options, and specify Rows Per Batch as 10,000 or 20,000, to see if you get different results.

|||

No, I don't use ExecSQL task to create table, I just use SqlConnection to create table and then close+dispose the connection.

Actually I am runnning the package now. It looks like it is running slower and slower. I can see the row numbers increaing in the SQL2005 database.

I am using OLE DB Destination,

oDestInstace.SetComponentProperty("CommandTimeout", 0);

oDestInstace.SetComponentProperty("OpenRowset", "[dbo].[" + destinationTable+"]");

oDestInstace.SetComponentProperty("OpenRowsetVariable", null);

// oDestInstace.SetComponentProperty("SqlCommand", null);

oDestInstace.SetComponentProperty("DefaultCodePage", 1252);

oDestInstace.SetComponentProperty("AlwaysUseDefaultCodePage", false);

oDestInstace.SetComponentProperty("AccessMode", 0);

oDestInstace.SetComponentProperty("FastLoadKeepIdentity", false);

oDestInstace.SetComponentProperty("FastLoadKeepNulls", false);

// oDestInstace.SetComponentProperty("FastLoadOptions", null);

oDestInstace.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);

The exception happens after package runs for 30 minutes or more. It doesn't occur for small files.

Thanks

|||

Try setting FastLoadOptions to TABLOCK, CHECK_CONSTRAINTS, ROWS_PER_BATCH=1000

and FastLoadMaxInsertCommitSize to 1000.

|||

Yes, you are right. I did that and it works perfectly. Apart from FastLoadMaxInsertCommitSize = 0.

Thanks.

importing an excel file and save data into a database in my web account

Hi everyone, sorry if this message is not supposed to be posted here.

I'm learning asp.net , and would like to know how I can insert data from a excel file into a database on my web account. Pretty much insert/update information in the database using excel file or a access file.

Thanks a lot in advance

Checkout thses links:

http://support.microsoft.com/kb/311731

http://www.msdner.com/forum/thread302753.html

http://support.microsoft.com/kb/316934

http://www.vbdotnetheaven.com/UploadFile/mahesh/AccessExcelDatabase04252005014321AM/AccessExcelDatabase.aspx

I hope they will help.

Good luck.

|||

Thanks a lot. I found an interesting article that talks about this. again thanks for your assistance

|||

If my post was helpful for your then please mark it as answer.

If not, please share the answer with us in a post and mark it as answer.

Remeber we have to cooperate to help each other.

Thanks and you are welcome.