Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Running a Query Using a Text File for Input Expand / Collapse
Author
Message
Posted Thursday, March 1, 2007 10:05 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 2:44 PM
Points: 3,475, Visits: 583

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

Post #348581
Posted Thursday, March 1, 2007 3:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, 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.



Post #348654
Posted Friday, March 2, 2007 3:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 2, 2007 3:45 AM
Points: 5, Visits: 1
Very comprehensive. Cheers Paul.
Post #348732
Posted Friday, March 2, 2007 10:38 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:35 PM
Points: 49, Visits: 280

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
Post #348858
Posted Saturday, March 3, 2007 12:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 27, 2012 7:28 AM
Points: 18, Visits: 46

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?

 

Post #348956
Posted Tuesday, November 13, 2007 3:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 11:56 AM
Points: 70, 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
Post #421433
Posted Tuesday, November 13, 2007 5:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 8:38 AM
Points: 28, 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.


Post #421524
Posted Tuesday, November 13, 2007 7:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 8, 2013 11:56 AM
Points: 70, 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!
Post #421561
Posted Monday, January 14, 2008 12:51 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 10:46 AM
Points: 911, 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.
Post #442641
Posted Friday, January 18, 2008 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 18, 2013 10:38 AM
Points: 6, 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
Post #445008
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse