VB Script - Sorting Tab Delimited Text File

[NickC111]

I have a tab delimited text file that needs to be sorted by the 1st reference.

Example Text File

7001712282 09/02/2010 NOTES1
7001712282 21/06/2010 NOTES2
7001712282 16/02/2011 NOTES3
7001712282 25/08/2011 NOTES4
7001750271 16/02/2011 NOTES1
7001750271 26/05/2011 NOTES2
7001750271 25/08/2011 NOTES3
7001535829 18/09/2009 NOTES1
7001535829 09/02/2010 NOTES2
7001535829 21/06/2010 NOTES3

I want to sort the data by the 1st reference so that the dates and notes are stored in a single row.

Like this

7001712282,09/02/2010,NOTES1,21/06/2010,NOTES2,16/02/2011,NOTES3,25/08/2011,NOTES4

Any ideas?

 

[Geates]

- open the text file
- read a line and split it into an array of 3 elements
- set three variables - one for the reference number(strRefNum), one for the combined row (strRow), and one for everything (strEverything), all to an empty string.
- compare the first element with strRefNum. if it is different, append strRow to strEverything (if it is not empty) and set strRow to strRefNum. append the 2nd and 3rd element to the string.
-repeat until the whole file is read.
-append strRow to strEverything.

set objFSO = CreateObject("Scripting.FileSystemObject")
set objFile = objFSO.OpenTextFile("c:\myFile.txt")

strRefNum = ""
strRow = ""
strEverything = ""
do while not objFile.AtEndOfStream
	strLine = objFile.ReadLine
	arrElements = split(strLine, vbTab, 3)
	if (strRefNum <> arrElements(0)) then
		if (strRow <> "") then
			strEverything = strEverything & strRow & vbNewLine
		end if
		strRefNum = arrElements(0)
		strRow = strRefNum
	end if
	strRow = strRow & "," & arrElements(1) & "," & arrElements(2)
loop

strEverything = strEverything & strRow

msgbox strEverything

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer