Japan characters Junk

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Rahul Rahul 1 week, 6 days ago.

  • Author
    Posts
  • #70442
    Profile photo of Rahul
    Rahul
    Participant

    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
    Rahul Kumar

  • #70468
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    It's hard to assist if you don't show the actual issue you are seeing. What is the goal of this code? If you want data from a database into Excel, you can just add the datasource in excel. Another option is getting the data directly in Powershell from the data source versus SQLPLUS > CSV > Powershell > Excel.

    • #70516
      Profile photo of Rahul
      Rahul
      Participant

      Thanks for your reply Rob!!

      I understand your point, though the problem here is that this is an already deployed code.

      This is how my Japanese characters appear in my final excel sheet. Rest of the data are absolutely okay!!

      手動スケジュール:

You must be logged in to reply to this topic.