Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Tabbed multiple csv files into one single excel file with using shell script not perl
# 1  
Old 02-16-2018
Tabbed multiple csv files into one single excel file with using shell script not perl

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  
Old 02-16-2018
From technet:
Quote:
Office Open XML (OOXML) format was introduced with Microsoft Office 2007 and became the default format of Microsoft Excel ever since. Excel-related file extensions of this format include: .xlsx – Excel workbook. .xlsm – Excel macro-enabled workbook; same as xlsx but may contain macros and scripts.
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  
Old 02-18-2018
You might get a starting point looking at this thread:

Data formatting in CSV file to EXCEL
This User Gave Thanks to Chubler_XL For This Post:
# 4  
Old 02-19-2018
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  
Old 02-19-2018
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:
Code:
12.5,cell 2

File 2:
Code:
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):
Code:
<?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).

Last edited by Chubler_XL; 02-19-2018 at 05:22 PM..

Previous Thread | Next Thread
Test Your Knowledge in Computers #197
Difficulty: Easy
C# ranked higher than C according to the TIOBE Index for October 2019.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk - CSV file - field with single or multiple spaces

Hi, In a csv file, I want to select records where first column has zero or multiple spaces. Eg: abc.csv ,123,a ,22,b ,11,c a,11,d So output should be: ,123,a ,22,b ,11,c Please advise (5 Replies)
Discussion started by: vegasluxor
5 Replies

2. Shell Programming and Scripting

Write two csv files into one excel with multiple sheets

I have requirement to write two CSV files to one single excel with multiple sheets. Data present in the two files should sit in excel as different sheets. How can we achieve this using shell script? 1.csv 2. csv 1,2,3,4 5,6,7,8 XXXXX YYYYY Res.excel 1.csv data... (1 Reply)
Discussion started by: duplicate
1 Replies

3. Shell Programming and Scripting

Perl script to Merge contents of 2 different excel files in a single excel file

All, I have an excel sheet Excel1.xls that has some entries. I have one more excel sheet Excel2.xls that has entries only in those cells which are blank in Excel1.xls These may be in different workbooks. They are totally independent made by 2 different users. I have placed them in a... (1 Reply)
Discussion started by: Anamika08
1 Replies

4. UNIX for Dummies Questions & Answers

How to Create excel file(.csv) using shell script?

Hi, i have shell script which compiles n number of test cases and execute them one by one. i want to create report in excel through script in which two columns namely "test id" and "release".second column have two subcolumns namely compiles and excutes. so i want first column should display test... (15 Replies)
Discussion started by: RamMore123
15 Replies

5. Shell Programming and Scripting

Shell Script for converting file to Excel or CSV

Hi I have a dat file which has "n" number of columns. The file is delimited. The number of columns keep varying as the file is generated out of DB queries. Could you please help me in writing a script which will generate a XLS or CSV file out of the dat file. (5 Replies)
Discussion started by: Vee
5 Replies

6. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

7. Shell Programming and Scripting

Copy Data from CSV file to Excel Sheet using Perl

Hi All, Firstly I will like to wish A Happy New Year to all. Now my issue is I have one csv file say(data.csv) and one excel file say(result.xls) The result.xls contains two sheet name Sheet1 and Sheet2, Now What I am trying to do is to First I want to delete that data of Sheet2 if present any,... (6 Replies)
Discussion started by: adisky123
6 Replies

8. Shell Programming and Scripting

Shell script - Excel/CSV file - More than one tab

Hi All, Following is my requirement. I have searched the site and found some threads which has same queries, but non of them have any answer. I thought of posting it once more. We are generating different reports through shell script after we finish our data load. Currently there are 7 such... (5 Replies)
Discussion started by: ace_friends22
5 Replies

9. Shell Programming and Scripting

Add multiple .csv files as sheets to an excel file in unix.

Hi, I am using Solaris 8. My script outputs 4 .csv files. Currently I am SFTPing the files and creating a new excel file with the 4 files as sheets. Can anyone suggest ways to do this in UNIX ? Thanks, David. (2 Replies)
Discussion started by: libin4u2000
2 Replies

10. Shell Programming and Scripting

Executing Multiple .SQL Files from Single Shell Script file

Hi, Please help me out. I have around 700 sql files to execute in a defined order, how can i do it from shell script (3 Replies)
Discussion started by: anushilrai
3 Replies

Featured Tech Videos