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,
So first off we declare are variables,
Dim oFSO, oInputFile,sInputLine Dim oOutputFile,sOutputLine Dim sInputFile Dim sOutputFile Dim iChar,bEveryChar Dim oArray
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 = "Y" oArray = "Y" oArray = "Y" oArray = "Y" oArray = "Y" oArray = "Y" oArray = "Y" oArray = "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 thethat 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 simpleto split up your card column file and get it loaded into excel cleanly. So what does the output file look like , here is an example00092,91,06,000,02,3,2,9,2 00092,91,64,100,01,5,5,4,1