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

2

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!

answered Dec 28, 2018 at 15:08

Gert Jan Kraaijeveld

1,1878 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

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

0

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.

answered Dec 28, 2018 at 14:52

Joost

1,2361 gold badge11 silver badges19 bronze badges

Collectives™ on Stack Overflow

Import CSV with row delimiters into Excel using PowerShell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related