retrieve data from a static text file at an http address
-
I need to retrieve the data in a delimited text file that is written to public http address such as http://www.example.com/filename.txt . I need to create this connection in a DTS package to pull the data in to a db table as the text file is updated daily. This is running on SQL Server 2000.
Right now I am saving the file to my C drive in .xls formate and the package picks up the file from there and imports to db table.
Any pointers?
Thanks
-
Not too sure of the specific coding, but I think the best bet would be to have an ActiveScript task that performs a HTTP GET and saves the file locally. From there it's a simple datapump to get the data into the database.
--------------------
Colt 45 - the original point and click interface
-
Something like:
Dim objSvrHTTP
Dim fso
Dim f
Set objSvrHTTP = CreateObject("Msxml2.ServerXMLHTTP.4.0")
objSvrHTTP.open "GET", "http://www.example.com/filename.txt", false
objSvrHTTP.send
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.createtextfile("c:\mylocalfile.txt")
f.Write objSvrHTTP.responseText
Set f = Nothing
Set fso = Nothing
Set objSvrHTTP = Nothing
...in activeX should do the trick.
-
OK that should copy the file to my c: drive. Maybe this is simplistic but how would I import directly from http file to table in my db?
Sorry I was not more clear on what I was hoping to do.
Thanks
Will
-
In the DTS package, after the ActiveScript task have a Text Source connection then have a SQL Server connection and put a datapump task between the two connections.
--------------------
Colt 45 - the original point and click interface -
I use this for a job that i have it get a file form a web site and put it into a network location.
This is an active x script
Function Main()
Dim HTTP
Dim Stream
Const adTypeBinary = 1
Const adSaveCreateNotExist = 1
Const adSaveCreateOverWrite = 2
strSource = "http://www. etc.... "
strDest = "location to put file"
Set Stream = CreateObject("adodb.stream")
Set HTTP = CreateObject("Microsoft.XMLHTTP")
HTTP.open "GET", strSource, False
HTTP.send
Stream.type = adTypeBinary
Stream.open
Stream.write HTTP.responseBody
Stream.savetofile strDest, adSaveCreateOverWrite
Set Stream = nothing
Set HTTP = nothing
Main = DTSTaskExecResult_Success
End Function
-
the file is a .csv
Thanks for any help
-
Were you able to setup a datapump to import the file?
--------------------
Colt 45 - the original point and click interface -
I have not. I need to go directly from HTTP file to a db table without using my local C: drive.
Useless I have misunderstood, every thing we have talked about so far must place the file on my local drive then use a text file connection to import data into db. While this would save me from manually bringing the file to my local drive it still is not what I hoped to accomplish.
If I have misunderstood please set me straight.
Thanks
Will
-
Are you able to access the file via a shared folder? If so, then the regular datapump will work fine.
If not, and you definitely can't save the file locally (even in a temp location), then I'd say you're stuck with loading the file in line by line in VBScript.
To do that, you'll need to open an ADO connection to the database. Parse the response text into an array of some sort. Pull the first record from the array. Dynamically build an insert statement from that record and execute it on the ADO connection.
As you can see a lot more involved, I'd be pushing to get some sort of access to the local file system.
--------------------
Colt 45 - the original point and click interface -
Now this hasn't been fully tested ( and thanks to MIT for an accessible CSV file on the net!). The Stored Procedure below retrieves a CSV from the web and converts it into XML format stored in a table. The example execution below that, has the script that needs to be customised for your individual file and table needs (makes a good trial run too!).
IF
EXISTS (SELECT 1 FROM sysobjects WHERE name = 'uspCsvToXmlConverter' AND xtype = 'P')
DROP PROCEDURE uspCsvToXmlConverter
GO
CREATE
PROCEDURE uspCsvToXmlConverter(
@url VARCHAR
(1024),
@hasHeader
BIT,
@separator
CHAR(1)
)
AS
SET
NOCOUNT ON
-- For HTML object
DECLARE
@object INT
-- For Line and Character location
DECLARE
@thisChar INT
DECLARE
@nextChar INT
DECLARE
@whereChar INT
DECLARE
@varNumber INT
DECLARE
@varLoop INT
DECLARE
@updateText VARCHAR(8000)
DECLARE
@updateIndex INT
-- For data input / output
CREATE
TABLE #headerTable (headerText TEXT)
CREATE
TABLE #dataTable (dataText TEXT)
CREATE
TABLE #xmlTemplateTable (templateText TEXT)
CREATE
TABLE #xmlCompleteTable (completeText TEXT, isProcessed BIT)
DECLARE
@headerData VARCHAR(8000)
DECLARE
@xmlData VARCHAR(8000)
DECLARE
@xmlDataTemplate VARCHAR(8000)
DECLARE
@xmlDataFilled VARCHAR(8000)
-- For text operations
DECLARE
@ptrval binary(16)
DECLARE
@datlen INT
-- Input Table
CREATE
TABLE #htmlTable (htmlText TEXT)
--- Create HTTP object and retrieve responseText
EXEC
sp_OACreate 'MSXML2.ServerXMLHTTP', @object OUTPUT
EXEC
sp_OAMethod @object, 'open', NULL, 'GET', @url, FALSE
EXEC
sp_OAMethod @object, 'send'
INSERT
INTO #htmlTable
EXEC
sp_OAGetProperty @object, 'responseText'
EXEC
sp_OADestroy @object
SET
@thisChar = 1
SET
@nextChar = 1
SET
@varNumber = 10
-- Process header data in to template table
IF
@hasHeader = 1
BEGIN
INSERT INTO #xmlTemplateTable SELECT '<rowdata'
INSERT INTO #headerTable SELECT SUBSTRING(htmlText, 1, PATINDEX('%'+CHAR(10)+'%', htmlText)) FROM #htmlTable
WHILE (SELECT DATALENGTH(headerText) FROM #headerTable) > 0
BEGIN
SELECT @nextChar = PATINDEX('%'+@separator+'%', headerText) FROM #headerTable
IF @nextChar = 0 SELECT @nextChar = DATALENGTH(headerText) FROM #headerTable
SELECT @updateText = ' ' + REPLACE(SUBSTRING(headerText, @thisChar, @nextChar - @thisChar), ' ', '_') + '="' FROM #headerTable
SELECT @datlen = DATALENGTH(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
SELECT @ptrval = TEXTPTR(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
UPDATETEXT #xmlTemplateTable.templateText @ptrval @datlen 0 @updateText
SELECT @updateText = 'value_' + CAST(@varNumber AS VARCHAR)
SELECT @datlen = DATALENGTH(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
SELECT @ptrval = TEXTPTR(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
UPDATETEXT #xmlTemplateTable.templateText @ptrval @datlen 0 @updateText
SELECT @updateText = '"'
SELECT @datlen = DATALENGTH(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
SELECT @ptrval = TEXTPTR(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
UPDATETEXT #xmlTemplateTable.templateText @ptrval @datlen 0 @updateText
-- Remove processed data from header table
SELECT @ptrval = TEXTPTR(headerText) FROM #headerTable
UPDATETEXT #headerTable.headerText @ptrval 0 @nextChar
-- Remove processed data from input table
SELECT @ptrval = TEXTPTR(htmlText) FROM #htmlTable
UPDATETEXT #htmlTable.htmlText @ptrval 0 @nextChar
SELECT @thisChar = 1
SELECT @varNumber = @varNumber + 1
END
SELECT @updateText = '/>'
SELECT @datlen = DATALENGTH(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
SELECT @ptrval = TEXTPTR(templateText) FROM #xmlTemplateTable WHERE templateText IS NOT NULL
UPDATETEXT #xmlTemplateTable.templateText @ptrval @datlen 0 @updateText
END
SELECT
@varNumber = @varNumber -1
WHILE
(SELECT DATALENGTH(htmlText) FROM #htmlTable) > 0
BEGIN
SET @varLoop = 10
SET @thisChar = 1
INSERT INTO #dataTable SELECT REPLACE(SUBSTRING(htmlText, 1, PATINDEX('%'+CHAR(10)+'%', htmlText)) + ',', CHAR(10), '') FROM #htmlTable
-- Insert Template to Xml table
INSERT INTO #xmlCompleteTable SELECT templateText, 0 FROM #xmlTemplateTable
WHILE @varLoop <= @varNumber
BEGIN
SELECT @nextChar = PATINDEX('%'+@separator+'%', dataText) FROM #dataTable
SELECT @updateIndex = PATINDEX('%value_' + CAST(@varLoop AS VARCHAR) + '%', completeText) - 1 FROM #xmlCompleteTable
SELECT @updateText = SUBSTRING(dataText, @thisChar, @nextChar - @thisChar) FROM #dataTable
SELECT @datlen = DATALENGTH(completeText) FROM #xmlCompleteTable WHERE isProcessed = 0
SELECT @ptrval = TEXTPTR(completeText) FROM #xmlCompleteTable WHERE isProcessed = 0
UPDATETEXT #xmlCompleteTable.completeText @ptrval @updateIndex 8 @updateText
SELECT @thisChar = 1
SELECT @varLoop = @varLoop + 1
-- Remove processed data from header table
SELECT @ptrval = TEXTPTR(dataText) FROM #dataTable
UPDATETEXT #dataTable.dataText @ptrval 0 @nextChar
-- Remove processed data from input table
SELECT @ptrval = TEXTPTR(htmlText) FROM #htmlTable
UPDATETEXT #htmlTable.htmlText @ptrval 0 @nextChar
END
TRUNCATE TABLE #dataTable
END
INSERT
INTO #xmlTable SELECT completeText FROM #xmlCompleteTable
DROP
TABLE #htmlTable
DROP
TABLE #headerTable
DROP
TABLE #dataTable
DROP
TABLE #xmlTemplateTable
DROP
TABLE #xmlCompleteTable
GO
/* End of script procedure */
/* Example Execution */
DECLARE
@url VARCHAR(1024)
DECLARE
@hasHeader BIT
DECLARE
@separator CHAR(1)
DECLARE
@xmlLine VARCHAR(8000)
DECLARE
@iDoc INT
SET
@hasHeader = 1 -- At the moment, the file must have a header line for sp to work
SET
@url = 'http://web.mit.edu/14.33/www/attach/cephs.csv' -- File location
SET
@separator = ',' -- Character separator
-- Need to use temp table not table variable for Text Pointer in sp
CREATE
TABLE #xmlTable (xmlText TEXT)
EXEC
uspCsvToXmlConverter @url, @hasHeader, @separator
-- This is the bit that you need to customise for your own file/table combination
CREATE
TABLE #outputTable (date SMALLINT, year INT, month SMALLINT, p1_drug_b VARCHAR(100), r1_drug_b VARCHAR(100), p1_hosp_b VARCHAR(100), r1_hosp_b VARCHAR(100), p2_drug_b VARCHAR(100),
r2_drug_b VARCHAR
(100), p2_hosp_b VARCHAR(100), r2_hosp_b VARCHAR(100), p4_drug_b VARCHAR(100), r4_drug_b VARCHAR(100), p4_hosp_b VARCHAR(100), r4_hosp_b VARCHAR(100),
p5_drug_b VARCHAR
(100), r5_drug_b VARCHAR(100), p5_hosp_b VARCHAR(100), r5_hosp_b VARCHAR(100), p7_drug_b VARCHAR(100), r7_drug_b VARCHAR(100), p7_drug_g VARCHAR(100),
r7_drug_g VARCHAR
(100), p7_hosp_b VARCHAR(100), r7_hosp_b VARCHAR(100), p7_hosp_g VARCHAR(100), r7_hosp_g VARCHAR(100)
)
DECLARE
csr_xml CURSOR FOR
SELECT
xmlText FROM #xmlTable
OPEN
csr_xml
FETCH
NEXT FROM csr_xml INTO @xmlLine
WHILE
@@FETCH_STATUS = 0
BEGIN
-- Process xml data into table
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlLine
INSERT INTO #outputTable
SELECT *
FROM OPENXML (@idoc, '/rowdata',3)
WITH (date SMALLINT, year INT, month SMALLINT, p1_drug_b VARCHAR(100), r1_drug_b VARCHAR(100), p1_hosp_b VARCHAR(100), r1_hosp_b VARCHAR(100), p2_drug_b VARCHAR(100),
r2_drug_b VARCHAR
(100), p2_hosp_b VARCHAR(100), r2_hosp_b VARCHAR(100), p4_drug_b VARCHAR(100), r4_drug_b VARCHAR(100), p4_hosp_b VARCHAR(100), r4_hosp_b VARCHAR(100),
p5_drug_b VARCHAR
(100), r5_drug_b VARCHAR(100), p5_hosp_b VARCHAR(100), r5_hosp_b VARCHAR(100), p7_drug_b VARCHAR(100), r7_drug_b VARCHAR(100), p7_drug_g VARCHAR(100),
r7_drug_g VARCHAR
(100), p7_hosp_b VARCHAR(100), r7_hosp_b VARCHAR(100), p7_hosp_g VARCHAR(100), r7_hosp_g VARCHAR(100)
)
EXEC sp_xml_removedocument @idoc
FETCH NEXT FROM csr_xml INTO @xmlLine
END
CLOSE
csr_xml
DEALLOCATE
csr_xml
SELECT
* FROM #outputTable
DROP
TABLE #xmlTable
DROP
TABLE #outputTable
/* Example End */
Any questions, problems let me know... As I say it's untested on a range of csv files..
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply