Skip to content

Instantly share code, notes, and snippets.

@clinuxrulz
Created March 22, 2024 08:20
Show Gist options
  • Save clinuxrulz/3d13c27e9b6a6faa4ecb5c797a92b637 to your computer and use it in GitHub Desktop.
Save clinuxrulz/3d13c27e9b6a6faa4ecb5c797a92b637 to your computer and use it in GitHub Desktop.
Dump CSV into Excel via powerscript.
Add-Type -ReferencedAssemblies ("Microsoft.Office.Interop.Excel") -TypeDefinition @"
using System;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
public static class Program {
public static void Main(string[] args) {
if (args.Length != 2) {
Console.WriteLine("Please pass input file name and output file name as arguments.");
return;
}
var inputFilename = args[0];
var outputFilename = args[1];
ProcessFile(inputFilename, outputFilename);
}
static void ProcessFile(string inputFilename, string outputFilename) {
string[][] csv;
using (var sr = new System.IO.StreamReader(inputFilename)) {
csv = CsvParser.ParseAll(sr);
}
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = (Excel.Workbook)excel.Workbooks.Add();
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add();
sheet.Name = "Totals";
for (int i = 0; i < csv.Length; i++) {
var row = csv[i];
for (int j = 0; j < row.Length; j++) {
var cell = row[j];
sheet.Cells[1 + i, 1 + j] = cell;
}
}
workbook.SaveAs(outputFilename);
excel.Quit();
// Release COM objects from memory
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
}
static class CsvParser {
public static string[][] ParseAll(System.IO.StreamReader sr) {
List<string[]> csv = new List<string[]>();
while (true) {
var line = sr.ReadLine();
if (line == null) {
break;
}
var row = ParseLine(line, '\t');
csv.Add(row);
}
return csv.ToArray();
}
public static string[] ParseLine(string line, char delim) {
List<string> cols = new List<string>();
string value = "";
for (int i = 0; i < line.Length; i++) {
char at = line[i];
if (at == delim) {
cols.Add(value);
value = "";
if (i == line.Length - 1) {
// It ends with comma
cols.Add("");
}
} else if (at == '"') {
cols.Add(ParseEnclosedColumn(line, ref i));
i++;
} else {
value += line[i];
if (i == line.Length - 1) {
// Last character
cols.Add(value);
}
}
}
return cols.ToArray();
}
static string ParseEnclosedColumn(string line, ref int index) {
string value = "";
int numberQuotes = 1;
int index2 = index;
for (int i = index + 1; i < line.Length; i++) {
index2 = i;
switch (line[i]) {
case '"':
numberQuotes++;
if (numberQuotes % 2 == 0) {
if (i < line.Length - 1 && line[i + 1] == ',') {
index = i;
return value;
}
} else if (i > index + 1 && line[i - 1] == '"') {
value += '"';
}
break;
default:
value += line[i];
break;
}
}
index = index2;
return value;
}
}
"@ -passthru | Out-Null
[Program]::Main(@(".\DcCsvToExcel\samples\DCP_240320.csv", "output.xlsx"))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment