Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Data formatting in CSV file to EXCEL

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Data formatting in CSV file to EXCEL
# 1  
Old 07-22-2014
Question Data formatting in CSV file to EXCEL
Hello friends

I want to convert an csv file on unix (which is generated by a ETL application) to a formatted excel sheet like .I have roughly like 28 columns


1)All numbers need to be stored as numbers with leading zeros-like format as text for this column to preserve leading zeroes e.g '0012'
2)All Amounts should be formatted as numbers with 2 decimals and separator e.g '123.45'
3)All dates to be formatted as MM/DD/YYYY

My users are totally business and they want this unix csv file totally formatted so that they can use it readily..

I have read forums where people suggested using perl script ..Any initial help to start with is greatly appreciated.
# 2  
Old 07-22-2014
Excel will mangle your formatting and replace it with whatever it pleases, especially with dates.
# 3  
Old 07-22-2014
even if its not for the dates..I just want to format the text and numbers.
# 4  
Old 07-22-2014
Why not format up your sheet in excel how you want it then save as "XML Spreadsheet 2003".

You can then edit this file and cut out the unnecessary stuff like (<DocumentProperties> <OfficeDocumentSettings> etc.), just play around by editing the file and checking it can still be loaded by excel.

As an example this is a nice 2 column sheet with leading zero format on the first column, it's a fairly simple exercise to then transform a flat csv file into this type of .xml document.

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>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="0000"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="etldev_demo">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row>
    <Cell ss:StyleID="s63"><Data ss:Type="Number">12</Data></Cell>
    <Cell><Data ss:Type="String">test text</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

Or course there are perl CPAN modules that can assist in producing this output (Spreadsheet-WriteExcelXML comes to mind), and you may find dealing with these easier.
# 5  
Old 07-22-2014
I need this done on a daily basis like an automated things so hence I cant format it manually..

second thing is I am looking for a shell script/perl script which will take the csv on unix and give me a formatted xls with all the requirements I specified..
# 6  
Old 07-22-2014
Well what I'm thinking of is a template file where an awk program can then merge with to produce your document.

Imaging if the document above was split up into something like:

Code:
--HEADER--
<?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>
  <Style ss:ID="s63">
   <NumberFormat ss:Format="0000"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="etldev_demo">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="1" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
--ROW--
   <Row>
    <Cell ss:StyleID="s63"><Data ss:Type="Number">%%FIELD#01%%</Data></Cell>
    <Cell><Data ss:Type="String">%%FIELD#02%%</Data></Cell>
   </Row>
--FOOTER--
  </Table>
 </Worksheet>
</Workbook>

Then an awk program could output the HEADER block, and for each csv line replace all the %%FIELD#nn%% values in the ROW block and finally output the FOOTER block.

---------- Post updated at 08:37 AM ---------- Previous update was at 08:13 AM ----------

The code to produce your file could be as simple as this:

Code:
awk -F, '
FNR==NR&&/^--/ {section++; next}
FNR==NR{block[section]=(block[section]?block[section]"\n":"") $0;next}
FNR==1{print block[1]}
{ out=block[2]
  for(i=1;i<=NF;i++) gsub(sprintf("%%%%FIELD#%02d%%%%",i),$i,out);
  print out
}
END{print block[3]}' template data.csv

However, if your csv has quoted fields with possible escaped characters and the like, you will need something a little smarter, like a proper csv parser.

Fortunately I have already posted one in this forum and we could use most of the code from that:

https://www.unix.com/302850801-post13.html
# 7  
Old 07-23-2014
Hi, can we see a sample of what this cvs data looks like?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Formatting data to put it in the excel file

Hello, I have a file with the below contents : Policy Name: Backup_bkp Policy Type: Catalog_bkp Active: yes Effective date: 08/07/2013 02:02:12 Mult. Data Streams: no Client Encrypt: no Checkpoint: no Policy Priority: ... (11 Replies)
Discussion started by: rahul2662
11 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

4. Shell Programming and Scripting

Writing excel file using perl : Excel file formatting changed

I am trying to create a program where user can input data in certain excel cells using user interface on internet....the programming is on perl and server is unix But when i parse data into excel the formatting of sheets is turned to default and all macro coding removed. What to do...Please... (7 Replies)
Discussion started by: mud_born
7 Replies

5. 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

6. Shell Programming and Scripting

csv file to excel issue

Hi, I am trying to attach and email a csv file in the form of an excel sheet. And I have been successful in doing this. But after some days I realised that some fields in the csv file are also having commas because of which this field is getting splitted in columns in the excel sheet. ... (5 Replies)
Discussion started by: girish1428
5 Replies

7. UNIX for Dummies Questions & Answers

Excel File to CSV

Hi All, I have to convert the excel file which will be placed in the Unix box to a CSV file using a shell script. Please Advise. Thanks & Regards, Kumar66 (1 Reply)
Discussion started by: kumar66
1 Replies

8. Shell Programming and Scripting

Formatting Data - CSV

I want to check whether if any column data has any + , - , = prefixed to it then convert it in such a form that in excel its not read as formula. echo "$DATA" | awk 'BEGIN { OFS="," } -F" " {print $1,$2,$3,$4,$5,$6,$7,$8.$9,$10,$11,$12}' (4 Replies)
Discussion started by: dinjo_jo
4 Replies

9. Shell Programming and Scripting

text file to excel or csv

Hi, I have a text file like ---------------------- aaa|bbb|ccc|ddd|eee fff|gggg|hhhhhh|kkkk -------------------- I have a script which will transfer(thourgh FTP) this text file to windows system. But I want to covert it into excel or CSF then upload into windows system.. thanks (9 Replies)
Discussion started by: srikanthus2002
9 Replies

10. Filesystems, Disks and Memory

Formatting excel file

Hi, I have a problem in formatting the excel file from my script. Assume that there is a text file generated using a 'C shell Unix script' with a format similar to the one seen below. Each column has a fixed length and every row is going to have a value against a column depending on the... (1 Reply)
Discussion started by: subra
1 Replies
Login or Register to Ask a Question