Excel: Import a CSV file

WSH scripts provides a simple interface to access Office application like Microsoft Excel from a script. I have shown the details within the previous sample. But there is one problem, if you try to import a Comma Separated Value (CSV) file which doesn't use semicolons as separators. In this case the CSV file's content is imported in the first column of the first workbook.

Note: This behavior was pointed out by Dat Nguyen from Montreal, Canada, during our discussion about WSH features. Later on I found the reason: The separator character is user defined and depend on the operating system (a German Windows uses different characters than a US Windows).

My first attempt to use named parameters defined for the Excel Open method failed (WSH script engines don't support named parameters). I was able to pass the parameters in their default order, but the properties to set the separator value causes nothing. So I decided to develope another solution. The ideas is rather simple: I invoke Excel, create an empty Workbook and import the CSV file into the first Worksheet. During this import we can set several properties for the Import wizard. Further details may be found in the following listing. The CSV file Names.csv must be located in the same folder as the VBScript file. The sample requires Microsoft Excel 2000.

'************************************************
' File:     ExcelCSV2.vbs (WSH sample in VBScript) 
' Author:   (c) Günter Born (last edit 5-April-1999)
'
' A modified VBScript sample which demonstrates how to
' import a CSV file (with several separators like commas,
' semicolon, etc.) into a new sheet. We use the import wizard
' to avoid the trouble that CSV files with comma delimiters
' are getting read into the first column (a little bit tricky,
' but I was not able to get Open method to accept the format
' properties - they was ignored).
'
' Well, here we go: 
' Launch Excel, add a worksheet, import the CSV file
' select the current worksheet, read some cell 
' values back into the script, print the CSV content
' and terminate.
'
' Names.csv is a simple text file created with an editor
' using the following structure:
'
' Name:STRING,ID:INT
' Born,123
' Miller,728
' Myers,1334
' McFyer,789
' Thommy,345
'
' Here I have used a comma as a field separator. The CSV file Names.csv
' must be located in the same folder as the script for this example.
'
' In no way shall the author be liable for any
' losses or damages resulting from the use of this
' program. Use AS-IS at your own risk.
'
' The code is the property of the author. You may
' use the code and modify it, as far as this header
' remains intact. Further updates and other samples
' may be found on my site mentioned above. 
' This sample was derived from samples shown in my book:
' Inside Windows Scripting Host, MS Press Germany
'
' Check out Born's Windows Script Host Bazaar at:
' http://www.borncity.de
''************************************************
Option Explicit
Const vbNormal = 1         ' window style

DIM objXL, objWb, objR, objTab     ' Excel object variables
DIM Title, Text, tmp, i, j, file, name

Title = "WSH sample - by G. Born"

' here you may set the name of the file to be imported 
file = "Names.csv"    ' must be located in the script folder

' create an Excel object reference
Set objXL = WScript.CreateObject ("Excel.Application")

' set the Excel window properties (not absolutely necessary)
objXL.WindowState = vbNormal ' Normal
objXL.Height = 300           ' height
objXL.Width = 400            ' width
objXL.Left = 40              ' X-Position
objXL.Top = 20               ' Y-Position
objXL.Visible = true         ' show window

' Create new Workbook (needed for import the CSV file=
Set objWb = objXl.WorkBooks.Add 

' Get the first loaded worksheet object of the current workbook
Set objWb = objXL.ActiveWorkBook.WorkSheets(1)
objWb.Activate               ' not absolutely necessary (for CSV)
' Now invoke the import wizard

Set objTab = objWb.QueryTables.Add ("TEXT;"+GetPath + file, objWb.Range("A1"))

' here comes the mumbo jumbo to set all the properties for the wizard
' Oh Microsoft, how do I wish to has a With feature or a possibility to
' pass named arguments to methods .... 
  objTab.Name = "Names"
  objTab.FieldNames = True
  objTab.RowNumbers = False
  objTab.FillAdjacentFormulas = False
  objTab.PreserveFormatting = True
  objTab.RefreshOnFileOpen = False
  objTab.RefreshStyle = 1  'xlInsertDeleteCells
  objTab.SavePassword = False
  objTab.SaveData = True
  objTab.AdjustColumnWidth = True
  objTab.RefreshPeriod = 0
  objTab.TextFilePromptOnRefresh = False
  objTab.TextFilePlatform = 2  'xlWindows
  objTab.TextFileStartRow = 1
  objTab.TextFileParseType = 1  'xlDelimited
  objTab.TextFileTextQualifier = -4142 ' xlTextQualifierNone
  objTab.TextFileConsecutiveDelimiter = False
  objTab.TextFileTabDelimiter = True               ' ### my delimiters 
  objTab.TextFileSemicolonDelimiter = True
  objTab.TextFileCommaDelimiter = True
  objTab.TextFileSpaceDelimiter = False
  objTab.TextFileColumnDataTypes = Array(1, 1)
  objTab.Refresh False

WScript.Echo "We have loaded the worksheet"

' demonstrate how to read the column header values
Text = "Worksheet " & objWb.name & vbCRLF
Text = Text & "Column titles" & vbCRLF
Text = Text & CStr(objWb.Cells(1, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(1, 2).Value) & vbCRLF

' show some cell values (using the "hard coded method")
Text = Text & CStr(objWb.Cells(2, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(2, 2).Value) & vbCRLF
Text = Text & CStr(objWb.Cells(3, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(3, 2).Value) & vbCRLF
Text = Text & CStr(objWb.Cells(4, 1).Value) & vbTab
Text = Text & CStr(objWb.Cells(4, 2).Value) & vbCRLF

' Show results
MsgBox Text, vbOkOnly+ vbInformation, Title

objXl.ActiveSheet.PrintOut    ' print Worksheet

WScript.Echo "We are printing, close after printing"

' I like to prevent the warning message about the unsaved data
' during closing Excel 
objXL.DisplayAlerts = False  ' prevent all message boxes

objXl.Quit                  ' Quit Excel 
Set objXL = Nothing

'##########################
Function GetPath
' Retrieve the script path
 DIM path
 path = WScript.ScriptFullName  ' Script name
 GetPath = Left(path, InstrRev(path, "\"))
End Function
'*** End

Additional information about the WSH may be found in my German WSH book Inside Windows Script Host, published by Microsoft Press Germany.

Back

(c) G. Born