![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n \n
Hey, Scripting Guy! How can I convert a comma-separated values file to a tab-separated values file? Hey, RV. Depending on the nature of your comma-separated values (CSV) file this is either very easy or a little bit tricky. Let\u2019s follow the Scripting Guys Philosophy of Life and look at the easy one first.<\/P>\n
— RV<\/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> \n
| \n Note<\/B>. Of course, in our real<\/I> Philosophy of Life we\u2019d take the easy one first and then hope that there wouldn\u2019t be time to do the hard one as well. Today, however, we\u2019ll make an exception and tackle both the easy one and the hard one.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n Let\u2019s assume we have a very simple CSV file, one that looks like this:<\/P> How can we convert this to a tab-separated values file (TSV)? Like we said, this is pretty easy. We\u2019ll begin by opening the file and reading the entire contents into a variable. Once the file contents are in memory we\u2019ll replace all the commas with tabs and then re-save the file. Just like magic we\u2019ll have turned a CSV file into a TSV file. Here\u2019s the code that does the trick:<\/P> Set objFSO = CreateObject(“Scripting.FileSystemObject”)<\/p>\n Set objFile = objFSO.OpenTextFile(“C:\\scripts\\test.txt”, ForReading)\nDo Until objFile.AtEndOfStream\n strLine = objFile.ReadLine\n strLine = Replace(strLine, “,”, vbTab)\n strNewText = strNewText & strLine & vbCrLF\nLoop<\/p>\n objFile.Close<\/p>\n Set objFile = objFSO.OpenTextFile(“C:\\scripts\\test.txt”, ForWriting)\nobjFile.WriteLine strNewText<\/p>\n objFile.Close\n<\/PRE>\n We begin by defining two constants – ForReading and ForWriting – that we use when opening and then re-opening our file (C:\\Scripts\\Test.txt). And, yes, we said re-opening: when using the FileSystemObject<\/B> to work with text files you can open a file for reading or<\/I> you can open a file for writing; unfortunately, you can\u2019t do both at the same time. Because of that we\u2019ll need to open the file, read the contents into memory, close the file, and then re-open that same file for writing in order to save the modified data.<\/P>\n After defining our constants we create an instance of the FileSystemObject and use the OpenTextFile<\/B> method to open the file for reading. We then create a Do Loop in which we\u2019ll read in the existing CSV file and then construct our new TSV file.<\/P>\n How do we do that? We begin by using the ReadLine<\/B> method to read the first line of the file and store it in a variable named strLine. Next we use the VBScript Replace<\/B> method to replace all the commas in the variable with tab characters; that\u2019s what happens here:<\/P> Note that we use the VBScript constant vbTab<\/B> to indicate the tab character; had we used something like \u201ctab\u201d<\/B> we would have replaced all the commas with the word tab<\/I>. Not exactly what we had in mind. Likewise, don\u2019t try typing a set of double quotes, setting the cursor inside those double quotes and pressing the TAB key (\u201c \u201c). That just won\u2019t work.<\/P>\n After we replace the commas with tabs we can start putting together our revised file. In this line of code we assign the variable strNewText the current value of strNewText (which, the first time through the loop, will be nothing) plus<\/I> the new, tab-delimited strLine plus<\/I> a carriage-return linefeed (vbCrLf): <\/P> The first time through the loop strNewText will look like this, with tabs replacing the commas:<\/P> We then loop around and repeat the process with line 2. By the time we\u2019ve finished reading in all the lines of the file the variable strNewText will hold this value:<\/P> As you can see, we\u2019ve removed all the commas and replaced them with tabs. All we have to do now is close the file and then re-open it for writing. With the file re-opened we can use the WriteLine<\/B> method to replace the existing contents of the file with the value of strNewText. And that\u2019s it: our CSV file is now a TSV file. And we did it all PDQ ASAP. <\/P>\n See, that was<\/I> pretty easy, wasn\u2019t it? Now, what about that harder scenario? Well, suppose we have a CSV file that looks like this:<\/P> This is still a CSV file; the difference is that individual items are enclosed in double quote marks. That\u2019s because some of the items include commas themselves. For example, the first line in the file is actually composed of these three items:<\/P> Why do we care about that? Well, suppose we use our previous script and simply replace all the commas with tabs. If we do that, our first line will be divvied up like this:<\/P> Oops; that\u2019s not even close to being right. The problem (aside from the double quotes) lies with the commas found inside the double quote marks; those commas should not<\/I> be converted to tabs. How are we going to deal with that<\/I>?<\/P>\n Well, to be honest, the best way to deal with this issue would be to use regular expressions. However, explaining how regular expressions work lies way beyond the scope of this column; to learn more about that we encourage you to view the Scripting Guys webcast<\/B><\/A> on the subject. In the meantime, we\u2019ll show you a solution that, while not 100% foolproof, should work in most situations:<\/P> Set objFSO = CreateObject(“Scripting.FileSystemObject”)<\/p>\n Set objFile = objFSO.OpenTextFile(“C:\\scripts\\test.txt”, ForReading)\nDo Until objFile.AtEndOfStream\n strLine = objFile.ReadLine\n strReplacer = Chr(34) & “,” & Chr(34)\n strLine = Replace(strLine, strReplacer, vbTab)\n strLine = Replace(strLine, chr(34), “”)\n strNewText = strNewText & strLine & vbCrLF\nLoop<\/p>\n objFile.Close<\/p>\n Set objFile = objFSO.OpenTextFile(“C:\\scripts\\test.txt”, ForWriting)\nobjFile.WriteLine strNewText<\/p>\n objFile.Close\n<\/PRE>\n The difference between this script and the first one we showed you occurs when we go about replacing commas in a line. We now do that using these three lines of code:<\/P> In the first line we\u2019re assigning the value Chr(34) & “,” & Chr(34)<\/B> to a variable named strReplacer; that means strReplacer will now be equal to this:<\/P> What was the point of that? Well, as we\u2019ve already seen, we can\u2019t search for and replace all<\/I> commas. Therefore, what we\u2019re going to do in line 2 is search for double quotes followed by a comma followed by another set of double quotes. Sounds crazy but if you take a look at line 1 in our text file you\u2019ll see that this is actually the pattern used to separate items:<\/P> In other words, replacing all instances of \u201c,\u201d <\/B>will yield this:<\/P> As you can see, that\u2019s pretty close to what we want; we just need to do something about those extraneous double quotes. That\u2019s what we do with our third line of code, which replaces any remaining double quote marks with nothing:<\/P> The net result? Just what the doctor ordered:<\/P> |