Loading a card column file into Excel

by

In this article we are going to show you how you might change a quantum output file into something that can be loaded up into Excel. You might want to do this if you just want to look at the data file.

Before we start lets take a quick look at what the input data looks like,

000929106000023292
000929164100015541

So first off we declare are variables,

Dim oFSO, oInputFile,sInputLine
Dim oOutputFile,sOutputLine
Dim sInputFile
Dim sOutputFile
Dim iChar,bEveryChar
Dim oArray[99]

This 

 uses something called the file system object which allows us to read and write files. To work with this object we need a few variables. First of we have the oFSO vairable and this will be used to connect to the file system object. Next we have a few vars that are used to hold information about the files we are going to play with ( oInputFile,sInputLine, oOutputFile,sOutputLine, sInputFile,sOutputFile ). From their names we can clearly see that they will be used to store information about the input and output files. And finaly we have some variables that will be used to identify each charater in the file ( iChar ), decide whether to seperate each char with a comma regardless ( bEveryChar ) and an Array that will be used to store the possitions of the commas if they are required. Once all these variables are decalred we then set up some card column positions.

' Set the array to mark where you want the , to be ( start at 0 )
oArray[4] = "Y"
oArray[6] = "Y"
oArray[8] = "Y"
oArray[11] = "Y"
oArray[13] = "Y"
oArray[14] = "Y"
oArray[15] = "Y"
oArray[16] = "Y"


In this case we want a comma in positions 5,7,9,12,14,16,17 we have taken one off these numbers as our array starts at 0. next we have the 


 that will connect to our input and output files.

sInputFile = "10-DER91.qtd"
oOutputFile = "10-DER91.csv"
bEveryChar = false

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	Set oInputFile = oFSO.OpenTextFile(sInputFile,iomode.ForReading)
	Set oOutputFile = oFSO.OpenTextFile(oOutputFile,iomode.forwriting,true)


Using the File System Object we can connect to our files and in the case of the output file create it. Then we can start to loop each line of the input file until the end adding in out commas at the correct place. The following do while loop does just that,


do while oInputFile.AtEndOfStream = false
	sInputLine = oInputFile.readLine()
	sOutputLine = ""
	If ( bEveryChar ) Then
		for iChar = 0 to len(sInputLine)
		sOutputLine = sOutputLine + mid(sInputLine,iChar,1) + ","
		Next
	Else
		for iChar = 0 to len(sInputLine)
			if ( oArray[iChar]  ="Y" )  Then
			sOutputLine = sOutputLine + mid(sInputLine,iChar,1) + ","
			Else
			sOutputLine = sOutputLine + mid(sInputLine,iChar,1)
			End If
		Next
	End If
	oOutputFile.writeline(sOutputLine)
Loop


As mentioned above we loop the input file a line at a time and after each line has been stored in the sInputLine variable , we loop each char 1 at a time and check against the array to see if we need to place a comma after it. We then add this character or characters ( if the comma is required ) to the output string and once all input characters are read we write the new line to the output file.


And then we finaly close the files and the program terminates.


	oInputFile.Close()
	oOutputFile.Close()

	Set oFSO = Null


So there you have it some realy simple 


 to split up your card column file and get it loaded into excel cleanly. So what does the output file look like , here is an example

00092,91,06,000,02,3,2,9,2
00092,91,64,100,01,5,5,4,1

Leave a Comment

%d bloggers like this: