Import CSV with row delimiters into Excel using PowerShell

Question

Need a help with a PowerShell script to import a CSV file with the column delimiters as | and the row delimiters as & (not carriage return) into Excel.

Here is the sample of CSV file:

 row1col1|row1col2|row1col3|row1col4&row2col1|row2col2|row2col3|row2col4&row3col1|row3col2|row3col3|row3col4 etc.

I found this script, but in only works when row delimiter is CR (carriage return). What lines should I add to make it work with & symbol, not CR.

#Define locations and delimiter $csv = "file.csv" #Location of the source file $xlsx = "file.xlsx" #Desired location of output $delimiter = "|" #Specify the delimiter used in the file # Create a new Excel workbook with one empty sheet $excel = New-Object -ComObject Excel.Application $workbook = $excel.Workbooks.Add(1) $worksheet = $workbook.Worksheets.Item(1) # Build the QueryTables.Add command and reformat the data $TxtConnector = ("TEXT;" + $csv) $Connector = $worksheet.QueryTables.Add($TxtConnector, $worksheet.Range("A1")) $query = $worksheet.QueryTables.Item($Connector.Name) $query.TextFileOtherDelimiter = $delimiter $query.TextFileParseType = 1 $query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count $query.AdjustColumnWidth = 1 # Execute & delete the import query $query.Refresh() $query.Delete() # Save & close the Workbook as XLSX. $Workbook.SaveAs($xlsx, 51) $excel.Quit()

Replace & with new line (ensure first that there's no & in data). — montonero
– montonero, Commented Dec 28, 2018 at 13:36
I don't think there is a database connector for text files that doesn't expect table rows to be separated by newlines. The Workbooks.Open() and Workbooks.OpenText() methods also only allow for specifying column delimiters, not row delimiters. Replacing the row delimiter in your input file is probably your best option. — Ansgar Wiechers
– Ansgar Wiechers, Commented Dec 28, 2018 at 13:50

Gert Jan Kraaijeveld · Accepted Answer · 2018-12-28 15:08:56Z

Most of he time I use the module ImportExcel (https://www.powershellgallery.com/packages/ImportExcel) to import or export data to/from Excel. It is much faster then the com interface. That makes my solution a 2-liner

(Get-Content .\sample.csv) -replace '&',"`r`n" |Set-Content .\sample.csv Import-Csv .\sample.csv -Delimiter '|' | Export-Excel -Path .\export2.xlsx -WorksheetName SHEET1

Again: you need a module. It works even when Excel is not installed!

Joost · Accepted Answer · 2018-12-28 14:52:11Z

You can't use a text connector with everything on one line. You'll have to add some code at the beginning of your script to get it to work.

$folder = 'c:\path\to\file' $file = 'test.csv' $delimiter = '|' $lineseparator = '&' $csvfile = Join-Path $folder $file # step by step $contents = Get-Content $csvfile -Raw $replacedcontents = $contents -replace $lineseparator, "`r`n" $replacedcontents | Out-File $csvfile -Force # or use this oneliner (Get-Content $csvfile -Raw) -replace $lineseparator, "`r`n" | Out-File $csvfile -Force

I've used `r`n (the Windows standard, char 13 & 10) but it will probably work with just `n as well.

Collectives™ on Stack Overflow

Import CSV with row delimiters into Excel using PowerShell

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related