0

I have an Excel file that I receive and want to process it to a CSV using Powershell. I have to alter it quite specifically so it can be a reliable input for a program that will process the csv info. I don't know the exact headers, but i know there can be duplicates. What I do is open the xlsx file with excel and save it as CSV:

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $True
$objExcel.DisplayAlerts = $True

$Workbook = $objExcel.Workbooks.open($xlsx1)
$WorkSheet = $WorkBook.sheets.item($sheet)
$xlCSV = 6

$Workbook = $objExcel.Workbooks.open($xlsx2)
$WorkSheet = $WorkBook.sheets.item($sheet)
$WorkBook.SaveAs($csv2,$xlCSV)

Now, the XLSX file will have comma's, so first I want to change them to dots. I tried this, but it's not working:

$objRange = $worksheet.UsedRange
$objRange.Replace ",", "."

It errors out saying: Unexpected token '", "'.

Then when saving I want to set the Delimiter to comma, as it uses ";" standard. With something like:

$WorkBook.SaveAs($csv2,$xlCSV) -delimiter ","

The last problem is the duplicate headers; this prevents PS to use Import-CSV. Here I tried, when file is separated with a comma it works:

Get-Content $downloads\BBKS_DIR_AUTO_COMMA.csv -totalcount 1 >$downloads\Headers.txt

But then I need to rename de duplicate names like I can have Regio, Regio, Regio. I want to change this to Regio, Regio2, Regio3

My plan was to lookup the data of the txt, search for duplicates, and then ad an incremental nummer.

In the end I need to add a column with incremental numbers, but always with four numbers, like; 0001, 0002, 0010, 0020, 0200, 1500, I wont exceed 9999. How can this be done?

If you can help me, if only partially I'm very happy. Further, I'm running Windows 7 x64, Powershell 3.0, Excel 2016 (if relevant) If easier, its fine to go back to Command prompt for some tasks.

J. Ede
  • 31
  • 5
  • You are asking two different questions here: one for saving csv with `;` delimiter (identified in title) and the other for headers. As for former, what is your [default Language setting](http://stackoverflow.com/questions/13496686/how-to-save-semi-colon-delimited-csv-file-using-vba)? Is it set to `;`? For latter, ask another separate SO question as that requires some extensive coding and not a one liner. – Parfait Nov 25 '16 at 23:53

1 Answers1

1

Personally, I wouldn't try and work with Excel sheets via Excel itself and COM - I'd use the excellent module https://github.com/dfinke/ImportExcel

Then you can import from the sheet straight to a native Powershell object array, and re-export with Export-Csv -Delimiter.

Edit: To answer follow ups :

Once you've loaded the module you can do "Get-Module ImportExcel | Select-Object -ExpandProperty ExportedCommands" to see what it makes available.

To import your Excel in the first place, do something like :

$WorkBook = Import-Excel

And if you need to take care of duplicate column names, you can do :

$WorkBook = Import-Excel -Header @("Regio1", "Regio2", "Regio")

Where the array you pass to -Header needs to include every column you want from the workbook.

GodEater
  • 3,445
  • 2
  • 27
  • 30
  • Thank you for your quick answer, and I get the feeling that the ImportExcel Module is great. Its just that I'm quite new to the programming and there doesn't seem to be a guide through the Module. Am I missing a guide (apart from the not so userfriendly update Readme) somewhere explaining commands that can be used and or a forum to ask questions? – J. Ede Nov 25 '16 at 13:28