Phil,
I know you've solved your problem already but I thought this was a fairly straight forward solution and you said, "It was a surprisingly awkward task but I may well have done it rather inefficiently... ". So this might be less awkward.
Information sources for this script sample are:
http://msdn.microsoft.com/en-us/library ... 85%29.aspx
For Schema.ini information.
http://www.mjtnet.com/forum/dbquery-dir ... t=sqltocsv
For Marcus' SQLToCSV VBScript
And:
http://www.mjtnet.com/blog/2013/03/27/g ... installed/
I could be wrong, but I believe that if you don't have office installed, this solution will not work for you without the Microsoft Office Data Connectivity Components mentioned on this blog page. I'm not really sure because I don't have any computers to test on that don't already have ODBC drivers installed. Perhaps someone else can clarify this issue.
Phil, you stated you didn't want to resort to using excel and I don't know if this script violates your rule or not.
This script uses SQL's "order by" function to sort by the text file's fourth column. We use the schema.ini file to tell the Microsoft driver that the delimiter is a semicolon ";". Then process the SQL line through VBScript.
More explanation in the comments.
Hope someone finds this useful,
Dick
Code: Select all
//Load the VBScript
GoSub>VBScript
/*
Create and/or cleanup the folder
I like to keep schema using script created files in a unique
folder so that schema.ini files don't interfere with each other.
"Schema.ini" must have that exact name and reside in with the
files its formatting
*/
CreateDir>%temp_dir%__SchemaSample
DeleteFile>%temp_dir%__SchemaSample\Schema.ini
DeleteFile>%temp_dir%__SchemaSample\PhilText.txt
DeleteFile>%temp_dir%__SchemaSample\SortedPhilText.txt
//Write Schema.ini to the folder
LabelToVar>Schema,vSchemaData
WriteLn>%temp_dir%__SchemaSample\Schema.ini,wres,vSchemaData
//Write Phil's sample data to the folder
LabelToVar>Sampletext,vSampleData
Let>WLN_NOCRLF=1
WriteLn>%temp_dir%__SchemaSample\PhilText.txt,wres,vSampleData
Let>WLN_NOCRLF=0
//Set up the parameters and run modified SQLToCSV
Let>con=Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=%temp_dir%__SchemaSample\;
Let>sql=select * from PhilText.txt order by F4
Let>oFile=%temp_dir%__SchemaSample\SortedPhilText.txt
VBRun>SQLToCSV,con,sql,oFile
//ReadFile and remove Header added by schema.ini and SQL
ReadFile>%temp_dir%__SchemaSample\SortedPhilText.txt,vData
StringReplace>vData,F1;F2;F3;F4%crlf%,,vData
MDL>vData
/*
Sampletext:
file;563547215;0;Z:\Z - Manuals\PR\B.txt
file;5645215;0;Z:\Z - Manuals\PR\C.txt
file;3457215;0;Z:\Z - Manuals\PR\A.txt
file;55415;0;Z:\Z - Manuals\PR\D.txt
*/
/*
Schema:
[PhilText.txt]
Format=Delimited(;)
ColNameHeader=False
*/
//Modified SQLToCSV the write in phil's format
SRT>VBScript
VBSTART
Sub SQLToCSV(connection_string,sql_string,output_file)
Dim outFile, objConnection, objRecordSet, strSQL, intCount
set outFile = CreateObject("Scripting.FileSystemObject").CreateTextFile(output_file)
Set objConnection = CreateObject("ADODB.Connection")
objConnection.open connection_string
Set objRecordSet = objConnection.Execute(sql_string)
for intcount = 0 to objRecordSet.fields.count -1
if intcount <> objRecordSet.fields.count-1 then
outFile.write objRecordSet.fields(intcount).name & ";"
else
outFile.write objRecordSet.fields(intcount).name
end if
next
outFile.writeline ""
do while not objRecordSet.eof
for intcount = 0 to objRecordSet.fields.count - 1
if intcount <> objRecordSet.fields.count-1 then
outFile.write objRecordSet.fields(intcount).value & ";"
else
outFile.write objRecordSet.fields(intcount).value
end if
next
outFile.writeline ""
objRecordSet.movenext
loop
objRecordSet.Close
set objRecordSet = Nothing
objConnection.Close
set objConnection = Nothing
outFile.Close
set outFile = Nothing
End Sub
VBEND
END>VBScript
