Tabbed multiple csv files into one single excel file with using shell script not perl

Shell Programming and Scripting
1

Hi Experts,

I am querying backup status results for multiple databases and getting each and every database result in one csv file. so i need to combine all csv files in one excel file with separate tabs. I am not familiar with perl script so i am using shell script.

Could anyone please provide shell script or any logic then it will be helpful for automatic backup status report.

My query results are coming from db2 luw database.

Thanks,
Ramakrishna

2

From technet:

You need complex methods in VBA or perl (or other Microsoft Visual Studio languages) to be able to write xlsx files. Since you cannot program in an environment that supports what you need, consider simply creating a series commonly named CSV formatted files for each workbook.

Then you can write a powershell script to import them into an Excel workbook format.
Create an Excel file from within Powershell - Spiceworks.

I do not believe you can do this solely in UNIX/Linux shell

FWIW, the real problem appears to be your Windows-centric requirements, which is understandable. Somebody decided for you to skip the use of DB2 the toolsets for reporting and data management, it seems.

3

You might get a starting point looking at this thread:

1 Like
4

Thanks for replying ...

I don't have knowledge on perl/Powershell/python programming languages.
I have written shell script and it generating reports so i need to send them in one excel file with multiple tabs. If any one provide ready made script then it will be helpful for me....

Thanks in advance.

5

Sorry ramakrk2 but these Forums are about assisting you to help yourself not providing ready made scripts. It sounds like you have some shell scripting skills and a multi tab excel sheet is really just a text file in the correct format.

To assist you further here is a worked example for a simple case of two csv files:

File 1:

12.5,cell 2

File 2:

file 2 row 1,11.9,Testing
file 2 row 2,23.7,Unix
file 2 row 3,99.9,Scripting

Target output (XML workbook):

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
  </Style>
 </Styles>

 <Worksheet ss:Name="Sheet 1">
  <Table>
<Row>
    <Cell><Data ss:Type="Number">12.5</Data></Cell>
    <Cell><Data ss:Type="String">cell 2</Data></Cell>
</Row>
  </Table>
 </Worksheet>

 <Worksheet ss:Name="Sheet 2">
  <Table>
<Row>
    <Cell><Data ss:Type="String">file 2 row 1</Data></Cell>
    <Cell><Data ss:Type="Number">11.9</Data></Cell>
    <Cell><Data ss:Type="String">Testing</Data></Cell>
</Row>

<Row>
    <Cell><Data ss:Type="String">file 2 row 2</Data></Cell>
    <Cell><Data ss:Type="Number">23.7</Data></Cell>
    <Cell><Data ss:Type="String">Unix</Data></Cell>
</Row>

<Row>
    <Cell><Data ss:Type="String">file 2 row 3</Data></Cell>
    <Cell><Data ss:Type="Number">99.9</Data></Cell>
    <Cell><Data ss:Type="String">Scripting</Data></Cell>
</Row>
  </Table>
 </Worksheet>

</Workbook>

Don't let this big block of text throw you off.

Most of it is produced by outputting fixed text strings, with only the red blocks needing to be populated from your data (csv files).