Japan characters Junk

PowerShell Help
1

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

2

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.

3

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!!

手動スケジュール: