?
Solved

ASP.NET - Reading a tab delimited file and update the database table

Posted on 2006-05-19
7
Medium Priority
?
653 Views
Last Modified: 2008-01-09
Hi,

I want to read the data from  tab delimited text file into variables and pass this data as variables to stored procedure to update a sql server database table.
I want this to be done from ASP.NET PAGE, when the user selects the the file to update and clciks on the update button,
the program should read the data from text file and update the table.

Can any one suggests me what is the best way to do this.

Thanks
0
Comment
Question by:tvenkat9
7 Comments
 
LVL 25

Expert Comment

by:nauman_ahmed
ID: 16721441
The following article will be helpfull:

Reading a Delimited File Using ASP.Net and VB.Net
http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/


Reading a Delimited File using ASP.NET and VB.NET
http://www.theukwebdesigncompany.com/articles/article.php?article=151

-Nauman.
0
 
LVL 27

Expert Comment

by:Sammy
ID: 16721479
You can read the textfile and bound the results from reading it to a dropdown list so users can select the option they want to update "Stored procedure" and you can do the databased updating based on their selection
take a look here to see how can a delimited textfile can be read in asp.net

http://www.devarticles.com/c/a/ASP.NET/Reading-a-Delimited-File-Using-ASP.Net-and-VB.Net/

Make sense?

Good luck
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 16721505
if u ll work with SQL server database and with a huge text file , best way to use bulk insert method of SQL Server.
its faster then .NET technology.

if ur situation suits this scene .. i can explain how.

Melih SARICA
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:tvenkat9
ID: 16736783
I want to update the table from Asp.net application, user will not have access directly to the sql server database.
Can we use bulk insert method of SQL Server from web application?
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 16739782
yes .. bulk insert is an TSQL command on SQL server

But csv file must be located on SQL server drives.

Create a tmp table that has same column count with csv file
First load ur data in to a tmp table
check data in tmp table to be sure data is valid.
then copy to the original localtion





0
 

Author Comment

by:tvenkat9
ID: 16753911
Can you give some more details about bulk insert ,
How do I write code for bulk insert , How do I call the this function from asp.net (Can I create stored procedure?).

Thanks
0
 
LVL 19

Accepted Solution

by:
Melih SARICA earned 1500 total points
ID: 16758705
Here is an example of a stored procedure

------------CREATE Proc _sp_UpdateBDTOrders(
      @UserId varchar(35),
      @Dowhat int
--      @Dowhat Values
--      0 = Check Tables
--      1 = Copy Data To temp file
--      2 = delete tmp file
--      3 = Process Data
--      4 =Kontrol Data
)
as
if @Dowhat = 0
begin
      if  object_id('_BDTOrdersTaahhutTmp') is null
            begin
            Create Table _BDTOrdersTaahhutTmp
            (
                  T$Comp int,
                  t$orno int,
                  t$pono int,
                  t$ddat datetime,
                  t$dqua int,
                  Process_time datetime Default getdate(),
                  USER_GUID  varchar(40),
                  Copy_Complete_Time datetime Null)
            create clustered index _BDTOrdersTaahhutTmp_index1  on _BDTOrdersTaahhutTmp (t$comp,t$orno,t$pono)
      end
      if  object_id('_BDTOrdersTaahhutTmp') is null
            begin
                  Create Table _BDTTmp
                  (
                  T$Comp int,
                  t$orno int,
                  t$pono int,
                  t$ddat datetime,
                  t$dqua int
                  )
            end
end
if @Dowhat = 1
begin
       declare @SQLtext varchar(500)
      set @SQLtext = '
      bulk insert _BDTTmp from ''F:\imports\BDTOrders\'+@UserID+'.csv''
            WITH
                        (
                                FIELDTERMINATOR = '';'',
                                ROWTERMINATOR = ''\n'',
                         FIRSTROW  = 2
                        )'

      exec (@SQLtext)
      select 0 Stat
end
if @Dowhat = 2
begin
   delete from       _BDTTmp
end
if @dowhat = 3
begin
      insert into _BDTOrdersTaahhutTmp(T$Comp,t$orno,t$pono ,t$ddat ,t$dqua,USER_GUID)
             select *,@UserID from _BDTTmp

      drop table _BDTTmp

      update ecatalog.dbo.__Bakiye  
            set ecatalog.dbo.__Bakiye.T$ddat = ecatalog.dbo._BDTOrdersTaahhutTmp.t$ddat,
                  ecatalog.dbo.__Bakiye.t$dqua = ecatalog.dbo._BDTOrdersTaahhutTmp.t$dqua
            from ecatalog.dbo.__Bakiye  inner join      ecatalog.dbo._BDTOrdersTaahhutTmp
                  on ecatalog.dbo._BDTOrdersTaahhutTmp.t$comp = ecatalog.dbo.__Bakiye.t$comp and
                        ecatalog.dbo._BDTOrdersTaahhutTmp.t$orno = ecatalog.dbo.__Bakiye.t$orno and
                        ecatalog.dbo._BDTOrdersTaahhutTmp.t$pono = ecatalog.dbo.__Bakiye.t$pono
      Where ecatalog.dbo._BDTOrdersTaahhutTmp.User_GUID = @USERID
            and  ecatalog.dbo._BDTOrdersTaahhutTmp.Copy_Complete_Time  is null

      update _BDTOrdersTaahhutTmp set Copy_Complete_Time = getdate()
            where USER_GUID= @USerId and Copy_Complete_Time is null
end
if @dowhat =4
begin
      Select  _BDTTmp.* ,ecatalog.dbo.__Bakiye.t$ITEM,ecatalog.dbo.__Bakiye.T$CUNO from _BDTTmp left  join ecatalog.dbo.__Bakiye
                  on ecatalog.dbo._BDTTmp.t$comp = ecatalog.dbo.__Bakiye.t$comp and
                        ecatalog.dbo._BDTTmp.t$orno = ecatalog.dbo.__Bakiye.t$orno and
                        ecatalog.dbo._BDTTmp.t$pono = ecatalog.dbo.__Bakiye.t$pono
end
GO
-----------

Melih SARICA
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question