![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n \n
Hey, Scripting Guy! I have a fixed-width text file which includes first name (6 characters), last name (13 characters), and department (9 characters) all on a single line. How do I read that using ADO? Hey, DW. Well, to be perfectly honest, we don\u2019t think you can<\/I> read a file like this using ADO (ActiveX Data Objects). ADO is a fantastic way to interact with text files (for more information, see the article Much ADO About Text Files<\/B><\/A>), but – really – ADO is a bit of a prima donna: everything has to be set up just right or ADO will refuse to work. (Unlike the Scripting Guys, who refuse to work even if everything is<\/I> set up just right.) As a database technology, ADO requires – to steal a term from the XML world – a \u201cwell-formed\u201d database; it\u2019s simply not equipped to take a file like yours and make<\/I> a database out of it. (In this case, by chopping your huge line into individual records.)<\/P>\n But that\u2019s all right; after all, we can use good old, reliable VBScript to create a well-formed database. What we\u2019ll do today is show you how you can quickly and easily carve your huge string of data into individual records. We\u2019ll do that simply by echoing those individual records (i.e., individual records in the text file) back to the screen. However, you could easily write those lines to another text file and then use ADO to read in that second text file. We\u2019re just going to help you get started, then let you take it from there.<\/P>\n To begin with, we\u2019re assuming you have a text file that looks something like this, with field names and a couple of records all contained on a single line:<\/P> What we want to end up with is something that looks more like this:<\/P> Is that possible? Of course it is:<\/P> Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)<\/p>\n strContents = objFile.ReadAll\nobjFile.Close<\/p>\n i = False<\/p>\n Do Until i = True \n intLength = Len(strContents)\n If intLength < 28 Then\n Exit Do\n End If\n strLines = strLines & Left(strContents, 28) & vbCrLf\n strContents = Right(strContents, intLength – 28)\nLoop<\/p>\n Wscript.Echo strLines\n<\/PRE>\n We start out by defining a constant named ForReading and set the value of that constant to 1; we\u2019ll use that to open our text file for reading. <\/P>\n Note<\/B> You know how your mother would always<\/I> tell you to put your hood up, or to look both ways when crossing the street? Well, we Scripting Guys always<\/I> tell you that you can\u2019t open a text file without specifying how you want to open that text file: for reading, for writing, for appending. And you can only do one operation at a time: you can\u2019t open a text file for both reading and<\/I> writing. Hence the need for the constant ForReading.<\/P>\n Oh: and put your hood up, too. It\u2019s cold outside.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n Next we create an instance of the FileSystemObject<\/B> and use the OpenTextFile<\/B> method to open the file C:\\Scripts\\Test.txt. We call the ReadAll<\/B> method in order to read the entire contents of that file into the variable strContents, then use the Close<\/B> method to close the file.<\/P>\n After that we have this odd little line of code:<\/P> What we\u2019re going to do in a second is set up a Do loop that enables us to read the contents of the file (well, OK, the value of the variable strContents) in 28-character increments. Why 28 characters? Well, we have 6 characters in each record reserved for the first name, 13 characters set aside for the last name, and 9 characters allotted for the department. Thus:<\/P> In order to keep our loop going until we\u2019ve read through the entire file, we set the variable i<\/I> to False, then tell our loop to keep going until i<\/I> is equal to True:<\/P> As it turns out, i<\/I> never will<\/I> equal True, but don\u2019t worry: we\u2019ll still be able to get out of the loop, and we\u2019ll show you that Houdini-like feat in a moment.<\/P>\n Inside the loop we first use the Len<\/B> function to determine the number of characters in the string strContents (with our sample file, and counting the header line as a record, that should be 28 characters per record times 3 records, or 84). We then have this little block of code:<\/P> What we\u2019re doing here is checking to see if the length of our string is less than 28 characters. If it is, then we must have reached the end of the file. Therefore, we use the Exit Do<\/B> command to exit the loop. (And that\u2019s<\/I> how the amazing Scripting Guys managed to escape the clutches of the never-ending Do loop!)<\/P>\n Assuming the length is 28 characters or more, we then execute this line of code:<\/P> Here we\u2019re creating a new string – strLines – that divides the contents of our text file into separate records, each record consisting of 28 characters plus a carriage return-linefeed (vbCrLf<\/B>). To do that, we simply combine the current contents of strLine with the first 28 characters in the string (that\u2019s what the Left<\/B> function does) and the VBScript constant vbCrLf. The first time through the loop strLines will look like this:<\/P> See how that works? <\/P>\n As soon as our first line (that is, our first record) is safely tucked away in the variable strLines, we then need to delete that information from strContents. That\u2019s what we do here:<\/P> This time we\u2019re using the Right<\/B> function to take x<\/I> number of characters from the end of the string, working our way backwards. What, exactly, do we mean by x<\/I> number of characters? To calculate x<\/I> we take the length of strContents (84) and subtract 28 (the length of an individual record). That leaves us with 56 (84 – 28 = 56) so we start at the end of the string and count back 56 characters. That means, the first time through the loop, we\u2019ll end up with this:<\/P> Note<\/B>. Yes, there are other ways we could do this, some of which might save you a line or two of code. However, we thought this approach was the easiest.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n As you can see, all we\u2019ve done is remove the first record. We\u2019re now ready to go back through the loop and repeat the process. This time, of course, the length of strContents will be 56; that\u2019s because we just eliminated 28 characters. In turn, that means we\u2019ll end up pulling out this data and adding it to the variable strLines: <\/P> We continue this process until we run out of characters in the variable strContents. At that point we simply echo back the values of strLines:<\/P> If that\u2019s not a well-formed database, well, we don\u2019t know what is.<\/P>\n
— DW<\/P>![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \nFirst Last Dept Ken Meyer Finance Pilar Ackerman HR\n<\/PRE>\n
First Last Dept \nKen Meyer Finance \nPilar Ackerman HR\n<\/PRE>\n
Const ForReading = 1<\/p>\n
\n<\/THEAD>\n\n
\n \n <\/DIV>\ni = False\n<\/PRE>\n
6 + 13 + 9 = 28\n<\/PRE>\n
Do Until i = True\n<\/PRE>\n
If intLength < 28 Then\n Exit Do\nEnd If\n<\/PRE>\n
strLines = strLines & Left(strContents, 28) & vbCrLf\n<\/PRE>\n
First Last Dept\n<\/PRE>\n
strContents = Right(strContents, intLength – 28)\n<\/PRE>\n
Ken Meyer Finance Pilar Ackerman HR\n<\/PRE>\n
\n<\/THEAD>\n\n
\n \n <\/DIV>\nKen Meyer Finance\n<\/PRE>\n
First Last Dept \nKen Meyer Finance \nPilar Ackerman HR\n<\/PRE>\n