3/15/2024 0 Comments Excel reflow text![]() ![]() Oops that’s not even close to being right. If we do that, our first line will be divvied up like this: “a” “b c d e” “f” Why do we care about that? Well, suppose we use our previous script and simply replace all the commas with tabs. For example, the first line in the file is actually composed of these three items: a b,c,d,e f That’s because some of the items include commas themselves. This is still a CSV file the difference is that individual items are enclosed in double quote marks. See, that was pretty easy, wasn’t it? Now, what about that harder scenario? Well, suppose we have a CSV file that looks like this: “a”,”b,c,d,e”,”f” And that’s it: our CSV file is now a TSV file. With the file re-opened we can use the WriteLine method to replace the existing contents of the file with the value of strNewText. All we have to do now is close the file and then re-open it for writing. By the time we’ve finished reading in all the lines of the file the variable strNewText will hold this value: a b c d e fĪs you can see, we’ve removed all the commas and replaced them with tabs. ![]() We then loop around and repeat the process with line 2. The first time through the loop strNewText will look like this, with tabs replacing the commas: a b c d e f 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 the new, tab-delimited strLine plus a carriage-return linefeed (vbCrLf): strNewText = strNewText & strLine & vbCrLF That just won’t work.Īfter we replace the commas with tabs we can start putting together our revised file. Likewise, don’t try typing a set of double quotes, setting the cursor inside those double quotes and pressing the TAB key (“ “). Note that we use the VBScript constant vbTab to indicate the tab character had we used something like “tab” we would have replaced all the commas with the word tab. ![]() ![]() Next we use the VBScript Replace method to replace all the commas in the variable with tab characters that’s what happens here: strLine = Replace(strLine, “,”, vbTab) How do we do that? We begin by using the ReadLine method to read the first line of the file and store it in a variable named strLine. We then create a Do Loop in which we’ll read in the existing CSV file and then construct our new TSV file. Because of that we’ll 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.Īfter defining our constants we create an instance of the FileSystemObject and use the OpenTextFile method to open the file for reading. And, yes, we said re-opening: when using the FileSystemObject to work with text files you can open a file for reading or you can open a file for writing unfortunately, you can’t do both at the same time. We begin by defining two constants – ForReading and ForWriting – that we use when opening and then re-opening our file (C:\Scripts\Test.txt). Set objFile = objFSO.OpenTextFile(“C:\scripts\test.txt”, ForWriting) StrNewText = strNewText & strLine & vbCrLF Set objFile = objFSO.OpenTextFile(“C:\scripts\test.txt”, ForReading) Set objFSO = CreateObject(“Scripting.FileSystemObject”) Here’s the code that does the trick: Const ForReading = 1 Just like magic we’ll have turned a CSV file into a TSV file. Once the file contents are in memory we’ll replace all the commas with tabs and then re-save the file. We’ll begin by opening the file and reading the entire contents into a variable. How can we convert this to a tab-separated values file (TSV)? Like we said, this is pretty easy. Let’s assume we have a very simple CSV file, one that looks like this: a,b,c,d,e,f ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |