Convert .txt file to .xlsx file

PowerShell Help
1

Hello

I have a script to convert .txt file to .xlsx file.

As I have , on the 1st column, 18 digits, Excel tranform it to 3,63121E+17 instead of 363121123465789456

How can I make to have the correct digits using a powershell command (no external SW or Excel manipulation)

Here my script:

$SourcePath = “C:\Programs\Test.txt”
$DestinationPath = “C:\Programs”
$deleimter= “`;”
$SourceTxt = Get-Content $SourcePath
$xlsxFile = $DestinationPath + “.xlsx”
if (Test-Path ($xlsxFile))
{
Remove-Item $xlsxFile
}
$SourceTxt >> $txtFile

[threading.thread]::CurrentThread.CurrentCulture = ‘en-US’
$xl=New-Object -ComObject “Excel.Application”
$wb=$xl.Workbooks.Add()
$ws=$wb.ActiveSheet
$xl.Visible=$True
$cells=$ws.Cells
$Content = Get-Content $SourcePath
$numOfRows = $Content.Length
$numOfColumns = $Content[0].split($deleimter).Count
for ($i=0; $i -lt $numOfRows ;$i++)
{
$rowData = $Content[$i].split($deleimter)
for ($j=0; $j -lt $numOfColumns; $j++)
{
$cellData = $rowData[$j]
$cells.item($i+1,$j+1) = $cellData
}
}
#Apply some Format for Excel header

$xl.Cells.EntireColumn.AutoFit()
$xl.Cells.EntireColumn.AutoFilter()

#Save Sheet

$wb.Save()
$wb.Close()
$xl.Quit()

Clean up

Many Thanks.

2

Hi, welcome to the forum :wave:

Before we start, when posting code in the forum, please can you use the preformatted text </> button. It really helps us with readability, and copying and pasting your code (we don’t have to faff about replacing curly quote marks to get things working). If you can’t see the </> in your toolbar, you will find it under the gear icon.

How to format code on PowerShell.org

To disable the exponent notation, you would normally just change the NumberFormat property of the cell. Around line 34 of your code you would add:

$ws.Columns(1).NumberFormat = '#'

However, this won’t work for you because Excel will handle only the first 15 digits, so for your 18 digit numbers you’ll end up with 363121123465789000 if you try to use a number format. In fact, you’ll also get that if you try to convert the format to text after you’ve added the value to the cell.

What you’ll need to do is treat the number as text when you insert the value. You can do this by changing line 27 in your code to:

$cells.item($i+1,$j+1) = "'$cellData"

The initial apostrophe tells Excel to treat that value as text.

3

Hello,

Sorry for the wrong layout, next post I will try to think about it.
Many thanks for your help, now it works fine :smile:
Do you know a web site where we can find commands to work with Excel files and PowerShell ?

Thanks !

4

Glad it’s working :slight_smile:

By far the best solution is to ditch the COM object approach entirely, and use the ImportExcel module.

5

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.