Exporting CSV file to XLS

PowerShell Help
1

To make my previous script even more useful I need the final output files to be in a .xls format(yes I know that is not the current excel format but it is the only one that will upload correctly to the site that it is needed for) I can get a file out put that has the data but it is not in the correct rows and columns and I also have some numbers with a leading 0 and tags that look like dates that need to be preserved as they are. I think that I may need to convert my file before I export it as a .xls so this does not happen, but at this stage I am not entirely sure.

Data when I display it at the cmd prompt

$Info
Student ID : 987654
First Name : Fred
Surname : Brown
Unique ID : 0123456
Tag : 3/4/5

Student ID : 876543
First Name : John
Surname : Brown
Unique ID : 234567
Tag : 3k

Do I need to convert my current file and what is the proper export cmd?

Thanks for any suggestions

2

If you’re doing this for a single file it’s not worth the pain of scripting it. The excellent Excel module which I’d usually recommend won’t preserve the formatting that you require and, as far as I know, only exports to XLSX.

Your best bet is to open a blank Excel workbook, click the Data tab, then use the From Text wizard to import your CSV file. On the last page of the wizard, change the column data format from General to Text (either for all columns or on a per column basis) and this will preserve your values.

3

As long as you are not trying to do a lot of formatting and just want to simply save the file as another format, you can try something like this:

$xl = New-Object -ComObject "Excel.Application"
$xl.Visible=$False
$wb = $xl.Workbooks.Open("C:\Users\rob\desktop\archive\test.csv")
$wb.SaveAs("C:\Users\rob\desktop\archive\test.xls", 56)
$xl.Quit()

This is using the Excel COM object, which isn’t very efficient. You can also look at generating the xls using xml methods: https://gallery.technet.microsoft.com/office/Export-XLSX-PowerShell-f2f0c035

4

Lyn,

the Powershell Excel Module could be helpful for you:

https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/

5

Hi Rob,

I found a variant of your recommended script that works with Powershell version 4 and now I need to force all the cells to be in text formatting so it does not change the data on me which at the moment makes it unusable.

$xl = new-object -comobject excel.application
$xl.visible = $true
$Workbook = $xl.workbooks.open(“c:\powershell\161011trangieOldStu.csv”)
$Worksheets = $Workbooks.worksheets
$Workbook.SaveAs("c:\powershell\161010trangieCurrentStu.xls”,1)
$Workbook.Saved = $True
$xl.Quit()

Thanks for any help

PS Olaf is the script display correctly this time.

6

Lyn,

absolutely perfect. I’m proud of you! :wink:

7

There are a couple of ways you can try. I tried this:

$xl = New-Object -ComObject "Excel.Application"
$xl.Visible=$true
$wb = $xl.Workbooks.Open("C:\Users\rob\desktop\archive\test.csv")
#Select first worksheet
$ws = $wb.worksheets.Item(1)
#Create a range for everything used in Excel
$usedRange = $ws.UsedRange
#Remove all formatting
$usedRange.ClearFormats()
$wb.SaveAs("C:\Users\rob\desktop\archive\test.xls", 1)
$xl.Quit()

I was using some dates and when formatting was cleared, it jacked them all up. My next thought was seeing if the Open method had a switch or modifier to specify NOT to format and didn’t see anything. I came across this post: formatting - How can I set Excel to always import all columns of CSV files as Text? - Super User

The only thing that was odd about this post was they wanted to call the PS1 with BAT file, so you can just specify the file versus a script to call a script:

$csv = Get-Item "C:\MyCSV"

$excel = New-Object -ComObject excel.application 
$excel.visible = $true
$workbook = $excel.Workbooks.Add()
$worksheet = $workbook.worksheets.Item(1)
$worksheet.Name = $csv.basename

$arrFormats = ,2 * $worksheet.Cells.Columns.Count

$TxtConnector = ("TEXT;" + $csv.fullname)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))

$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $Excel.Application.International(5)
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = $arrFormats
$query.AdjustColumnWidth = 1
$query.Refresh()
$query.Delete()

Remove-Item variable:arrFormats

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.GC]::Collect() 
[System.GC]::WaitForPendingFinalizers()
8

Hi Rob,

I tried the second script and it works like a charm, the only thing I had to change was to save the file which now makes my script complete.

Thank you Rob and everyone else who has contributed to me expanding my knowledge in powershell