Importing from CSV data to Excel

Importing multiple CSV files from a source location to a single excel file with each tab named with loaded CSV filename. In this blog, we will go through steps to automate the above task via Windows’s Power Shell and via UNIX’s Perl scripting.

Step 1:

Open up a PowerShell console, and navigate to the folder with the CSV’s stored in, and execute the script.

Tip:

If you receive any errors make sure you have set your execution policy to unrestrict with following command from a PowerShell console with elevated privileges.

set-executionpolicy Unrestricted -Scope CurrentUser

Step 2:

The code used is as below.

Note: Below code has to be replaced appropriately:

$scriptpath is source file location,

$scriptpath = “C:\Users\Admin\Desktop\PowerShell”

$outfilepath is output file location,

$outfilepath = “C:\Users\ Admin \Desktop\PowerShell”

Code:

#Environment Specific Variable Value Setting

$scriptpath = “C:\Users\Admin\Desktop\PowerShell”

$outfilepath = “C:\Users\ Admin \Desktop\PowerShell”

#Date Specific Value Setting

$Day = (Get-Date).Day

$Month = (Get-Date).Month

$Year = (Get-Date).Year

$Hour = (Get-Date).Hour

$Minute = (Get-Date).Minute

$Second = (Get-Date).Second

#Function for creating excel from csv

function merge_csv

{

$Day = (Get-Date).Day

$Month = (Get-Date).Month

$Year = (Get-Date).Year

$Hour = (Get-Date).Hour

$Minute = (Get-Date).Minute

$Second = (Get-Date).Second

$CSVPath = $outfilepath

$XLOutput= “$outfilepath\$Year$Month$Day$Hour$Minute$Second”

$csvFiles = Get-ChildItem (“$CSVPath\*”) -Include *.csv

$Excel = New-Object -ComObject excel.application

$Excel.visible = $false

$Excel.sheetsInNewWorkbook = $csvFiles.Count

$workbooks = $excel.Workbooks.Add()

$CSVSheet = 1

Foreach ($CSV in $Csvfiles)

{

$worksheets = $workbooks.worksheets

$CSVFullPath = $CSV.FullName

$SheetName = ($CSV.name -split ‘\.’)[0]

$worksheet = $worksheets.Item($CSVSheet)

$worksheet.Name = $SheetName

$TxtConnector = (“TEXT;” + $CSVFullPath)

$CellRef = $worksheet.Range(“A1”)

$Connector = $worksheet.QueryTables.add($TxtConnector,$CellRef)

$worksheet.QueryTables.item($Connector.name).TextFileCommaDelimiter = $True

$worksheet.QueryTables.item($Connector.name).TextFileParseType  = 1

$worksheet.QueryTables.item($Connector.name).Refresh() |out-null

$worksheet.QueryTables.item($Connector.name).delete() |out-null

$worksheet.UsedRange.EntireColumn.AutoFit() |out-null

$CSVSheet++

}

$workbooks.SaveAs($XLOutput,51)

$workbooks.Saved = $true

$workbooks.Close()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbooks) | Out-Null

$excel.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

[System.GC]::Collect()

[System.GC]::WaitForPendingFinalizers()

#remove-item $CSVPath\$str*.csv

write-host “`r`n$str Details are stored in the path $outfilepath. The file name is $XLOutput`r`n” -ForegroundColor Green

}

cd $scriptpath

merge_csv

 Note:

Using excel with PowerShell is made by using the available excel.application COM object that will allow us to work with excel to add data and format that data.

$excel = New-Object -ComObject excel.application

The following is not mandatory, but for this example I will make excel visible so it is easier to troubleshoot and see what is happening when I begin adding and formatting data.

$excel.visible = $True

Now to add the worksheet, we will dig into the workbooks property and use its Add() method to add a workbook into Excel.

$workbook = $excel.Workbooks.Add()

UNIX Version

UNIX version of the automated script is incorporated through Perl scripting. The script reads the .csv files in a specific directory (which is dynamic) and creates an excel sheet with the sheet name as the file names. The input files are same.

The perl script requires the modules Spreadsheet::WriteExcel and Text::CSV_XS to be pre- loaded in the perl library, in the UNIX server from where the script will be executed.

The code used is as below.

#!/usr/bin/perl -w

#use strict;

use warnings;

use Spreadsheet::WriteExcel;

use Text::CSV_XS;

# create the filename

my $excelname = ‘TargetExcel.xls’;

my $workbook = Spreadsheet::WriteExcel->new($excelname);

# other files we are reading

@filenames = <*.csv>;

foreach $filename (@filenames) {

my ($name) = (split /\./,$filename);

#my $nme = ‘$’.$name.’file’;

my $nme = $filename;

#my $bfile = “b.csv”;

#my $cfile = “c.csv”;

# three worksheets

my $ws = $workbook->addworksheet(“$name”); # a

#my $b = $workbook->addworksheet(“b”); # b

#my $c = $workbook->addworksheet(“c”); # c

# setup the column formats

my %cf = ( font => ‘Arial’, );

my %bold = (  bold => 1  );

my %ch = ( align => ‘center’,  valign => ‘center’,  font => ‘Times New Roman’,  size => 24  );

my $ch = $workbook->addformat(%ch, %bold); # header

my $cn = $workbook->addformat(%cf, size => 10); # normal text

my $cs = $workbook->addformat(%cf, size => 8); # small text

# this is where we read the data from csv and write to the xls

write_from_csv($ws, $nme);

#write_from_csv($b, $bfile);

#write_from_csv($c, $cfile);

$ws->center_horizontally(0); # undo the horizontal centering

#$b->center_horizontally(0); # undo the horizontal centering

#$c->center_horizontally(0); # undo the horizontal centering

}

#$workbook->close();  # play nice

exit(0);

# Write from a comma seperated values file

# usage:

# write_from_csv($worksheet, $file);

# $worksheet is a reference to a worksheet object

# $file should contain the filename of the .csv to read (including path if necessary)

#

# This subroutine will go through the .csv and write the values in the same places in

# the selected worksheet.

#

sub write_from_csv {

my ($worksheet, $file) = @_;

# Create a new CSV parsing ojbect with the CSV options that I needed

my $csv = Text::CSV_XS->new({

‘quote_char’  => “, # what?  no quote character?  you got it!

‘escape_char’ => ‘\\’, # a backslash

‘sep_char’    => ‘,’,

‘binary’      => 0

}

);

# Row and column are zero indexed!

my $row = 0;

open (CSVFILE, “<“, $file) || die “Unable to open $file for reading: $!, stopped”;

while (<CSVFILE>) {

if ($csv->parse($_)) {

my @Fld = $csv->fields;

my $col = 0;

# keep that line (row)?

my $keep = 1;

if ($keep) {

foreach my $token (@Fld) {

$worksheet->write($row, $col, $token);

$col++;

}

}

$row++ if ($keep);

}

else {

my $err = $csv->error_input;

print “Text::CSV_XS parse() failed on argument: “, $err, “\n”;

}

}

}

Note:

Create a new Excel workbook (i.e. file) using new().

my $workbook = Spreadsheet::WriteExcel->new($excelname);

Add a worksheet to the new workbook using add_worksheet().

my $ws = $workbook->addworksheet(“$name”); # a

Write to the worksheet using write().

$worksheet->write($row, $col, $token);

Both the scripts output an excel file with multiple tabs within it. These are reusable components and can be customized to bring in more dynamic.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s