SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Running a Query Using a Text File for Input


Running a Query Using a Text File for Input

Author
Message
Yelena Varshal
Yelena Varshal
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8916 Visits: 600

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 Varshal

Paul Thornett
Paul Thornett
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 96
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.



Predrag Miletic
Predrag Miletic
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 1
Very comprehensive. Cheers Paul.
Philippe Cand
Philippe Cand
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 294

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
R Wong
R Wong
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 49

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?


sho-467341
sho-467341
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 144
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
Paul Thornett
Paul Thornett
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 96
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.



sho-467341
sho-467341
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 144
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!
mojo-168709
mojo-168709
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1125 Visits: 831
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.
KDM8943
KDM8943
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 19
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:

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search