0

I need to create a report that I would like to be in Excel for easier handling of data, but that Excel file needs to get the data from two csv files that are generated every time when my tests are completed ( both csv files contains id, name, timestamp but from different entity). Plan is to have a automated report from my tests in form of Excel.

My plan is to automate this process with PowerShell as I could run these PowerShell scripts and tests inside a pipeline. I haven't got luck so far with importing the data into Excel. I picked up the csv and I can see data via PowerShell output but cant figure it out how to transfer that data into excel via PowerShell. I need to mention that because my firm's policy, I can't install any PowerShell modules ( I found some ImportExcel module that would helped me to achieve what I want but unfortunately I can't use it ). To mention that files are currently on my local machine but plan is that all data will be on server where the tests are run.

So is there a way to import CSV file(s) into Excel sheet via PowerShell?

All best, D

ApprenticeOfCode90
  • 31
  • 10
  • 1
    Manually create the excel file, and reference the csv files as data connections. Then, each time you'll replace the csv file, you can refresh the data to get the latest version. This way, you won't have to bother building an excel file, and every customization you may do in the excel file will be preserved. – Steve B Jul 15 '22 at 15:32
  • `my firm's policy, I can't install any PowerShell modules` : you can't build an house if you can't use bricks – Steve B Jul 15 '22 at 15:35
  • You can use comobjects, see e.g.: [Excel COM Object in PowerShell](https://stackoverflow.com/q/36471135/1701026) – iRon Jul 15 '22 at 15:38
  • COM OBjects are a plague, playing with openxml packaging is far more reliable (but somehow trickier), no need to have excel on the computer, works on any system, etc. etc. – Steve B Jul 15 '22 at 15:39
  • I was thinking for a backup plan to do the same thing that you @SteveB suggested in first comment, but I really hope that I can find a solution to automate this, and as for second comment, that's a good analogy, but policies are polices. :) – ApprenticeOfCode90 Jul 15 '22 at 15:41
  • I forgot to mention that I've actually tried using COM Objects but all I managed to to is to create an Excel file, rename Sheet and enter some hardcoded data in cell, couldn't get to load csv data into cells. – ApprenticeOfCode90 Jul 15 '22 at 15:43
  • Both..., try to be constructive (com object might be the only option in this environment). "*couldn't get to load csv data into cells*.". The example I referrd to shows how to do this. If you can't get it to work, create a [mcve] with information outlined in [how to ask](https://stackoverflow.com/help/how-to-ask) – iRon Jul 15 '22 at 16:16
  • 1
    I've actually found a solution to this. Had to dig a bit deeper , but this comment from the way back actually worked for me. Link to comment => https://stackoverflow.com/a/21169406/9016391 – ApprenticeOfCode90 Jul 15 '22 at 16:43
  • I've actually found a solution to this. Had to dig a bit deeper , but this comment from the way back actually worked for me. Link to comment => stackoverflow.com/a/21169406/9016391 – ApprenticeOfCode90 Oct 21 '22 at 16:33

0 Answers0