SQLServerCentral / Discuss Content Posted by Roy Carlson / Article Discussions / Article Discussions by Author / Running a Query Using a Text File for Input / Latest Posts

RE: Running a Query Using a Text File for Input

Jay Tucker — Wed, 24 Jun 2009 17:34:57 GMT

Anatol,I used your method to prepare text input to use in a WHERE clause with an IN ( ) list. If I have a lot more data to input, I would want to use a file for input, but I don't know how to actually use the file that is created in your example. Please explain.Thanks.

RE: Running a Query Using a Text File for Input

barbichaf2000 — Tue, 04 Mar 2008 14:29:28 GMT

Hi I am new to SQL servers and I would like to know how does one script a database and what is scripting. I know how to use the copy tables but I don't want to copy tables only, I would like to copy the entire database.Thank You

RE: Running a Query Using a Text File for Input

Philippe Cand — Fri, 18 Jan 2008 13:26:31 GMT

[quote][b]mojo (1/14/2008)[/b][hr]Does anyone know the Microsoft.Jet.OLEDB.4.0 engine works with 64-bit SQL 2005 server?[/quote]See thishttp://sqlblogcasts.com/blogs/simons/archive/2006/01/05/SSIS-64-Bit_2C00_-Excel-and-Access.aspxIt has a link to another post where more details are discussed. ;)

RE: Running a Query Using a Text File for Input

Jeff Moden — Fri, 18 Jan 2008 10:57:19 GMT

Very good comprehensive article. I just don't understand why a simple BULK INSERT couldn't be used to populate a temp table...

RE: Running a Query Using a Text File for Input

KDM8943 — Fri, 18 Jan 2008 10:08:11 GMT

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"] [size="2"] 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 = &H0001strFilename = "C:\TEMP\custID.txt "Set objFSO = CreateObject("Scripting.FileSystemObject")Set objFile = objFSO.OpenTextFile(strFilename, ForReading)' Read text file line by lineDo Until objFile.AtEndOfStream strLine = strLine & "'" & objFile.ReadLine & "',"LoopobjFile.ClosestrLine = left(strLine, len(strLine)-1) 'KNOCK OFF THE LAST APOSstrSql = "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 rsSet 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.CloseELSE 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[/size][/font]

RE: Running a Query Using a Text File for Input

mojo-168709 — Mon, 14 Jan 2008 12:51:31 GMT

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 1The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

RE: Running a Query Using a Text File for Input

sho-467341 — Tue, 13 Nov 2007 07:01:00 GMT

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!

RE: Running a Query Using a Text File for Input

Paul Thornett — Tue, 13 Nov 2007 05:59:46 GMT

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.

RE: Running a Query Using a Text File for Input

sho-467341 — Tue, 13 Nov 2007 03:45:18 GMT

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

RE: Running a Query Using a Text File for Input

R Wong — Sat, 03 Mar 2007 00:43:00 GMT

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?

RE: Running a Query Using a Text File for Input

Philippe Cand — Fri, 02 Mar 2007 10:38:00 GMT

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.

RE: Running a Query Using a Text File for Input

Predrag Miletic — Fri, 02 Mar 2007 03:48:00 GMT

Very comprehensive. Cheers Paul.

RE: Running a Query Using a Text File for Input

Paul Thornett — Thu, 01 Mar 2007 15:08:00 GMT

1) Create your text folder on the server, e.g. D:\Mssql\LinkedServer2) Populate your text folder. For a simple example, I'm going to use the following tab-delimited file, called Example.txtMyCol1(TAB)MyCol2(TAB)MyCol312138(TAB)129(TAB)Row 1 description12138(TAB)129(TAB)Row 2 description12165(TAB)133(TAB)Row 3 description12212(TAB)134(TAB)Row 4 description12243(TAB)140(TAB)Row 5 description3) 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=OEMColNameHeader=TrueFormat=TabDelimited But I find it better to describe each column, as in:[MyExample.txt]CharacterSet=OEMColNameHeader=TrueFormat=TabDelimitedCol1=NamedCol1 LongCol2=NamedCol2 LongCol3=NamedCol3 Text Width 502) Add the linked serverexec sp_addlinkedserver ServerName, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'FolderLocation', NULL, 'Text'ServerName is your name for the serverFolderLocation refers to the folder on the server where your text files are located, e.g. D:\Mssql\LinkedServer3) 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. NamedCol1Notes------ 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.

RE: Running a Query Using a Text File for Input

Yelena Varshal — Thu, 01 Mar 2007 10:05:00 GMT

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.

RE: Running a Query Using a Text File for Input

Dennis D. Allen — Thu, 01 Mar 2007 09:04:00 GMT

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 ONSELECT    C.CustomerID,    C.CompanyName,    C.City,    O.OrderDate,    O.FreightFROM    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

RE: Running a Query Using a Text File for Input

finaltable — Thu, 01 Mar 2007 09:01:00 GMT

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.

RE: Running a Query Using a Text File for Input

Predrag Miletic — Thu, 01 Mar 2007 08:28:00 GMT

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.

RE: Running a Query Using a Text File for Input

JJ B — Thu, 01 Mar 2007 08:26:00 GMT

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!

RE: Running a Query Using a Text File for Input

Predrag Miletic — Thu, 01 Mar 2007 08:25:00 GMT

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.

RE: Running a Query Using a Text File for Input

Roger L Reid — Thu, 01 Mar 2007 08:15:00 GMT

Since folks are casting around for the "more than one way to do it" - my tendency would be to use perl and sqsh. Has the advantage of working equally well from a Unix or Windows client, and works whether the database is MS SQL Server, Sybase, or both.

Which simply reflects my own background and circumstances as does the original authors.

RE: Running a Query Using a Text File for Input

webrunner — Thu, 01 Mar 2007 07:50:00 GMT

Hello Paul,Yes, please post more details on using the Jet 4.0 text driver for this type of query. Others have also mentioned Reporting Services and also trying to determine if there is a query that can be done straight from the database by talking to the users and finding out their logic for selecting records in the first place.Thanks,webrunnerP.S. I think this discussion is great. Depending on the situation, you never know which method will be available, so it is good to know as many as possible.

RE: Running a Query Using a Text File for Input

jancorley — Thu, 01 Mar 2007 07:44:00 GMT

I agree. vbs and xls are helpful, at times, but yours is the better solution.

RE: Running a Query Using a Text File for Input

schleep — Thu, 01 Mar 2007 07:17:00 GMT

Since we're allowed to use QA and presumably access other DBs on the server, why not BCP in into a DB the list of customers, then use osql to run a single query, joining to the tables in the vendor's DB?The vbs solution requires 17 000 queries, which, IMHO, is ludicrous.P

RE: Running a Query Using a Text File for Input

tripleAxe — Thu, 01 Mar 2007 07:12:00 GMT

Why not use T-SQL to generate the queries to be executed? Then cut and paste the result of this into a text file to be executed using OSQL.

For example,

SELECT DISTINCT'SET NOCOUNT ON SELECT C. CustomerID, C. CompanyName, C.City, O. OrderDate, O. Freight FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID WHERE C.CustomerID = '''+C.CustomerId+''''FROM Customers C INNER JOIN Orders O ON C.CustomerID = O.CustomerID

RE: Running a Query Using a Text File for Input

Richard Gardner-291039 — Thu, 01 Mar 2007 04:15:00 GMT

Fair enough - it could be a useful solution, I agree.

My personal preference these days is reporting services - it takes 5 minutes to set up a data source, run a query and deploy to the intranet, then dump it to Excel.

Once you've taught your users how to use autofilters you can dump anything you want in half an hour and everyone is happy.

RE: Running a Query Using a Text File for Input

Predrag Miletic — Thu, 01 Mar 2007 04:08:00 GMT

Absolutely agree Richard. But assumption here is that one would do what you suggest by default and not just jump every time end user says hop (well, unless you are junior DBA ;-)).But once your discussion with requestor is exhausted and you end up with task like this then tech solutions discussed here is exactly what you need.

RE: Running a Query Using a Text File for Input

Predrag Miletic — Thu, 01 Mar 2007 03:55:00 GMT

This article gave me a nice insight into vbs since I did not use it for a long time, but agree with you guys that is a bit of overkill. I would use Excel myself, but have to admit that the other 3 suggestions are very nice (Dave's querysql, Paul's Jet and Daniel's DOS).

Another solution would be MS Access (even though personally it is my last resort): import text file, link the other tables from SQL and create report.

Paul, could you please provide us with more details on your Jet 4.0 text driver solution.

Rgds

RE: Running a Query Using a Text File for Input

Richard Gardner-291039 — Thu, 01 Mar 2007 03:55:00 GMT

Interesting tecchy answers......

To my mind the problem would be better solved by asking the user what their criteria is for choosing the 17,000 customers in the first place, there must have been some reason and therefore logic behind the distinction, then write that query instead and you've saved the user the bother of ever doing the same thing again....

If I spent my life doing what users told me instead of what they need to be done I'd be an Excel expert..........

RE: Running a Query Using a Text File for Input

Stefan Morrow — Thu, 01 Mar 2007 01:47:00 GMT

Hi there,this topic brings me actually to another issue. Considering the original limitations of query and report only, would it have been acceptable to write a stored procedure? That is obviously if the server is 2005 and has CLR enabled.I had some similar topics not too long ago to implement data pushing usijng C# stored procedures rather than using polling mechanisms to update client workstations.Using a stored procedure to output to a text file would allow you to communicate with outside processes as well as build a bridge for older or incompatible systems.As I am new, please allow me the question: have there been any topics on CLR stored procedures?Stefan

RE: Running a Query Using a Text File for Input

Daniel van der Meulen — Thu, 01 Mar 2007 01:26:00 GMT

Hi,

I like the approach to solving the problem, but the VBscript seems like overkill. I've also used excel several times to solve these problems. But when they are this easy why not use good old DOS:

FOR /F %i in (custID.txt) do @echo set nocount on select %i >> custID.qry

This solved your problem in just one line of code. For more information on the FOR command, just type "help for" on the command line.

Daniel

RE: Running a Query Using a Text File for Input

Paul Thornett — Thu, 01 Mar 2007 01:20:00 GMT

I prefer to use a linked server using the Jet 4.0 text driver. This technique allows me to treat the incoming text file as a table, so I can join to it in the normal way. If anyone is interested, I can provide more detail on this. It's a technique I use a lot when dealing with tab-delimited files which are used either for input or output (yes, I can also INSERT new rows into my text files).

RE: Running a Query Using a Text File for Input

Dave Dustin — Thu, 01 Mar 2007 01:06:00 GMT

whereas my solution was to write an application in C++ which lets me use a placeholder in a sql file, and specify a tab delimited text file to provide the values.

-- test.sql

SELECT TestTable.RowID, TestTable.RowName FROM TestTable WHERE TestTable.RowID = '@1'

-- input.txt

123321456654

-- console

querysql.exe -server=(local) -database=testdb -sql=test.sql -input=input.txt -output=output.txt

very handy because I don't have to create any tempoary files with all the sql statements. Output can be either to screen or to a file. Works with either a ODBC-DSN or direct to the SQL server.

Would this be of interest to anybody else?

RE: Running a Query Using a Text File for Input

Anatol Romanov-404520 — Wed, 28 Feb 2007 22:12:00 GMT

Nice solution. As a DBA I faced similar tasks many times. My usual way of completing them is by using Excel:

1. Start with 1 column

ALFKIANTONAROUTBERGSBOLIDBONAPBSBEVCACTUCONSHDOGGYFOLIGFOLKOFRANKFRANRFRANSFURIB

2. Insert a column in front of it

3. Type

Placeholder1

in the first cell of this column. Drag and drop the value to populate the whole column.

4. Type

Placeholder2

in the first cell of the 3rd column. Drag and drop the value to populate the whole column.

5. "Save As" to a text file

6. Open the saved text part in notepad. do Edit/Replace replacing

Placeholder1 with

SET NOCOUNT ON SELECT C. CustomerID, C. CompanyName, C.City, O. OrderDate, O. Freight FROM Customers C INNER JOIN Orders O " ON C.CustomerID = O.CustomerID WHERE C.CustomerID = '

then replacing Placeholder2 with a single quote. Make sure all TABs and Commas are replaced with empty strings as well.

Save the output.

It is more simple than it sounds!

Regards,

Anatol Romanov

Running a Query Using a Text File for Input

rscarlson — Wed, 10 Jan 2007 09:26:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/rcarlson/2801.asp

The original solution required much more than this article suggested. The response from everyone emphasized the need to always examine the alternatives. Sometimes the easiest solution is not the best and vice versa. I am very happy that this article generated an active response. The comment about what users want and what they need is an important one to remember.

Thanks for the great discussion.