Hi,

I use this code (found somewhere) to convert csv to xls. It works perfectly when there is no national characters.

But when I am trying to convert csv file which is encoded in ISO 2859-2 it results in some text errors (like Waciciel or Sd). When I am doing it manually from excel (Data -> from text -> set encoding 2859-2 ...) everything is ok

Any idea how to set it up the same way in this script?

### Set input and output path
$inputCSV = "C:\tmp\test.CSV"
$outputXLSX = "C:\tmp\test.xls"

### Create a new Excel Workbook with one empty sheet which name is the file
$excel = New-Object -ComObject excel.application
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)
$worksheet.name = "$((GCI $inputCSV).basename)"

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data  From Text" in Excel
$TxtConnector = ("TEXT;" + $inputCSV)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)

### Set the delimiter ( , or ; ) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
### this options don't seems necessary
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
### change decimal separator as "." (can be ",")
$query.TextFileDecimalSeparator = "."
$query.AdjustColumnWidth = 1

### Execute & delete the import query
# using my_output avoid having an outuput that display true
$my_output = $query.Refresh()
$query.Delete()

### Save & close the Workbook as XLS.
$Workbook.SaveAs($outputXLSX,56)
$excel.Quit()

Post you issue with the author of the script for assistance.

Here is an example of setting the encoding in VBA:

Sub ImportEncoded()
'
' ImportEncoded Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\jvierra\Documents\savertf2.txt", Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "savertf2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 1253
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Application.Goto Reference:="ImportEncoded"
End Sub

You would need to set al of the properties to specify a CSV file as TEXT.

Here is a VBA specific for CSV files.

Set qry = ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\test\dir2.csv", Destination:=Range("$B$9"))
With qry
    .CommandType = 0
    .Name = "dir2"
    .FieldNames = True
    .PreserveFormatting = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePlatform = 28591
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileCommaDelimiter = True
    .TextFileTrailingMinusNumbers = True
    .Refresh
End With

This line sets the encoding:     .TextFilePlatform = 28591

You have to find the encoding for ISO2859-2.  It should be 28592 but check.

It worked.

Thank you very much.