none
Export-excel, losing leading 0 upon export RRS feed

  • Question

  • Background:  I have a PowerShell script that imports a txt file as a csv, selects certain fields, and places them in an object.  It then takes the object and filters for certain results using a where-object statement and places these results in a new object.  I use a foreach statement for each line in that object and replace and edit the fields as desired.  At the end, I use an Export-Excel statement to export the results to an Excel Workbook.

    Problem:  One of the columns contains some numbers that start with 0 as well as some that have a letter in them.  The 0 remains in the data up until it executes the Export-Excel line.  Once this is done, Excel drops off the 0.  These numbers must maintain the 0.

    I have tried inserting an apostrophe to the beginning of those that start with 0, but the apostrophe does not get dropped when exported to excel.  I've also tried inserting a space, using Padleft, and specifying a numberformat of text on the export-excel line.  The numberformat did not work as it changed all the columns and left me with ### in multiple columns.  Is there anyway to keep the leading 0? 

    Here are basics of the code:

    $InputFile=@{}

    $OutputFile ='U:\Desktop\Output.xlsx'

    $TempFile = @{}

    $InputFile =Import-CsvU:\Desktop\File1.txt`

         |Select@{Name="Transaction Code";Expression={$_."TRANCODE"}}, `

          @{Name="Trade Number";Expression={$_."TRANNUMBER"}}, `

          @{Name="Source";Expression={$_."SOURCE"}}

    $TempFile=$InputFile|where-object{$_."Source"-eq"trans"}

    foreach($linein$TempFile)

        {    

         $line."Transaction Code"=$line."Transaction Code"-replace"CAP DG","LTCG"

         $line."Transaction Code"=$line."Transaction Code"-replace"DIV \$\+","DIV"

         $line."Trade Number" = $line."Trade Number".Insert(0,"'") 

        }

    $TempFile | Export-Excel -Path $OutputFile -WorkSheetName 'Output1'

     



    • Edited by clones92 Friday, July 15, 2016 2:09 PM
    Monday, July 11, 2016 9:34 PM

Answers

  • You'll need the developer of your Excel module your question. (This is not a support forum for third-party modules.)


    -- Bill Stewart [Bill_Stewart]

    Tuesday, July 12, 2016 10:07 PM
    Moderator

All replies

  • What is Export-Excel?

    Why not use Export-Csv?


    -- Bill Stewart [Bill_Stewart]

    Monday, July 11, 2016 9:52 PM
    Moderator
  • To maintain the zero you must export to excel as text.  The easiest way to do the is to use an import spec to force an import and use Excel to import the CSV file.

    Please do not post colorized code.  It is impossible to copy and hard to read.  If you rally expect help then please use the correct code posting methods.


    \_(ツ)_/


    • Edited by jrv Monday, July 11, 2016 10:02 PM
    Monday, July 11, 2016 10:00 PM
  • Here:

    if this file " U:\Desktop\File1.txt" is a  csv file then just rename it with a CSV extension and it will open in Excel.  You can then rename, filter and arrange all of the data very easily.


    \_(ツ)_/

    Monday, July 11, 2016 10:06 PM
  • This is only a very small portion of the code.  The whole thing creates 10 different objects containing multiple rows all with different filters and edits and appends each one to the end of an excel file.  At the end, there are two worksheets in an excel workbook.  One has around 3500 rows and the other usually over 6000 rows.  That being said, this is why PowerShell works well.  As of right now, it's done with Excel Macros and takes over 45 minutes to run through all the macros.  The whole script takes 45 seconds, with the only issue being the 0 getting dropped in one of the 20 columns in the worksheet.  
    Tuesday, July 12, 2016 9:45 PM
  • Your updated comments do not really contribute anything useful to the question.

    Export-Excel is not a built-in cmdlet. Export-Csv is a built-in cmdlet, and Excel has no problem opening CSV files.

    As a result, we can only provide general guidance.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, July 12, 2016 9:50 PM
    Moderator
  • You'll need the developer of your Excel module your question. (This is not a support forum for third-party modules.)


    -- Bill Stewart [Bill_Stewart]

    Tuesday, July 12, 2016 10:07 PM
    Moderator
  • This is only a very small portion of the code.  The whole thing creates 10 different objects containing multiple rows all with different filters and edits and appends each one to the end of an excel file.  At the end, there are two worksheets in an excel workbook.  One has around 3500 rows and the other usually over 6000 rows.  That being said, this is why PowerShell works well.  As of right now, it's done with Excel Macros and takes over 45 minutes to run through all the macros.  The whole script takes 45 seconds, with the only issue being the 0 getting dropped in one of the 20 columns in the worksheet.  

    You will have to learn Excel.  Excel and most utilities will stor a number I Excel as a number.  If you want to store it as a string you will have toa lter the utility or the Excel sheet to accept numbers as strings.

    Since we have no idea what you code or spreadsheet are trying to do it is not really possible to be of much help.

    Start by learning Excel.  It will help you to understand your question.


    \_(ツ)_/

    Tuesday, July 12, 2016 10:27 PM
  • It may not be built in, but it is a cmdlet of the Import-Excel module.  I could use Export-Csv if it is possible to 1) append multiple objects containing rows to the same csv, and 2) somehow get two csv's to open in the same workbook.  The end-user requests a one-click process, but maybe it cannot be done.  I do appreciate the responses, don't get me wrong.

    It is not a PowerShell CmdLet or module.  You need to contact the author of the CmdLet for help.

    Your question and response indicate that you have no training in PowerShell.  If you were to gain some training you would be more likely to understand the issues that we are presenting you with.


    \_(ツ)_/

    Tuesday, July 12, 2016 10:30 PM
  • https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/ 

    https://github.com/dfinke/ImportExcel

    With PowerShell version 5.0, I used the Install-Module ImportExcel command.  It is a PowerShell cmdlet from a PowerShell module.  I realize it is not built in, but comes from the PowerShell Gallery and was created by someone else.

    I know how Excel works.  I know that it is taking this in as a number and dropping the 0.  I was wondering if there was anyway to format the single column upon export to not be a number, but rather text.  I will direct my questions elsewhere.

    Thanks for your time.

    Tuesday, July 12, 2016 10:38 PM
  • And the PowerShell Gallery says too use the Q&A page to ask the author to help you.

    Your links are invalid or point to a sier with custom modules You need to question the authors of the module We cannot support all third party modules.

    You also need to learn the basics of both systems before you attempt sophisticated coding.

    If you have a specific question then ask it.  We can answer those.  We cannot give you a tutorial on how to use third party tools.  That is not the purpose of this or most forums.  The more you know the easier it will be to find a solution.


    \_(ツ)_/

    Tuesday, July 12, 2016 10:57 PM
  • I have directed my question to the author. Which link is invalid?
    Tuesday, July 12, 2016 11:08 PM