Data formatting in CSV file to EXCEL Post: 302910146

Sponsored Content

Top Forums Shell Programming and Scripting Data formatting in CSV file to EXCEL Post 302910146 by Chubler_XL on Tuesday 22nd of July 2014 06:37:15 PM

07-22-2014

Moderator

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

Chubler_XL

View Public Profile for Chubler_XL

Find all posts by Chubler_XL

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

text file to excel or csv

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

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

6. 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,...

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

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

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

10. 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: ...

LEARN ABOUT MOJAVE

glgetuniformblockindex

GLGETUNIFORMBLOCKIND(3G)					    OpenGL 3.3						  GLGETUNIFORMBLOCKIND(3G)

NAME

       glGetUniformBlockIndex - retrieve the index of a named uniform block

C SPECIFICATION

       GLuint glGetUniformBlockIndex(GLuint program, const GLchar *uniformBlockName);

PARAMETERS

       program
	   Specifies the name of a program containing the uniform block.

       uniformBlockName
	   Specifies the address an array of characters to containing the name of the uniform block whose index to retrieve.

DESCRIPTION

       glGetUniformBlockIndex retrieves the index of a uniform block within program.

       program must be the name of a program object for which the command glLinkProgram() must have been called in the past, although it is not
       required that glLinkProgram() must have succeeded. The link could have failed because the number of active uniforms exceeded the limit.

       uniformBlockName must contain a nul-terminated string specifying the name of the uniform block.

       glGetUniformBlockIndex returns the uniform block index for the uniform block named uniformBlockName of program. If uniformBlockName does
       not identify an active uniform block of program, glGetUniformBlockIndex returns the special identifier, GL_INVALID_INDEX. Indices of the
       active uniform blocks of a program are assigned in consecutive order, beginning with zero.

ERRORS

       GL_INVALID_OPERATION is generated if program is not the name of a program object for which glLinkProgram() has been called in the past.

NOTES

       glGetUniformBlockIndex is available only if the GL version is 3.1 or greater.

SEE ALSO

       glGetActiveUniformBlockName(), glGetActiveUniformBlock(), glLinkProgram()

COPYRIGHT

       Copyright (C) 2010 Khronos Group. This material may be distributed subject to the terms and conditions set forth in the Open Publication
       License, v 1.0, 8 June 1999.  http://opencontent.org/openpub/.

OpenGL 3.3							    03/08/2011						  GLGETUNIFORMBLOCKIND(3G)

10 More Discussions You Might Find Interesting

1. Filesystems, Disks and Memory

Formatting excel file

Discussion started by: subra

2. Shell Programming and Scripting

text file to excel or csv

Discussion started by: srikanthus2002

3. Shell Programming and Scripting

Formatting Data - CSV

Discussion started by: dinjo_jo

4. UNIX for Dummies Questions & Answers

Excel File to CSV

Discussion started by: kumar66