Remodelling the .csv file using the script

Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Remodelling the .csv file using the script
# 1  
Old 08-07-2013
Remodelling the .csv file using the script

Hello All,
I have a .csv file named as remark.csv
The csv file content looks like below:

			Remark_Hello_1	Remark_Hello_2	Remark_Hello_3	Remark_Hello_4
Hello_World_FW_0001		X			
Hello_World_FW_0002		X		X		
Hello_World_FW_0003				X		X
Hello_World_FW_0004				X		           X

I want to write a script which will generate output in a .xls file or .csv file in the below format:

Hello World ID			Remark ID
Hello_World_FW_0001		Remark_Hello_1		
Hello_World_FW_0002		Remark_Hello_1
Hello_World_FW_0003		Remark_Hello_2
Hello_World_FW_0004		Remark_Hello_2

Basically i want to replace X with corresponding Remark ID.

Any suggestion on this will be really helpful

---------- Post updated at 11:02 AM ---------- Previous update was at 08:51 AM ----------

Hello Guys,

Any problem to understand the fquestion just let me know,
I have done this much,but unable to find the logic to proceed further,anyways i am not that expert in csv scripting using perl:

   #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::WriteExcel;

    # Open the comma-separated file
    open (TABFILE,$ARGV[0]) or die "$ARGV[0]: $!";

    # Create a new Excel workbook
    my $workbook  = Spreadsheet::WriteExcel->new($ARGV[1]);
    my $worksheet = $workbook->addworksheet();
	$worksheet->write(0,0,'Hello World ID');
	$worksheet->write(0,1,'Remark ID');
    # Row and column are zero indexed
    my $row = 1;
	my $val=1;
    while (<TABFILE>) {

# 2  
Old 08-07-2013
Make sure that your input file has only (multiple) TAB chars as separators:
awk -F'\t+' 'NR==1      {split ($0, H, "\t"); next}
                        {printf "%s", $1
                         for (i=2;i<=NF;i++) if ($i=="X") print "\t\t"H[i+2]}
        ' file
Hello_World_FW_0001        Remark_Hello_1
Hello_World_FW_0002        Remark_Hello_1
Hello_World_FW_0003        Remark_Hello_2
Hello_World_FW_0004        Remark_Hello_2

# 3  
Old 08-07-2013
Hello Rudic,

My input is in a excel file.Its not having multiple TAB character as a separator.
Anyways i just executed your script,,,,but it is not printing anything starting with Remark_ corresponding to Hello_
# 4  
Old 08-07-2013
The input file you have given in post#1 HAS multiple TAB chars PLUS some space chars.

Pls post the exact input csv file that "... is not printing anything ..."
# 5  
Old 08-07-2013

I have attached the input.xls as well as the output.xls.

Input.xls contain the details about the input.
Output.xls contains how the output shall looks like.

The second column in output shall be filled as per the X mark in input.xls corresponding to column name
# 6  
Old 08-09-2013
In post#1, you are talking of and showing a .csv file, i.e. a comma (or TAB) separated text file. Now you are attaching binary .xls files. This does not fit.
My proposal works if applied to correctly structured .csv files, outputting text files that can be imported into EXCEL.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 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 create a CSV file

I created a script that will go out and so a "/sbin/chkconfig --list | egrep XXX" against a server list that would create an output file like the following example: ---------------------------------------------------------------------------------- SERVER1 RC_Script_1 0:off 1:off 2:off... (4 Replies)
Discussion started by: asnatlas
4 Replies

4. Shell Programming and Scripting

Script to generate csv file

Hello; I need to generate a csv file that contains a list of all the files in a particular server (from the root directory ie: \) that have a permission stamp of 777. I would like to create the csv so that it contains the following: server name, file name, full path name where file exists,... (17 Replies)
Discussion started by: gvolpini
17 Replies

5. Shell Programming and Scripting

Conversion of below Tabs Tex file into CSV format file : shell script needed

Request if some one could provide me shell script that converts the below "input file" to "CSV format file" given Name Domain Contact Phone Email Location ----------------------- ------------------------------------------------ ------- ----- ---------------------------------... (7 Replies)
Discussion started by: sreenath1037
7 Replies

6. Shell Programming and Scripting

Script for updating a .csv file

Hello guys, I have one .csv file with all the file names(.c,.h,.sm,.txt etc)..... Now i have compared two baseline in UCM and got a report in .txt file,which looks like this...... DIFFBL INT_1029_Rel6.0.0.90@\DIR_PVOB STD_6.0.0.50_UCM@\DIR_PVOB (COMPONENT TEXT) Comparing the following: ... (14 Replies)
Discussion started by: suvenduperl
14 Replies

7. Shell Programming and Scripting

How to convert a excel file to a .csv file from unix script

Hi I have a excel file in unix machine and have to convert it into a .csv file.I have to do this from a unix script.How do we do this? Thanks Abhinav (3 Replies)
Discussion started by: akashtcs
3 Replies

8. Shell Programming and Scripting

Script for reading .csv file

Can someone please help me to write script for following scenario : 1> script should read a input .csv file of format : EmpName, PF, Leave, Basic ,HRA 2> another config file ( may be again a .csv file ) has format EmpName and EmpID 3> script should read another config file for each EmpName in... (3 Replies)
Discussion started by: creativeworld
3 Replies

9. Shell Programming and Scripting

Shell Script to Load data into the database using a .csv file and .ctl file

Since i'm new to scripting i'm findind it difficult to code a script. The script has to be an executable with 2 paramters passed to it.The Parameters are 1. The Control file name(.ctl file) 2. The Data file name(.csv file) Does anybody have an idea about it? :confused: (3 Replies)
Discussion started by: Csmani
3 Replies
Login or Register to Ask a Question