![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n <\/p>\n
Hey, Scripting Guy! I have a comma-separated values file that I need to import into Excel. However, before I do that I need to do some arithmetic and then add an additional field to the CSV file. On top of that, when I import this file I need to format this new field as a date-time value. How can I do that?
— R<\/p>\n
Hey, R. Well, by now most of you have probably heard that Microsoft is no longer interested in buying Yahoo! Although we offered Yahoo! a deal worth around $50 billion, that proposal was rejected; Yahoo! wanted another $5 billion or so. (Hey, don\u2019t we all<\/i> want another $5 billion or so?) At any rate, Microsoft CEO Steve Ballmer decided that $55 billion was more than Yahoo! was worth, and he called the deal off.<\/p>\n For both Yahoo! and Microsoft life goes on; today is simply business as usual. For Yahoo! stockholders, however, it\u2019s a different story: after all, no sooner did Microsoft announce that it had called off the deal then Yahoo! shareholders saw their stock value nosedive by 20 percent. The Scripting Guys hate to see anybody suffer a financial setback like that. Therefore, as a public service, the Scripting Guys would like to announce that we are not walking away from the deal that we<\/i> proposed a couple weeks ago<\/b><\/a>: in return for complete and total ownership of the company, we will give every Yahoo! stockholder their very own Dr. Scripto bobblehead doll<\/b><\/a>. (Ed<\/i>itor\u2019s Note: Bobblehead doll<\/i>s<\/i> not included.<\/i>) In fact, not only are we not walking away from that deal, but as a way to sweeten the offer a little, Scripting Guy Peter Costantini has agreed to divvy his salary up among each and every Yahoo! shareholder.<\/p>\n Note<\/b>. Did Scripting Guy Peter Costantini really<\/i> agree to divvy his salary up among each and every Yahoo! shareholder? Well, if you want to get picky about it, no, he didn\u2019t. But that\u2019s only because Peter is an extremely busy guy, and we didn\u2019t want to bother him. And seeing as how he didn\u2019t actually say \u201cno,\u201d well, that must mean that his answer is \u201cyes.\u201d<\/p>\n In case you\u2019re wondering, we don\u2019t know what Peter\u2019s annual salary is. We know how much Peter is worth<\/i>, but that\u2019s a different story.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n But wait; that\u2019s not all. In addition to a bobblehead doll and a cut of Peter Costantini\u2019s salary, each and every Yahoo! shareholder will also receive a script that can modify a CSV file, import that file into Microsoft Excel, and then apply a date-time format to one of the columns. That\u2019s a script that, by itself, has an estimated street value of $62.3 billion, well above the Yahoo! asking price of $55 billion.<\/p>\n What\u2019s that? You don\u2019t think the Scripting Guys even have<\/i> a script that can do all? Well, just feast your eyes on this<\/i> baby:<\/p>\n There appears to be a question in the back: can we explain how this script works? Not really; after all, we\u2019re already losing a lot<\/i> of money as it is by giving the code away. But what the heck; if it will help cinch the deal and get us ownership of Yahoo!, well, then we\u2019ll do it.<\/p>\n As you can see, the script starts out the same way most $62 billion scripts start out: by defining a pair of constants (ForReading and ForWriting), constants we\u2019ll use when we go to open our .CSV file. In fact, we\u2019re going to use one of those constants (ForReading) right now; after we create an instance of the Scripting.FileSystem<\/b>Object<\/b> we use this line of code to open the file C:\\Scripts\\Test.csv for reading:<\/p>\n Ah, good question: what does<\/i> Test.csv look like? Well, as it turns out, it looks something like this:<\/p>\n But it\u2019s not going to look like this for long; before we import this file into Excel we need to extract the second field in each line (in line 1, that\u2019s the value -2000000) and then subtract 1530000 from that number. Believe it or not, if that number is positive (greater than 0) then it can be converted to a date-time value in Excel. That means that, if the number is<\/i> positive, then we\u2019ll tack that value onto the end of the line in the text file; if the value is negative then we\u2019ll add an empty string (\u201c\u201d) to the end of the line.<\/p>\n So how exactly will we do all that? Well, to begin with, we set up a Do Until loop that runs until the file\u2019s AtEndOfStream<\/b> property is True; in other words, we\u2019ll keep reading until there\u2019s nothing left to read. Inside that loop we use the ReadLine<\/b> method to read the first line in the text file, then use VBScript\u2019s Split<\/b> function to split that value (splitting on the comma) into a mini-array named arrItems:<\/p>\n For those of you scoring at home, that makes arrItems equal to this:<\/p>\n Why do we bother splitting our line of text into an array? Well, here\u2019s one reason: that makes it very easy to grab the value of the second field in the line (arrItems(1)<\/b>) and then subtract 1530000 from it, something we do with this line of code:<\/p>\n In turn, that brings us to these two lines of code:<\/p>\n As you know, we just grabbed the first line in our text file and converted it to an array. We now need to add an additional field to the end of that line; in effect, that means we need to add a new item to the array arrItems. How are we going to do that? Well, first we need to determine how many items are currently in the array; that\u2019s what the Ubound<\/b> function tells us. We then use the ReDim Preserve<\/b> statement to redimension the array, setting the new size to the number of items currently in the array (intItems) plus 1.<\/p>\n Now we\u2019re ready to add a new item to the array:<\/p>\n All we\u2019re doing here is checking to see if the value of the variable intDateValue is greater than 0. If it is, we then set the value of our new array item (arrItems(intItems + 1)<\/b>) to, well, the value of intDateValue. If intDateValue isn\u2019t<\/i> greater than 0 then we set the value of this new array items to an empty string.<\/p>\n And you\u2019re right: this isn\u2019t<\/i> particularly sophisticated. But what do expect for a measly $62 billion?<\/p>\n At this point we\u2019re ready to start reversing our steps, sort of. Having started things off by splitting a line of text into an array, we\u2019re now going to use the Join<\/b> method to combine all the items in the array into a single, comma-separated string value:<\/p>\n Note<\/b>. Like we said, though, we aren\u2019t really<\/i> going backwards and undoing what we just did. After all, strNewLine, our new line of text, does<\/i> include the additional array item that we just added.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n After creating the new string value we then add this value (plus a carriage return-linefeed, the VBScript constant vbCrLf<\/b>) to a variable named strNewText:<\/p>\n And then it\u2019s back to the top of the loop, where we repeat the process with the next line in the text file.<\/p>\n After we\u2019ve read and processed each line in the text file we use the Close<\/b> method to close Test.csv, then immediately reopen the file, this time for writing:<\/p>\n Note<\/b>. Yes, we know. But the FileSystemObject won\u2019t let you simultaneously open a file for both reading and writing; it has to be one or the other.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n With Test.csv reopened we use the Write<\/b> method to replace the existing contents with the value of the variable strNewText. And then we once more close Test.csv.<\/p>\n Now it\u2019s time to open \u2013 and format \u2013 this file in Excel. To do that, we first create an instance of the Excel.Application<\/b> object, then set the value of the Visible<\/b> property to True; that gives us a running instance of Excel that we can see on screen. We then use the following line of code, and the Open<\/b> method, to open our .CSV file:<\/p>\n The pre-formatted version of this spreadsheet is going to look like this:<\/p>\n What do we do next? Well, first we execute these two lines of code:<\/p>\n In line 1 we\u2019re simply creating an instance of the Excel Range<\/b> object, a range that encompasses cell F1 (row 1, column 6). In line 2 when then use the EntireColumn<\/b> method to extend that range to take in all of column F.<\/p>\n![\"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> <\/p>\n\n<\/thead>\n\n
\n \n <\/div>\nConst ForReading = 1\nConst ForWriting = 2\n\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.csv\", ForReading)\n\nDo Until objFile.AtEndOfStream\n strLine = objFile.ReadLine\n arrItems = Split(strLine, \",\")\n\n intDateValue = arrItems(1) - 1530000\n intItems = Ubound(arrItems)\n ReDim Preserve arrItems(intItems + 1)\n If intDateValue > 0 Then\n arrItems(intItems + 1) = intDateValue\n Else\n arrItems(intItems + 1) = \"\"\n End If\n strNewLine = Join (arrItems, \",\")\n strNewText = strNewText & strNewLine & vbCrLf\n\nLoop\n\nobjFile.Close\n\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.csv\", ForWriting)\nobjFile.Write strNewText\nobjFile.Close\n\nSet objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\n\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.csv\")\nSet objRange = objExcel.Cells(1, 6)\nSet objRange = objRange.EntireColumn\n\nobjRange.NumberFormat = \"m\/d\/yyyy hh:mm:ss AM\/PM\"\n<\/pre>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.csv\", ForReading)\n<\/pre>\n
200Printer1,-2000000,3600,text,tree\n201kmille5lt1,1570587,1200,text,leaf\n201Printer1,1570761,3600,text,leaf\n201SShrimpWS,1570587,1200,text,leaf\n202Check1,1570454,3600,text,fruit\n202jgrovelt,-2000000,1200,text,fruit\n202LWtextLKE1WS,1570588,1200,text,fruit\n202Printer0,1570732,3600,text,fruit\n202Printer1,1570871,3600,text,fruit\n<\/pre>\n
arrItems = Split(strLine, \",\")\n<\/pre>\n
200Printer1\n-2000000\n3600\nText\ntree\n<\/pre>\n
intDateValue = arrItems(1) - 1530000\n<\/pre>\n
intItems = Ubound(arrItems)\nReDim Preserve arrItems(intItems + 1)\n<\/pre>\n
If intDateValue > 0 Then\n arrItems(intItems + 1) = intDateValue\nElse\n arrItems(intItems + 1) = \"\"\nEnd If\n<\/pre>\n
strNewLine = Join (arrItems, \",\")\n<\/pre>\n
\n<\/thead>\n\n
\n \n <\/div>\nstrNewText = strNewText & strNewLine & vbCrLf\n<\/pre>\n
Set objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.csv\", ForWriting)\n<\/pre>\n
\n<\/thead>\n\n
\n \n <\/div>\nSet objWorkbook = objExcel.Workbooks.Open(\"C:\\Scripts\\Test.csv\")\n<\/pre>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/csv1.jpg\")
<\/p>\nSet objRange = objExcel.Cells(1, 6)\nSet objRange = objRange.EntireColumn\n<\/pre>\n
![\"Microsoft](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/qanda\/csv2.jpg\")
<\/p>\n