locked
How to open excel in Powershell that can intake Japanese text RRS feed

  • General discussion

  • Hi Team,
    I have a code which converts 2 csv files into one excel file with 2 different tabs. Each tab depicts the content of
    the csv files. The problem is with the Japanese characters which appears junk in the final sheet. The CSV files are created using Oracle Spool command. The command prompt is set with "set nls_lang=AMERICAN_AMERICA.AL32UTF8" prior to connecting SQLPLUS

    Code that converts CSV into excel. I was guided by someone that if I use proper characterset while creating the Excel through powershell.

    Function Release-Ref ($ref)
    {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    }

    Function ConvertCSV-ToExcel
    {

    #Requires -version 2.0
    [CmdletBinding(
    SupportsShouldProcess = $True,
    ConfirmImpact = 'low',
    DefaultParameterSetName = 'file'
    )]
    Param (
    [Parameter(
    ValueFromPipeline=$True,
    Position=0,
    Mandatory=$True,
    HelpMessage="Name of CSV/s to import")]
    [ValidateNotNullOrEmpty()]
    [array]$inputfile,
    [Parameter(
    ValueFromPipeline=$False,
    Position=1,
    Mandatory=$True,
    HelpMessage="Name of excel file output")]
    [ValidateNotNullOrEmpty()]
    [string]$output
    )

    Begin {
    #Configure regular expression to match full path of each file
    [regex]$regex = "^\w\:\\"

    #Find the number of CSVs being imported
    $count = ($inputfile.count -1)

    #Create Excel Com Object
    $excel = new-object -com excel.application

    #Disable alerts
    $excel.DisplayAlerts = $False

    #Show Excel application
    $excel.Visible = $False

    #Add workbook
    $workbook = $excel.workbooks.Add()

    #Remove other worksheets
    # $workbook.worksheets.Item(2).delete()
    #After the first worksheet is removed,the next one takes its place
    #$workbook.worksheets.Item(2).delete()

    #Define initial worksheet number
    $i = 1
    }

    Process {
    ForEach ($input in $inputfile) {
    #If more than one file, create another worksheet for each file
    If ($i -gt 1) {
    $workbook.worksheets.Add() | Out-Null
    }
    #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
    $worksheet = $workbook.worksheets.Item(1)
    #$worksheet = $workbook.worksheets.Item(1)
    #Add name of CSV as worksheet name
    $worksheet.name = "$((GCI $input).basename)"

    #Open the CSV file in Excel, must be converted into complete path if no already done
    If ($regex.ismatch($input)) {
    $tempcsv = $excel.Workbooks.Open($input)
    }
    ElseIf ($regex.ismatch("$($input.fullname)")) {
    $tempcsv = $excel.Workbooks.Open("$($input.fullname)")
    }
    Else {
    $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")
    }
    $tempsheet = $tempcsv.Worksheets.Item(1)
    #Copy contents of the CSV file
    $tempSheet.UsedRange.Copy() | Out-Null
    #Paste contents of CSV into existing workbook
    $worksheet.Paste()

    #Close temp workbook
    $tempcsv.close()

    #Select all used cells
    $range = $worksheet.UsedRange

    #Autofit the columns
    $range.EntireColumn.Autofit() | out-null
    $i++
    }
    }

    End {
    #Save spreadsheet
    $workbook.saveas("$pwd\$output")

    Write-Host -Fore Green "File saved to $pwd\$output"

    #Close Excel
    $excel.quit()

    #Release processes for Excel
    $a = Release-Ref($range)
    }
    }

    $month_name = Get-ChildItem $file_temp PIVOT*.csv | Select-Object Name
    $month_name = $month_name -replace "PIVOT", ""
    $month_name = $month_name -replace ".csv", ""
    $month_name = $month_name -replace "@{Name=", ""
    $month_name = $month_name -replace "}", ""

    #powershell -command .\script.ps1
    Get-ChildItem *.csv | ConvertCSV-ToExcel -TEST.XLSX"

    Wednesday, May 10, 2017 5:55 AM

All replies

  • Please post the code correctly.  Use the code posting tool. As posted it is impossible to read or copy.

    To convert a CSV to Excel just open it in Excel. 

    If you export Unicode with the wrong method you will lose all international characters.


    \_(ツ)_/

    Wednesday, May 10, 2017 9:53 AM
  • Hi Team,

    I have a code which converts 2 csv files into one excel file with 2 different tabs. Each tab depicts the content of
    the csv files. The problem is with the Japanese characters which appears junk in the final sheet. The CSV files are created using Oracle Spool command. The command prompt is set with "set nls_lang=AMERICAN_AMERICA.AL32UTF8" prior to connecting SQLPLUS

    Code that converts CSV into excel. I was guided by someone that if I use proper characterset while creating the Excel through powershell.

    Function Release-Ref ($ref)
    {
    ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
    [System.__ComObject]$ref) -gt 0)
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
    }

    Function ConvertCSV-ToExcel
    {

    #Requires -version 2.0
    [CmdletBinding(
    SupportsShouldProcess = $True,
    ConfirmImpact = 'low',
    DefaultParameterSetName = 'file'
    )]
    Param (
    [Parameter(
    ValueFromPipeline=$True,
    Position=0,
    Mandatory=$True,
    HelpMessage="Name of CSV/s to import")]
    [ValidateNotNullOrEmpty()]
    [array]$inputfile,
    [Parameter(
    ValueFromPipeline=$False,
    Position=1,
    Mandatory=$True,
    HelpMessage="Name of excel file output")]
    [ValidateNotNullOrEmpty()]
    [string]$output
    )

    Begin {
    #Configure regular expression to match full path of each file
    [regex]$regex = "^\w\:\\"

    #Find the number of CSVs being imported
    $count = ($inputfile.count -1)

    #Create Excel Com Object
    $excel = new-object -com excel.application

    #Disable alerts
    $excel.DisplayAlerts = $False

    #Show Excel application
    $excel.Visible = $False

    #Add workbook
    $workbook = $excel.workbooks.Add()

    #Remove other worksheets
    # $workbook.worksheets.Item(2).delete()
    #After the first worksheet is removed,the next one takes its place
    #$workbook.worksheets.Item(2).delete()

    #Define initial worksheet number
    $i = 1
    }

    Process {
    ForEach ($input in $inputfile) {
    #If more than one file, create another worksheet for each file
    If ($i -gt 1) {
    $workbook.worksheets.Add() | Out-Null
    }
    #Use the first worksheet in the workbook (also the newest created worksheet is always 1)
    $worksheet = $workbook.worksheets.Item(1)
    #$worksheet = $workbook.worksheets.Item(1)
    #Add name of CSV as worksheet name
    $worksheet.name = "$((GCI $input).basename)"

    #Open the CSV file in Excel, must be converted into complete path if no already done
    If ($regex.ismatch($input)) {
    $tempcsv = $excel.Workbooks.Open($input)
    }
    ElseIf ($regex.ismatch("$($input.fullname)")) {
    $tempcsv = $excel.Workbooks.Open("$($input.fullname)")
    }
    Else {
    $tempcsv = $excel.Workbooks.Open("$($pwd)\$input")
    }
    $tempsheet = $tempcsv.Worksheets.Item(1)
    #Copy contents of the CSV file
    $tempSheet.UsedRange.Copy() | Out-Null
    #Paste contents of CSV into existing workbook
    $worksheet.Paste()

    #Close temp workbook
    $tempcsv.close()

    #Select all used cells
    $range = $worksheet.UsedRange

    #Autofit the columns
    $range.EntireColumn.Autofit() | out-null
    $i++
    }
    }

    End {
    #Save spreadsheet
    $workbook.saveas("$pwd\$output")

    Write-Host -Fore Green "File saved to $pwd\$output"

    #Close Excel
    $excel.quit()

    #Release processes for Excel
    $a = Release-Ref($range)
    }
    }

    $month_name = Get-ChildItem $file_temp PIVOT*.csv | Select-Object Name
    $month_name = $month_name -replace "PIVOT", ""
    $month_name = $month_name -replace ".csv", ""
    $month_name = $month_name -replace "@{Name=", ""
    $month_name = $month_name -replace "}", ""

    #powershell -command .\script.ps1
    Get-ChildItem *.csv | ConvertCSV-ToExcel -TEST.XLSX"

    Thanks

    Thursday, May 11, 2017 6:50 AM
  • The forum has a code posting tool on the edit bar.  Please use it so the code can be copied correctly and so that it is readable.

    If your import doesn't work it is likely because the character sets and encoding are incorrect.  The CSV must be saved as Unicode and it must be opened directly in Excel.  If you have the wrong information when you manually open the CSV in Excel then the CSV file is not being saved correctly.

    Note that it only takes about 5 lines to open a CSV in Excel.  If that file looks wrong after it is opened then your CSV file is likely using the wrong encoding.


    \_(ツ)_/



    • Edited by jrv Thursday, May 11, 2017 10:28 AM
    Thursday, May 11, 2017 10:24 AM