1

I'm running a PowerShell script on remote servers which generates the reports in the form of .csv files. I want to club these csv files as individual worksheets of a single Excel workbook.

However, the remote servers do not have Office installed, thus I cannot use the Com-Object approach.

In addition both 'Microsoft.JET.OLEDB.4.0' and 'Microsoft.ACE.OLEDB.12.0' providers are not registered on the machine.

I cannot download anything, since the script has to be generic and should able to work on any server from the large pool of servers.

Is there some way I can reach my goal or have I hit a dead end?

Or is it possible that I may be able to link the CSV files together in such a way that I may click on one cell, and other CSV file opens up?

Quick help is appreciated.

M_coder
  • 121
  • 3
  • 17
  • 1
    I think you have hit a dead end, or it will at least cost you a lot of hard work to figure this one out. Which Office version is your target? – Tom Jul 17 '15 at 09:52
  • Fetch the data from the remote machine, generate the report on your local machine – Mathias R. Jessen Jul 17 '15 at 09:54
  • @Tom The version does not matter, though 2013 would be good. – M_coder Jul 17 '15 at 09:57
  • @MathiasR.Jessen This can always be done, but the aim of this whole scripting was to make the process as generic and as automated as possible. – M_coder Jul 17 '15 at 09:59
  • My remark about the Office version was to see if you might be able to use OpenXML to directly create the Excel workbook. Very big task though and not sure if your server would allow it. – Tom Jul 17 '15 at 10:01
  • In fact, you should be better with CSVs than with an Excel workbook. The reason is that your CSVs are just plain text files, thus they are readable on any system, while with Excel you will only be able to read them via some tool that has to first be available on a certain PC, thus limiting (to a degree) your ability to read the data. Also, scripts use CSV data better than Excel, because parsing text is better than parsing some custom data structures without required DLLs. You can still use a workstation with Excel to combine these CSVs into one, with or without the COM object. – Vesper Jul 17 '15 at 10:05
  • @Tom Ok! Is there a quick check possible where I can check if the method will be feasible on my server. If yes, then I may try to work around things. – M_coder Jul 17 '15 at 10:05
  • @Vesper I agree. This was the requirement posed since the review team is used to working on such systems. I was trying to make my way through – M_coder Jul 17 '15 at 10:08

1 Answers1

0

Check if this fits your needs:

CSVs to multi-sheet XLS without Excel installed. Powershell

you will neeed to have the powershell module on any server where you intend to generate the excel workbook.

Community
  • 1
  • 1
Kiran Reddy
  • 2,836
  • 2
  • 16
  • 20
  • 1
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – Glorfindel Jul 17 '15 at 17:49
  • @Glorfindel - makes sense thx. – Kiran Reddy Jul 18 '15 at 02:13
  • Great!! This worked like a charm. Thanks so much for your help. – M_coder Jul 18 '15 at 12:06
  • @Kiran, An issue popped up while I was doing test on my server. It says "Exception Calling 'AutofitColumn' with '2' arguments. Hexadecimal Value 0x07 is an invalid character." Please help me with this. – M_coder Jul 18 '15 at 14:34
  • Not sure how you are running the cmdlet but 'Autofitcolumn' is a switch so you should not be passing any arguments to it. Help is included for all the cmdlets so type `help cmdletname -examples`. which should give you a good idea on how to run each command. If you are still having problems then perhaps you can open a new question here or just go to codeplex and open a new discussion on the project page. – Kiran Reddy Jul 20 '15 at 02:31