skip 1st few rows

[brucebachmann]

just starting using this to convert .csv files to password protected .xlsx files

Import-Csv c:\temp\myCSV.csv | Export-Excel c:\temp\myExcel.xlsx -AutoSize -Password "mypassword"

my csv files have 'report headers' at the very top, 5 or 6 rows with 1 column each

have a couple of issues

1. by default, the export is just converting 1 column for all rows, when the actual data has 20 rows, I would like the report headers in the excel file, followed by the data
2. the password has a $ in it, which causes a problem, when I remove the $, all ok, would like special chars in a password

[dfinke]

Please post the sample data, plus how you are passing the password.

[brucebachmann]

Doug, thanks for taking a look and all the work put into this module ! sample data atttached

running this from cmd line
Import-Csv c:\temp\Census-20221201.csv | Export-Excel c:\temp\Census-20221201.xlsx -Password "tri$1234"

the password gets truncated where the $ starts, password to open set to tri

Census-20221201.csv

[brucebachmann]

I guess another way would be to create a new xlsx file, add the 1st few lines from the cmd line and then append the data, if you could provide an example of how to do that, if that's easier for you

[dfinke]

Export-Excel has a Start-Row parameter. You can use -PassThru to get the Excel worksheet back and then use Set-ExcelRange to add the data for roww 1-5.

Check/search the Examples https://github.com/dfinke/ImportExcel/tree/master/Examples for those parameters and function.

[dfinke]

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = "./test.xlsx"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$excel = $data | Export-Excel $xlfile -AutoSize -StartRow 7 -PassThru

Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A1" -Value "Tri-Star Systems"
Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A2" -Value "Benefit Census Report"
Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A3" -Value "Employer: 350 - ABC Corporation" 
Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A4" -Value "Plan Year: 01/01/2023 - 12/31/2023"
Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A5" -Value "Report Date: 12/01/2022"

Close-ExcelPackage $excel -Show

[brucebachmann]

is a $ not allowed in a password value ?

[stahler]

Using double quotes "tri$1234" says:
Printout tri and the value of the variable $1234. Since $1234 is null, you get tri.

Instead of using double quotes, use single quotes, this will keep it as a verbatim string.