Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Working with stored text file in TSQL Expand / Collapse
Author
Message
Posted Thursday, March 20, 2014 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:14 AM
Points: 11, Visits: 44
I'm looking for a way to look into a field containing a text file and work with the content line by line. Our current environment will not allow file/OS access so the the only way i can reach this text file is to have it exist in a varchar(max) or whichever data type suggested.

Thank you in advance for any advise...

Keith
Post #1553245
Posted Thursday, March 20, 2014 8:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 37,098, Visits: 31,648
keith.westberg (3/20/2014)
...a field containing a text file and work with the content line by line


What's actually stored in the "field"? The path to the text file or the actual content from the text file?


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1553301
Posted Friday, March 21, 2014 11:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:14 AM
Points: 11, Visits: 44
The text file itself.
Post #1553621
Posted Friday, March 21, 2014 12:52 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:40 PM
Points: 3,931, Visits: 7,159
In binary form or the actual character string of contents from within the text file?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1553640
Posted Friday, March 21, 2014 1:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:20 AM
Points: 12,916, Visits: 32,080
a lot more information as far as details would help, but
if the data is important line by line, then for me, each line should be stored as a row/column in a table.

it sounds like you want to duplicate an existing process, but do it in SQL instead.

so the right thing to do would be to import each line as a row of data, and chop that data up into relevant columns; that way you can better search and manipulate the data itself.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1553642
Posted Friday, March 21, 2014 1:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:14 AM
Points: 11, Visits: 44
I apologize for not laying the groundwork... so here's the process.

User uploads a file to website application. The site parses the text file and generates insert parameters for the called sproc on sql server 2008 r2. This process is repeated for every line found in text file besides the header line which is parsed for appropriate columns. Before the call is made to the db, the business logic scrubs the content to assure data quality and format. Now some text files can be 100k lines, in which case this app makes 100k db calls.

My search is for a way for the user to simply upload the file to the db directly and kick off a process which would take place only on the db that walks the file line by line. Our users are NOT granted access to an area to drop files off for bulk loading. And the server admins will NOT provide a location for the db to reach out to use file system for its data source.

The only option i can see with these limitations, is to allow the user to push the entire file to the database and work the process from there. Granted it would not be the sexiest thing on the block... but its worth a run.

Any tips or sources for like process would be amazing... thank you
Post #1553656
Posted Friday, March 21, 2014 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:14 AM
Points: 11, Visits: 44
@MyDoggieJessie: text, not binary.

Post #1553657
Posted Friday, March 21, 2014 1:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:14 AM
Points: 11, Visits: 44
@MyDoggieJessie: Sorry -- it would be stored as actual character string if that's the way forward
Post #1553660
Posted Saturday, March 22, 2014 9:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:34 PM
Points: 37,098, Visits: 31,648
keith.westberg (3/21/2014)
I apologize for not laying the groundwork... so here's the process.

User uploads a file to website application. The site parses the text file and generates insert parameters for the called sproc on sql server 2008 r2. This process is repeated for every line found in text file besides the header line which is parsed for appropriate columns. Before the call is made to the db, the business logic scrubs the content to assure data quality and format. Now some text files can be 100k lines, in which case this app makes 100k db calls.

My search is for a way for the user to simply upload the file to the db directly and kick off a process which would take place only on the db that walks the file line by line. Our users are NOT granted access to an area to drop files off for bulk loading. And the server admins will NOT provide a location for the db to reach out to use file system for its data source.

The only option i can see with these limitations, is to allow the user to push the entire file to the database and work the process from there. Granted it would not be the sexiest thing on the block... but its worth a run.

Any tips or sources for like process would be amazing... thank you


This won't help except, maybe, to know that you have someone that agrees with you but I just have to say that I'm totally amazed at some DBAs/Server Admins . Yes, DBAs/Server Admins absolutely need to be extremely concerned about security, size, and safety but DBAs/Server Admins aren't supposed to be roadblocks. They're (we're) supposed to be enablers of safely doing things the right way and uploading a file and then bulk loading it is absolutely the correct method for doing this rather than sending a 100k row file across the pipe one bloody row at a time. The system(s) will pay dearly when just a handful of users try to upload such files in the RBAR fashion that you've been constrained to. The web Server is going to take a beating, as well.

There's absolutely no reason why an uploadable FTPS or similar site couldn't be constructed and offered to the users with logins and passwords that would preclude users from seeing each other's files.

Maybe there are some mitigating factors that I'm not aware of but, from here, it seems that the DBAs/Server Admins have erected a totally unjustifiable roadblock for what appears to be an important process. I don't know if it's arrogance, ignorance, or slothfulness that has cause them to do this to you but they need to realize that you're all working for the same company and when it comes to handling data, they need to be enablers instead of nay sayers because they're the ones that know how to do this in a secure fashion and with performance in mind.

I apologize for the rant but this kind of stuff just bugs the hell out of me.

Shifting gears to the problem at hand, you said...

User uploads a file to website application.



What is the process for that happening? Is the file loaded onto the Website box itself and then the app reads from that file or ???


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1553788
Posted Sunday, March 23, 2014 12:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 19, 2014 10:14 AM
Points: 11, Visits: 44
Thank you Jeff... and I hear you. I wish this was the first time a "we don't do that here" has bit me.

And again, I failed to give total clarity over the environment. This data load is performed by a single user and is done on the first of the month. So you are absolutely right on the "oh my god, how many calls will this app make" statement. The app is more of an analyst tool for comparing dates and values over time. The analyst(s) have been, for as long as i know, working other tasks involving other systems during the 1st to the 3rd, so I know for sure this process has been in place for at least 4 years.

I can't explain in detail why the lock down, but it does exist and it is a hurdle I have been wanting to jump since i arrived. The process I mentioned is working, but with some adjustments. For instance we had to limit the file size to 30mg in order to avoid page timeouts.

I have scoured the web looking for examples, articles, tips/tricks... and I get nothing that even come close to walking a varchar and working with the content from top to bottom. I even used my trusty expert exchange that I use when I'm really stuck, and even that left me empty.

Is it not possible?
Post #1553796
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse