Running a Query Using a Text File for Input
-
Cannot be used in the situation we are discussing at present. This T-SQL would generate statements for every single customer that made an order, but what we need is something to handle a large subset of a large customer base provided in the form of text file.
In other circumstances T-SQL would do just great. I use it often especially for admin tasks.
-
I, too, am tickled by this great discussion. I appreciate the author who wrote such a clear original article and then seeing the different ways other people would solve this.
Personally, I would use the Jet/Access method myself. However, if there were a reason I couldn't use Access, I would create the set of queries in Excel. Then the question is how to run them. I like the Author's suggestion of OSQL. I also like the DOS suggestion posted earlier. Both of these are areas I will research more. Thanks!
-
R L, give us some guidelines or short examples for the benefit of people that are not familiar with perl and sqsh. Hopefully something that makes solution quick and easy.
-
At my company we run into "requests" like this all the time. Us lowly developers don't have the right to create jack in the production database but the data results the business unit wants come from there. Our normal solution is to use Excel. In a Query analyzer window we'll do a create table statement to create a temporary table to hold the data, in this case "create table #tmp (cust varchar(5))"
We'll have a list of clients sent to us in Excel or a text file. Opening the attachment in Excel. We'll insert a blank column in front of the data column and define a formula as ="insert into #tmp values('" & A2 & "')" Copy the formula in A1 to the entire A column. Copy the column and do a Past...Special...Values. Copy the column of insert statements into QA and run. Now you have a temp table in a QA session you can join to the data in the database and use in whatever ways one needs.
We deal with payroll data for millions of people employed throughout the country and it is not uncommon to get a request to pull demographic info on 10-20K people at a time for some report or another. This method makes these requests trivial to handle.
Shuffle Up and Deal! -
Howdy All,
I agree that authoring a single select per CustomerID in the key file is wasteful and there is no need for the vbs script in this case. This also sounded like a one-off sort of request, so I thought I would offer a method that did not use a linked server.
Run the following query, either through the command line as in the article or in Query Analyzer and set your output options to create the desired output file.
SET NOCOUNT ON SELECT C.CustomerID, C.CompanyName, C.City, O.OrderDate, O.Freight FROM dbo.Customers AS C INNER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID INNER JOIN OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\;Extended Properties="text;HDR=No;FMT=Delimited"' )...custID#txt AS T ON T.F1 = C.CustomerID
Some things of note;
• The Data Source is the directory where the file being read is
• If there are no Header Rows (HDR=No) the column names are F1, F2, F3 and so on.
Cheers
-
It is just 17000 records of just one field CustomerID.
I am using everything that people mentioned above for different projects: Linked server with the text driver, OPENROWSET, CLR in 2005 to deal with text files, BCP, an independent (from the third-party) Reporting Database. I also support MS ACCESS /EXCELL VBA import from text files, VBscript from the ASP pages, VBscript by the cscript, whatever. My preferences are to use the back-end processing on the same server after getting the source data into temp tables. It is usually faster then doing the front-end processing or distributed queries.
Regards,Yelena Varsha
-
1) Create your text folder on the server, e.g. D:\Mssql\LinkedServer
2) Populate your text folder. For a simple example, I'm going to use the following tab-delimited file, called Example.txt
MyCol1(TAB)MyCol2(TAB)MyCol3
12138(TAB)129(TAB)Row 1 description
12138(TAB)129(TAB)Row 2 description
12165(TAB)133(TAB)Row 3 description
12212(TAB)134(TAB)Row 4 description
12243(TAB)140(TAB)Row 5 description
3) Create a Schema.ini file in the same folder. This file is used to describe all the text files you want to refer to.
At the very least, it contains the following lines for each filename:
[MyExample.txt]
CharacterSet=OEM
ColNameHeader=True
Format=TabDelimited
But I find it better to describe each column, as in:
[MyExample.txt]
CharacterSet=OEM
ColNameHeader=True
Format=TabDelimited
Col1=NamedCol1 Long
Col2=NamedCol2 Long
Col3=NamedCol3 Text Width 50
2) Add the linked server
exec sp_addlinkedserver ServerName, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'FolderLocation', NULL, 'Text'
ServerName is your name for the server
FolderLocation refers to the folder on the server where your text files are located, e.g. D:\Mssql\LinkedServer
3) Use the following syntax to access your text file:
SELECT * FROM TestServer...MyExample#txt (note the three dots, and the hash symbol - a period would interfere with SQL Server syntax)
If you use the shorter version of the Schema.ini file, your column names are those contained in the first row of the text file, e.g. MyCol1.
If you use the longer version of the Schema.ini file, your column names are those contained in the schema.ini file, e.g. NamedCol1
Notes
-----
- You can SELECT and JOIN and INSERT INTO your text files. You cannot use the DELETE or UPDATE commands.
- If you do SELECT *, your columns will appear in alphabetic sequence!!!
- If you need to give users access to your linked server, you may have to grant your users read/write access to the Temp folder on SQL Server. This is because one source I read stated that SQL Server needs to create its own temporary files in the Temp folder. I know that I had some problems in enabling users to read files using this approach; but we were also experiencing many problems with the database server, and eventually dumped that machine and used a different one (actually a virtual machine), so my problems may have been caused by something else.
-
Very comprehensive. Cheers Paul.
-
Hi,
It would have been good to mention that in SQL 2005, you can achieve the same result much more easilly by using a simple Merge Join Data flow transformation.
BI Guy
-
How about using SQL2005 express on my PC (local installation), use DTS to import the customer ID into a new table then T-SQL join the customer ID table to the linked db in SSM (provided that the db is linkable)?
Is there any problem or disadvantage doing it in this way?
-
Paul,
Thank you very much for posting that example!
It works a treat!
Is there anyway of doing away with the Schema.Ini file and specifying the rules (ie tab delimited and line feed etc directly in the query as you can with BULK INSERT?)
Cheers
-
I'm not aware of that possibility. You can remove the schema.ini file, but the sequence of the columns becomes arbitrary, to say the least! And doing that may introduce other limitations.
-
Thanks Paul,
I suspected as much. Anyway, I've used your excellent post to do what I needed and you've probably saved me many hours of trawling through t'internet to find what I was looking for.
REALLY appreciated!
-
Does anyone know the Microsoft.Jet.OLEDB.4.0 engine works with 64-bit SQL 2005 server?
I got this error when accessing a text file using OPENDATASOURCE with Microsoft.Jet.OLEDB.4.0 as the provider:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
-
Not necessarily... agreed that 17k 'select' statements is 'brute force', but i would suggest that a simple modification to the original vbs could do all of the work ... and 17k would not be prohibitive for the script below:
[font="Courier New"] Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateFalse = 0, TristateTrue = -1, TristateUseDefault = -2
'---- DataTypeEnum Values ----
Const adPropVariant = 138:Const adVarNumeric = 139:Const adArray = &H2000:Const adUseClient = 3
Const adOpenForwardOnly = 0:Const adLockReadOnly = 1:Const adCmdStoredProc = &H0004
Const adExecuteNoRecords = &H00000080:Const adOpenStatic = 3:Const adCmdText = &H0001
strFilename = "C:\TEMP\custID.txt "
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFilename, ForReading)
' Read text file line by line
Do Until objFile.AtEndOfStream
strLine = strLine & "'" & objFile.ReadLine & "',"
Loop
objFile.Close
strLine = left(strLine, len(strLine)-1) 'KNOCK OFF THE LAST APOS
strSql = "SET NOCOUNT ON; SELECT C.CustomerID as [CustomerID], C.CompanyName as [CompanyName], " &_
" C.City as [City], O.OrderDate as [OrderDate], O.Freight as [Freight] FROM Customers C INNER JOIN Orders O " &_
" ON C.CustomerID = O.CustomerID WHERE C.CustomerID IN (" & strLine & ") ORDER BY C.CustomerID; SET NOCOUNT OFF"
Wscript.Echo strSql
DIM rs
Set rs = GetRecordset( "(local)", "Northwind", strSql )
IF NOT ISNULL(rs) THEN
Set objFSOW = CreateObject("Scripting.FileSystemObject")
Set objFileW = objFSOW.OpenTextFile ("C:\TEMP\custIDResult.txt", ForAppending, True)
rs.MoveFirst
do until rs.EOF
objFileW.WriteLine rs("CustomerID") & "|" & _
rs("CompanyName") & "|" & _
rs("City") & "|" & _
rs("OrderDate") & "|" & _
rs("Freight")
rs.MoveNext
loop
set rs = nothing
objFileW.Close
ELSE
Wscript.Echo "RECORDSET IS EMPTY.... NO RECORDS TO PROCESS!"
END IF
'********************************************************************************
'
Function GetRecordset(ByVal TheSqlSvr, ByVal TheDB, ByVal strSQL)
Dim rs, sConnect
Set rs = CreateObject("adodb.Recordset")
rs.CursorLocation = adUseClient
sConnect ="Provider='SQLOLEDB';Data Source="& TheSqlSvr &";" _
& "Trusted_Connection=Yes;" _
& "Initial Catalog=" & TheDB
rs.Open strSQL, sConnect, adOpenForwardOnly, adLockReadOnly
Set rs.ActiveConnection = Nothing
Set GetRecordset = rs
Wscript.Echo "#Recs " & cstr(rs.RecordCount)
End Function
'==============================
'C:\TEMP\custID.txt
'ALFKI
'ANTON
'AROUT
'BERGS
'BOLID
'BONAP
'BSBEV
'CACTU
'CONSH
'DOGGY
'FOLIG
'FOLKO
'FRANK
'FRANR
'FRANS
'FURIB
[/font]
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply