Exporting CSV file to XLS

This topic contains 7 replies, has 4 voices, and was last updated by Profile photo of lyn wood lyn wood 1 week, 3 days ago.

  • Author
  • #54989
    Profile photo of lyn wood
    lyn wood

    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

    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

  • #54992

    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.

  • #54997
    Profile photo of Rob Simmers
    Rob Simmers

    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"
    $wb = $xl.Workbooks.Open("C:\Users\rob\desktop\archive\test.csv")
    $wb.SaveAs("C:\Users\rob\desktop\archive\test.xls", 56)

    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

  • #55000
    Profile photo of Olaf Soyk
    Olaf Soyk
  • #55212
    Profile photo of lyn wood
    lyn wood

    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.Saved = $True

    Thanks for any help

    PS Olaf is the script display correctly this time.

    • #55215
      Profile photo of Olaf Soyk
      Olaf Soyk


      absolutely perfect. I'm proud of you! 😉

  • #55236
    Profile photo of Rob Simmers
    Rob Simmers

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

    $xl = New-Object -ComObject "Excel.Application"
    $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
    $wb.SaveAs("C:\Users\rob\desktop\archive\test.xls", 1)

    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: http://superuser.com/questions/307496/how-can-i-set-excel-to-always-import-all-columns-of-csv-files-as-text

    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
    Remove-Item variable:arrFormats
  • #55258
    Profile photo of lyn wood
    lyn wood

    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

You must be logged in to reply to this topic.