March 25, 2014 at 4:05 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 25, 2014 at 7:05 pm
@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... =/
March 25, 2014 at 7:08 pm
@ScottPletcher: haha... the above response is for you. That's a dead giveaway that I'm new around here.
March 26, 2014 at 9:10 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 26, 2014 at 10:24 am
@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;
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply