{"id":71123,"date":"2004-10-28T14:34:00","date_gmt":"2004-10-28T14:34:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2004\/10\/28\/how-can-i-create-a-csv-file\/"},"modified":"2004-10-28T14:34:00","modified_gmt":"2004-10-28T14:34:00","slug":"how-can-i-create-a-csv-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-create-a-csv-file\/","title":{"rendered":"How Can I Create a CSV File?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I create a CSV file?

— LF<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, LF. For those of you who aren\u2019t up on your acronyms, CSV is short for comma-separated-values file, a text file in which individual elements are separated by commas. For example, say you have a CSV file consisting of user first names, last names, and job titles; that file might look something like this:<\/P>

Ken,Myer,Accountant\nPilar,Ackerman,Vice-President\nCarol,Philips,Research Specialist\n<\/PRE>\n
Incidentally, at Microsoft we love acronyms. The other day, for example, the Scripting Guys were sent a three-sentence product description that contained 11 acronyms. Our personal favorite – used extensively around here – is OOF, which is somehow short for O<\/B>ut O<\/B>f O<\/B>ffice.<\/P>\n
No wonder we felt the need to add a spellchecker to all our products.<\/P>\n
But back to your question. How can you create a CSV file all your own? That\u2019s easy: just use the FileSystemObject. After all, that\u2019s what it\u2019s here for.<\/P>\n
Let\u2019s take a look at a very simple demonstration script, then we\u2019ll take a look at a more practical example. Here\u2019s a sample script that writes the string A,B,C to a text file named Test.csv:<\/P>
Const ForWriting = 2<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objLogFile = objFSO.CreateTextFile(“test.csv”, _ \n    ForWriting, True)<\/p>\n
objLogFile.Write “A,” \nobjLogFile.Write “B,” \nobjLogFile.Write “C”\nobjLogFile.Writeline<\/p>\n
objLogFile.Close\n<\/PRE>\n
We start out by defining a constant named ForWriting and setting the value to 2; any time you use the FileSystemObject, you need to use the appropriate constant, depending on whether you want to read from, write to, or append a file. We create an instance of the FileSystemObject, then use the CreateTextFile method to create a new text file named Test.cvs. (Notice that we did not specify a path; that means that the text file will be created in the same folder as the script. Had we wanted to, we could have specified a complete path, like C:\\Scripts\\Logfiles\\Test.csv.)<\/P>\n
When we call the CreateTextFile method, we simultaneously create an object reference to the new file; in our script, we named that object reference objLogFile (although we could have called it anything we wanted). With the object reference in hand, we can then use the Write method to write data to the file. Notice that we begin by writing the string A,<\/B> (and, yes, we must manually include the commas when creating a CSV file). The Write method writes the specified data (in this case A,<\/B>), and then leaves the cursor in place. Because of that, when we next call this method the string B,<\/B> is tacked on right next to the A,<\/B>. As a result, our text file looks like this:<\/P>
A,B,\n<\/PRE>\n
We call the Write method a third time, and write C<\/B>. Because this marks the end of the line, we don\u2019t append a comma after it; instead, we call the WriteLine method, which is equivalent to hitting ENTER on the keyboard. That gives us a text file that looks like this:<\/P>
A,B,C\n<\/PRE>\n
Note that there are no spaces between commas and the start of the next item. When one item ends, the next begins right away.<\/P>\n
Now let\u2019s take a look at a more practical example. This script uses WMI to retrieve service information, then uses the FileSystemObject to write that information to a text file named Service_List.csv:<\/P>
Const ForAppending = 2<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objLogFile = objFSO.CreateTextFile(“service_list.csv”, _ \n    ForWriting, True)<\/p>\n
strComputer = “.”\nSet objWMIService = GetObject(“winmgmts:” _\n    & “{impersonationLevel=impersonate}!\\\\” & strComputer & “\\root\\cimv2”)\nSet colListOfServices = objWMIService.ExecQuery(“Select * from Win32_Service”)\nFor Each objService in colListOfServices\n    objLogFile.Write objService.Name & “,” \n    objLogFile.Write objService.StartMode & “,” \n    objLogFile.Write objService.State \n    objLogFile.Writeline\nNext<\/p>\n
objLogFile.Close\n<\/PRE>\n
As you can see, we use the same exact technique used in our demonstration script. The biggest difference is that we aren\u2019t using hard-coded values like A, B, and C. Instead, we\u2019re using variables like objService.Name. That\u2019s not a problem, we just use the variable as the parameter to the Write method. But because these are variables, we can\u2019t enclose them and the following comma in double quotes. A line of code like this will not<\/I> work:<\/P>
objLogFile.Write “objService.Name,”\n<\/PRE>\n
Instead, we specify the variable (objService.Name), and then use the ampersand to tack a comma (\u201c,\u201d) to the end. We do the same thing for the StartMode but not for State; that\u2019s because State is the last item on each line. Consequently, we use WriteLine to press ENTER and start a new line in the file (the same way we sued WriteLine after the letter C in our first example). Because we do this within a For Each loop, we\u2019ll end up writing this information for each service installed on the computer.<\/P>\n
The net result is a text file that looks like this:<\/P>
Alerter,Manual,Stopped\nALG,Manual,Stopped\nAppMgmt,Manual,Stopped\naspnet_admin,Auto,Stopped\n<\/PRE>\n
Although we could have cut down the number of lines of code slightly (by concatenating all our variables and what-not) writing the code in this fashion makes it pretty easy for you to see what\u2019s going on. It also makes it easy for you to add new items to each line. Want to include the service PathName? Then just add a line of code like this:<\/P>
objLogFile.Write objService.PathName & “,”\n<\/PRE>\n
One more thing: When you\u2019re working with WMI, the preceding script will likely handle all your needs. When working with other scripting technologies, however, you might encounter a problem with data that includes commas. For example, suppose you have a text file of users, their office addresses, and their job titles. One user has an address of 2049<\/B>, but another has an address of 2050, Suite A<\/B>. Your subsequent text file looks like this:<\/P>
Ken,Myer,2049,Accountant\nPilar,Ackerman,2050,Suite A,Vice-President\n<\/PRE>\n
Yikes! We have a problem: our first line contains four fields (remember commas indicate the end of one field and the beginning of the next): Ken \/ <\/B>Myer \/ <\/B>2049 \/ <\/B>Accountant. Unfortunately, because of the comma in the address itself, our second line has five<\/I> fields: Pilar \/ <\/B>Ackerman \/ <\/B>2050 \/ <\/B>Suite A \/ <\/B>Vice-President. Uh-oh \u2026.<\/P>\n
How do you deal with embedded commas like this? The secret is to surround each field with double quotes; if your text file looks like this, the embedded commas are ignored:<\/P>
“Ken”,”Myer”,”2049″,”Accountant”\n“Pilar”,”Ackerman”,”2050,Suite A”,”Vice-President”\n<\/PRE>\n
And how do you surround your fields with double quotes. Here\u2019s another sample script that uses the function Chr(34) to write a double quote mark, writes a service property (such as objService.Name), uses Chr(34) to write another double quote mark, and only then<\/I> tacks a comma on the end.<\/P>
Const ForWriting = 2<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objLogFile = objFSO.CreateTextFile(“service_list.csv”, _ \n    ForAppending, True)<\/p>\n
strComputer = “.”\nSet objWMIService = GetObject(“winmgmts:” _\n    & “{impersonationLevel=impersonate}!\\\\” & strComputer & “\\root\\cimv2”)\nSet colListOfServices = objWMIService.ExecQuery(“Select * from Win32_Service”)\nFor Each objService in colListOfServices\n    objLogFile.Write chr(34) & objService.Name & chr(34) & “,” \n    objLogFile.Write chr(34) & objService.StartMode & chr(34) & “,” \n    objLogFile.Write chr(34) & objService.State  & chr(34)\n    objLogFile.Writeline\nNext<\/p>\n
objLogFile.Close\n<\/PRE>\n
For more information, check out this portion<\/B><\/A> of the Microsoft Windows 2000 Scripting Guide. And we already know your next<\/I> question: how do you read<\/I> a CSV file using a script? For the answer, take a look at this Scripting Clinic column<\/B><\/A>on reading text files using ADO.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I create a CSV file?— LF Hey, LF. For those of you who aren\u2019t up on your acronyms, CSV is short for comma-separated-values file, a text file in which individual elements are separated by commas. For example, say you have a CSV file consisting of user first names, last names, […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[169,40,3,4,5],"class_list":["post-71123","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-filesystemobject","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I create a CSV file?— LF Hey, LF. For those of you who aren\u2019t up on your acronyms, CSV is short for comma-separated-values file, a text file in which individual elements are separated by commas. For example, say you have a CSV file consisting of user first names, last names, […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/71123","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=71123"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/71123\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=71123"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=71123"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=71123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}