Skip to content

Instantly share code, notes, and snippets.

@clinuxrulz
Created March 22, 2024 11:45
Show Gist options
  • Save clinuxrulz/6fe61af591d7e0c4a4feed58528508bd to your computer and use it in GitHub Desktop.
Save clinuxrulz/6fe61af591d7e0c4a4feed58528508bd to your computer and use it in GitHub Desktop.
csv into excel dump via powershell written by ChatGPT
function ProcessFile {
param(
[string]$inputFilename,
[string]$outputFilename
)
$csv = @()
$sr = New-Object System.IO.StreamReader($inputFilename)
try {
while ($true) {
$line = $sr.ReadLine()
if ($line -eq $null) {
break
}
$row = ParseLine -line $line -delim "`t"
$csv += ,$row
}
} finally {
$sr.Close()
}
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Add()
$sheet = $workbook.Worksheets.Add()
$sheet.Name = "Totals"
for ($i = 0; $i -lt $csv.Length; $i++) {
$row = $csv[$i]
for ($j = 0; $j -lt $row.Length; $j++) {
$cell = $row[$j]
$sheet.Cells.Item[1 + $i, 1 + $j] = $cell
}
}
$workbook.SaveAs($outputFilename)
$excel.Quit()
# Release COM objects from memory
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
}
function ParseLine {
param(
[string]$line,
[char]$delim
)
$cols = @()
$value = ""
for ($i = 0; $i -lt $line.Length; $i++) {
$at = $line[$i]
if ($at -eq $delim) {
$cols += $value
$value = ""
if ($i -eq ($line.Length - 1)) {
# It ends with comma
$cols += ""
}
} elseif ($at -eq '"') {
$cols += ParseEnclosedColumn -line $line -index ([ref]$i)
$i++
} else {
$value += $line[$i]
if ($i -eq ($line.Length - 1)) {
# Last character
$cols += $value
}
}
}
return $cols
}
function ParseEnclosedColumn {
param(
[string]$line,
[ref]$index
)
$value = ""
$numberQuotes = 1
$index2 = $index.Value
for ($i = $index.Value + 1; $i -lt $line.Length; $i++) {
$index2 = $i
switch ($line[$i]) {
'"'
{
$numberQuotes++
if ($numberQuotes % 2 -eq 0) {
if ($i -lt ($line.Length - 1) -and $line[$i + 1] -eq ',') {
$index.Value = $i
return $value
}
} elseif ($i -gt ($index.Value + 1) -and $line[$i - 1] -eq '"') {
$value += '"'
}
break
}
default
{
$value += $line[$i]
break
}
}
}
$index.Value = $index2
return $value
}
$arguments = $args
if ($arguments.Length -ne 2) {
Write-Host "Please pass input file name and output file name as arguments."
} else {
ProcessFile -inputFilename $arguments[0] -outputFilename $arguments[1]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment