SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with stored text file in TSQL


Working with stored text file in TSQL

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88586 Visits: 41130
I'm still a bit confused though. You say the user is uploading a file... what is that process? Is the user uploading a file to the Web Server and then your app is loading from that file or ???

I need to understand what the current process and file location is so I can try to come up with a better alternative.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
keith.westberg
keith.westberg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 48
Yes Jeff. We are using asp.net 3.5 to present the users/analysts with the tools they require. The data provider uploads files on the 1st of the month. We use the native upload control for .Net. This control places the file in a predefined folder within the apps folder structure. Once it successfully saves to this folder, we begin walking the file to generate the values required for the sproc. When this walk reaches the end of the file, we close the file and delete it.

Did I cover the details enough? Besides what's mentioned above, there really isn't much to it. We don't use ajax or any other eye candy so the controls and logic are pretty straight forward.

Keith
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88586 Visits: 41130
If that's the case, then you could use SQLBULKCOPY in C# to load the rows into the database in batches much like BCP or BULK INSERT would. That will be MUCH faster than doing an INSERT per row.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
keith.westberg
keith.westberg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 48
Well that looks promising. Let me do some reading and see where it leads. I think I may have looked at that once long ago, but shelved it because i needed to do a row by row validation and this if i recall was a firehouse with the all or nothing approach.

Let me do some google csi and see how things have changed, if at all.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88586 Visits: 41130
keith.westberg (3/25/2014)
Well that looks promising. Let me do some reading and see where it leads. I think I may have looked at that once long ago, but shelved it because i needed to do a row by row validation and this if i recall was a firehouse with the all or nothing approach.

Let me do some google csi and see how things have changed, if at all.


I you need to, you can still do "row-by-row" validations. Load 10 or 50 K rows into an array, validate the data, and bulk it over to the server.

A better way (IMHO) is to bulk all of it over to a staging table on the server and validate it there. Then copy the validated rows to their final resting places and bad data to an errata table for repairs or failure reporting.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8086 Visits: 7163
Gotta' admit, I prefer working in SQL/TSQL, so even for (moderately) large text files I'll often just do an:

OPENROWSET(BULK '<path_and_file_name>', SINGLE_CLOB) --or NCLOB, if req'd

to load the whole file at once into a [n]varchar(max) variable or table column, then process it from there. Maybe that can be of some help in your case as well.

[IIRC, that method doesn't optimize logging, so you might want to use a temp table when possible, or always if the file is very large.]

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
keith.westberg
keith.westberg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 48
@SSCommitted: I would sure love to see an example of what you mentioned. That's exactly what I envisioned but cannot for the life of me find an example of working with an uploaded file directly and walking it via tsql. My oracle friend says its cake, but ever since he made that comment, he went off the grid... =/
keith.westberg
keith.westberg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 48
@ScottPletcher: haha... the above response is for you. That's a dead giveaway that I'm new around here.
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8086 Visits: 7163
Roughly like below. You may have to adjust the "SINGLE_NCLOB" to "SINGLE_CLOB", depending on the format of the input file.



DECLARE @file_text varchar(max)

SELECT @file_text = file_text
FROM OPENROWSET(BULK 'd:\full\path\to\file\filename.txt', SINGLE_NCLOB) AS file_bulk(file_text)
--(Or)
--INSERT INTO dbo.tablename ( file_name, file_text )
--SELECT 'File1' AS file_name, file_text FROM ...

SELECT @file_text




SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
keith.westberg
keith.westberg
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 48
@ScottPletcher: Thank you Scott. I apparently misunderstood you. I assumed you meant that you preferred to generate the loading of records by moving the file to the db and parsing it to produce a flow of insert/updates per line.

That was my original vision. Apparently its not a common thing because I cannot find a single post where if given the following text data, a routine would parse and exec db calls

Payload: testing.txt stored in a nvarchar(max)
cola,colb,colc
a,b,b
s,d,f

SPROC would generate
exec insert into TBL (cola,colb,colc) values (a,b,b);
exec update TBL set cola = a, colb=b, colc=b;
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search