Wednesday, 15 July 2015

VBScript: How to Create and Write text in CSV file

You might have come across the need of export text content to CSV file in vbscript. We can create and write text content into CSV file using FileSystemObject. The CSV is nothing but the comma-separated-values, a text file in which individual values are separated by commas (,).

Create and Write text content to csv file includes following steps:

- Create new text file with .csv extention using CreateTextFile function.
- Write colunm headers as comma-separated-values in first line of the text file.
- Write data as comma-separated-values line-by-line.
- We use WriteLine method to move next line in text file, which is equivalent to hitting ENTER on the keyboard.
Dim csvFilePath,csvColumns
Const ForWriting = 2

' Create new CSV file 
csvFilePath ="C:\TestExport.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCSVFile = objFSO.CreateTextFile(csvFilePath, _ 
    ForWriting, True)

' Write comma delimited list of columns in new CSV file.
csvColumns = "Col1,Col2,Col3"
objCSVFile.Write csvColumns

' Write test values as comma-separated in new CSV file.
 For i = 0 to 5 
    objCSVFile.Write chr(34) & "Value1" & chr(34) & ","
    objCSVFile.Write chr(34) & "Value2" & chr(34) & ","
    objCSVFile.Write chr(34) & "Value3" & chr(34) & ""
The symbol chr(34) is nothing but double quote ("), which is used to surround your fields with double quotes to preserve csv special characters.

Note: Since we are creating the text file with .csv extension instead of actual csv file, if you open the csv file using MS Excel, the line should comes in a single cell, to fix this issue, copy the content of the csv file and paste in new text file and save it as new .csv file.


No comments:

Post a Comment