none
Powershell :: Importing XLSX file issues. Data not reading correctly RRS feed

  • Question

  • Hello my fellow scripters,

    There are 2 issues I am struggling with

    I have a file that I need to read with Poweshell and process the data.  Sounds easy enough, but it doesnt seem to be working all that well.

    When I use Import-Csv I get a screen full of crap.

    z�Ş$]Ԭu�i����2���Y��&�f�$��អ޶�K�/R{f�ߓ�ʎ�3I�|�9���7����I��T��P�Tq� �k�0�����DvI�? �C�����g�/��w��E�u...                                                                    
    �_���v` $��Wq���]�GT�v�k�y��=��A�������]�ً�S�ŭ�a�F�Ɋ��J=@����ms ��...                                                                                                      
    rגi��a�g{�E���;�Hlg2��"�LD�V<B 3�ʽ芩                                                                                                                                        
    ��� �'�!���}���h�d�f��S�P�c[-�{�A� IfP�x4���J\�b

    If I manually open the file and save as a CSV and then read, it seems to be working.

    The second issue is that the file is sent with data in the first row, then the headers, then the data.  I would like to remove the first row and only keep the headers and data unless I can parse through the data without removing the first row.

    I was told that there is no other way to get the file to me, so its all I have to work with.

    The server where this is running from will not have Excel installed.  I am not a noob by any means, but I think this is beyond my PS experience working with Excel.

    Any help would be great!

    Thanks

    EFD


    If you're going to answer my question with pretentiousness or disrespect, move on to another question. I don't need your help. Thank you

    Tuesday, June 3, 2014 1:38 PM

Answers

  • Hi EFD,

    without Excel at hand, you'll need to use some DB libraries (I think it was OleDB, but gotta look that up later unless someone else has the answer at hand) to access it (lots more complicated than just simply saving as Csv). Can't open an xslx directly with Import-Csv either.

    The easiest way for you would be to simply go ahead and copy it to a desktop that does have Excel, open it, remove the first line, save as Csv and Import it (you can automate that with Powershell fairly easily using ComObjects from the client side or via remoting).

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, June 3, 2014 1:51 PM

All replies

  • Hi EFD,

    without Excel at hand, you'll need to use some DB libraries (I think it was OleDB, but gotta look that up later unless someone else has the answer at hand) to access it (lots more complicated than just simply saving as Csv). Can't open an xslx directly with Import-Csv either.

    The easiest way for you would be to simply go ahead and copy it to a desktop that does have Excel, open it, remove the first line, save as Csv and Import it (you can automate that with Powershell fairly easily using ComObjects from the client side or via remoting).

    Cheers,
    Fred


    There's no place like 127.0.0.1

    Tuesday, June 3, 2014 1:51 PM
  • Thanks Fred, I was just thinking that I am going to probably see if I can get Excel installed on one of the servers.  My next hurdle is removing that pesky first line.

    I appreciate your help

    EFD


    If you're going to answer my question with pretentiousness or disrespect, move on to another question. I don't need your help. Thank you

    Tuesday, June 3, 2014 1:53 PM
  • Assuming you do get Excel installed, or choose to do this on your desktop with Excel installed, this bit of code will open the file, remove the first line, and save it as a CSV:

    $excel = New-Object -ComObject Excel.Application
    $workbook = $excel.Workbooks.open("c:\temp\test.xlsx")
    $worksheet = $workbook.Worksheets.Item(1)
    $worksheet.Activate() | Out-Null
    $worksheet.rows.item(1).delete() | Out-Null
    $worksheet.SaveAs("c:\temp\test.csv",6)
    $excel.quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null
    

    This also assumes you want to work with the first worksheet in the workbook.


    I hope this post has helped!

    Tuesday, June 3, 2014 2:32 PM
  • Great thanks!

    This is what I tested and seemed to work pretty well

      $e = New-Object -com "Excel.Application"
      $e.Visible = $false
      $e.DisplayAlerts = $false
      $wb = $e.workbooks.open($strPath)
      $s = $wb.ActiveSheet
      $t = 6
    
      $r = $s.Cells.Item(1,1).EntireRow
      $r.Delete()
      $s.SaveAs("C:\TEST3.csv,$type)
      $wb.Close()


    If you're going to answer my question with pretentiousness or disrespect, move on to another question. I don't need your help. Thank you

    Tuesday, June 3, 2014 2:36 PM
  • If you're going to answer my question with pretentiousness or disrespect, move on to another question. I don't need your help. Thank you

    Agree that both questioners and respondents should be courteous. I would courteously suggest that your signature can be construed as discourteous.


    -- Bill Stewart [Bill_Stewart]

    Tuesday, June 3, 2014 2:46 PM
    Moderator
  • Good afternoon Bill,

    Thank you for your comment.  Although you are rightfully entitled to your opinion, my signature is not an invitation for criticism.  If you're sole purpose is to visit a technical forum simply to respond to someone's signature I feel that your use of time needs to be evaluated.  We are all here for the same reason, to get answers and to get help with technical issues that may not be resolved in our own efforts.

    Remember, SOT.

    Thank you and have a wonderful day.

    EFD


    If you're going to answer my question with pretentiousness or disrespect, move on to another question. I don't need your help. Thank you

    Tuesday, June 3, 2014 3:45 PM
  • Good afternoon Bill,

    Thank you for your comment.  Although you are rightfully entitled to your opinion, my signature is not an invitation for criticism.  If you're sole purpose is to visit a technical forum simply to respond to someone's signature I feel that your use of time needs to be evaluated.  We are all here for the same reason, to get answers and to get help with technical issues that may not be resolved in our own efforts.

    Remember, SOT.

    Thank you and have a wonderful day.

    EFD


    If you're going to answer my question with pretentiousness or disrespect, move on to another question. I don't need your help. Thank you

    You're missing some important information, as a current forum bug is hiding forum roles.

    Bill isn't some random guy sniping at you, he's a moderator here.

    Plus, I happen agree with him. There's a reason I skipped your thread.

    Also, we're not all here for the same reason. You're here to get your free answer and then you're going to leave. We're only here to help people like you, so the least you can do is not pretend to be better than us.

    Enough said, I'm leaving this thread now. I'm not going to get into a debate.


    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Tuesday, June 3, 2014 3:49 PM
  • I think the First Amendment covers all of this and we all have the right to execute that as we see fit.

    You are the type of person that my signature is directed at, not Bill.

    If Bill is a moderator, then job well done!  It was not a personal attack, strictly opinionated.

    Now if I were you, I would get back to work.  Its really not fair that there are so many people out of work and you spend the company's time and money responding to threads like this.  I'm actually off today, but continue to progress through my projects for the ultimate goal of driving my company's goals and missions.

    Again, thank you for your post.  It was very enlightening.  I have added a few comments to my signature in hopes that it will give you warm fuzzies the next time we meet.

    EFD



    Warm Fuzzies!


    • Edited by JL-4 Tuesday, June 3, 2014 4:15 PM
    Tuesday, June 3, 2014 3:57 PM
  • I respectfully suggest that it is (at best) a bit presumptuous to assume that your time is more valuable than everyone else's. I was just pointing out something about your signature that you may not have considered. It is probably in your own best interest to revise it a bit, to avoid putting off people (like Mike) who might want to answer your question but skip it because of the signature.

    -- Bill Stewart [Bill_Stewart]

    Tuesday, June 3, 2014 3:59 PM
    Moderator
  • That's great, good for you.

    Enjoy your burned bridge, <EDIT: Snip the unprofessionalism>.


    Don't retire TechNet! - (Don't give up yet - 12,950+ strong and growing)

    Tuesday, June 3, 2014 3:59 PM
  • Just a heads up.  The code has a bug and will not give you a CSV file.

    Here is a fixed version:

    $xlType=6
    $xl=New-Object -com Excel.Application
    $xl.DisplayAlerts = $false
    $wb=$xl.workbooks.open($strPath)
    $xl.ActiveSheet.Cells.Item(1,1).EntireRow.Delete()
    $xl.ActiveSheet.SaveAs('C:\TEST3.csv', $xlType)
    $xl.Quit()
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb)
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
    
    

    For an explanation see: http://technet.microsoft.com/en-us/library/ff730962.aspx

    $xlType was not actually used in the original due to a typo.


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, June 3, 2014 4:08 PM
    Tuesday, June 3, 2014 4:07 PM
  • Understood Bill.  I will revise.  Thank you for staying as professional as you can.

    And as a side note, in no way did infer that my time was any more valuable than anyone else's.  I was simply stating that I am not required to work right now which leaves me to post as I see fit, given that this is my time and I am not spending company dollars in a proverbial slapping match.

    My signature tends to either weed out people like Mark or offend them because I come off as not friendly, but I digress.  I will change the signature.

    I knew I should have taken that job as an F-16 pilot :)

    Have a great day everyone and thank you for making TechNet so exciting.

    EFD



    Warm Fuzzies!
    • Edited by JL-4 Tuesday, June 3, 2014 4:15 PM
    Tuesday, June 3, 2014 4:11 PM
  • I apologize for whatever I did to offend you Mr. Mark

    Warm Fuzzies!


    • Edited by JL-4 Tuesday, June 3, 2014 4:15 PM
    Tuesday, June 3, 2014 4:12 PM
  • Thank you.  It actually did spit out a CSV, but I will try your solution as well.  

    EFD


    Warm Fuzzies!

    Tuesday, June 3, 2014 4:14 PM
  • Thank you.  It actually did spit out a CSV, but I will try your solution as well.  

    EFD


    Warm Fuzzies!

    It may be version dependent.  In the original it was $t=6 and the argument was set to $type.

    $t = 6
    $r = $s.Cells.Item(1,1).EntireRow
    $r.Delete()
    $s.SaveAs("C:\TEST3.csv,$type)

    It is also missing a quote which you may have fixed by making it a variable.  $type will be null or some other value.

    Just saving to a CSV extension will not necessarily create a CSV.  Setting type t 6 will force a conversion.

    Using too many variable assignments for com objects will prevent Excel from quitting and it will continue to run hidden.  Releasing the assigned objects will allow Excel to shut down.  Windows 8 does not have this issue.


    ¯\_(ツ)_/¯

    Tuesday, June 3, 2014 4:20 PM
  • That's very helpful jrv, I wasn't aware that every assignment would need to be released including ranges and worksheets, too.  

    I hope this post has helped!

    Tuesday, June 3, 2014 4:59 PM
  • That's very helpful jrv, I wasn't aware that every assignment would need to be released including ranges and worksheets, too.  

    I hope this post has helped!

    By not setting a reference you can avoid the locks.  Use only the fewest references and keep close track.  If you have a great many operations on a particular embedded object then do an assignment.  Try not to for only one or two operations.


    ¯\_(ツ)_/¯

    Tuesday, June 3, 2014 5:21 PM