CSV to Excel Conversion

Python convert csv to xlsx

Here's an example using xlsxwriter:

import os
import glob
import csv
from xlsxwriter.workbook import Workbook

for csvfile in glob.glob(os.path.join('.', '*.csv')):
    workbook = Workbook(csvfile[:-4] + '.xlsx')
    worksheet = workbook.add_worksheet()
    with open(csvfile, 'rt', encoding='utf8') as f:
        reader = csv.reader(f)
        for r, row in enumerate(reader):
            for c, col in enumerate(row):
                worksheet.write(r, c, col)
    workbook.close()

FYI, there is also a package called openpyxl, that can read/write Excel 2007 xlsx/xlsm files.

Automated way to convert CSV to Excel, and format as a table

As commented, if you install and import module ImportExcel, then it would not be hard to format your imported CSV data as table.

Import-CSV -Path 'X:\theInput.csv' | Export-Excel -TableName 'WhatEver' -TableStyle Medium13 -Path 'X:\'theOutput.xlsx'

Style Medium13 is an example. There are lots more styles. You can find the TableStyles enum here

---

It should also be possible to do this using Excel COM object.

For that, after loading the CSV data in a new sheet, you need to create a Range object from the table columns and cells and add that range to the sheets ListObjects collection. That will return a ListObject object you can capture in a variable.

You can then set the .TableStyle property on this object using $myListObject.TableStyle = "TableStyleMedium9"

However, this needs for you to have Excel installed where that is not needed when using the ImportExcel module.

csv to excel conversion

Using PHPExcel

include 'PHPExcel/IOFactory.php';

$objReader = PHPExcel_IOFactory::createReader('CSV');

// If the files uses a delimiter other than a comma (e.g. a tab), then tell the reader
$objReader->setDelimiter("\t");
// If the files uses an encoding other than UTF-8 or ASCII, then tell the reader
$objReader->setInputEncoding('UTF-16LE');

$objPHPExcel = $objReader->load('MyCSVFile.csv');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('MyExcelFile.xls');

Convert CSV (tab delimited) to Excel using command line

Assuming the sole objective is to covert tabs to commas:

$ tr '\t' ',' < file.csv
Jan,20,21:42:36,123UH9887934,[process name],text,text,text,text

$ awk -v OFS=, '{$1=$1}1' file.csv
Jan,20,21:42:36,123UH9887934,[process,name],text,text,text,text

$ sed 's/\t/,/g' file.csv
Jan,20,21:42:36,123UH9887934,[process name],text,text,text,text

How to convert CSV to Excel using Python with pandas in Apache NiFi?

Wrap your CSV to Excel logic inside a Python script and call it using ExecuteStreamCommand. Start something like below and make changes as per your requirement:

import pandas as pd

# Reading the csv file content from NiFi
csv_df = pd.read_csv(sys.stdin)
  
# send excel file back to NiFi
csv_df.to_excel(sys.stdout.buffer, index=False)

Configure your ExecuteStreamCommand processor as:

How to convert csv string data into excel sheet using C#

First of all, Excel can open CSV files natively so you really don't have to convert the file to Excel. But if you really need to, what I use to create Excel Sheets is EP Plus. There's no need to build the StringBuilder string if you're going to do this. If you already have it in one string, you'll have to first split by line breaks (possibly using csvItem.Split(Environment.NewLine)). I don't know how you're building sb so I can't really help with that.

using (var package = new ExcelPackage())
{
    var worksheet = package.Workbook.Worksheets.Add("Sheet 1");

    int rowCount = 1; // cols and rows in EP plus start at one not zero
    foreach (string line in XXX) { // foreach loop that goes through every line in your CSV data and sets each line to string "line"
        int colCount = 1;
        string[] lineData = line.Split(','); // split your line by comma
        foreach (string colValue in lineData) {
            worksheet.Cells[rowCount,colCount++].Value = colValue;
        }
        
        rowCount++;
    }

    // when you're done save your file
    var fi = new FileInfo(@"filename.xlsx");
    package.SaveAs(fi);
}

---

Related Topics