Shell script for .Txt to .csv conversion with data processing


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell script for .Txt to .csv conversion with data processing
# 1  
Old 01-03-2015
Shell script for .Txt to .csv conversion with data processing

Hi experts,

I want to convert a txt file having rows and columns (CNAI_DUMP_raw.txt) by comparing it with another text file (paramaters.txt) and generate a output in CSV which contains only 3rd column from CNAI_DUMP_raw.txt, and the columns mentioned in parameters.txt.

FYI: There are two columns in parameters.txt one containing some column name from CNAI_DUMP.txt and other with the ideal value against it.

The output should contain actual value of the cell and the ideal value mentioned in parameters.txt.

Both the files are attached herewith.

Please let me know for any more information.
# 2  
Old 01-03-2015
Hi, what have you tried and where are you stuck? What column from CNAI_DUMP.txt needs to be matched agains which column in parameters.txt..
This User Gave Thanks to Scrutinizer For This Post:
# 3  
Old 01-03-2015
Reply of the post

Hi,

I need to match 1st row in CNAI_DUMP_raw.txt with 1st column of parameter.txt

Desired output in CSV:

1st column: Parameter ( This column is present in CNAI_DUMP_RAW.txt in the first row and Parameter.txt in the first column)

2nd Column: Ideal Value ( This is the 2nd column present in Parameter.txt)

3rd Column: Cell Name ( Present in 4th cloumn of CNAI_DUMP_RAW.txt)
E.g RBR201A
RBR201B...

4th Column: Actual Value ( Present in CNAI_DUMP_RAW.txt)
This is the value of the cell in (CNAI_DUMP_RAW.txt) with Row id as Cell name and Column Id as 1st Column of Parameter.txt

---------- Post updated at 12:52 PM ---------- Previous update was at 12:38 PM ----------

Hi Scrutinizer,

I have tried to explain my desired output in the post above.

Also the input files were attached in the first post.

The desired output which I want to show in in csv format and can not be shown in code.

Please let me know for more clarification

---------- Post updated at 01:06 PM ---------- Previous update was at 12:52 PM ----------

Hi Scrutinizer,

I have tried to explain my desired output in the post above.

Also the input files were attached in the first post.

The desired output which I want to show in in csv format and can not be shown in code.

Please let me know for more clarification
# 4  
Old 01-03-2015
Has anyone found any matches in these two files?
# 5  
Old 01-03-2015
Quote:
Originally Posted by ongoto
Has anyone found any matches in these two files?
The OP didn't bother mentioning:
  1. that the field names contain uppercase letters in paramaters.txt but contain lowercase letters in CNAI_DUMP_raw.txt,
  2. what operating system is being used (which with an input file with lines containing well over 10,000 bytes per line can be very important), nor
  3. what shell is being used.
If the OP is unwilling to provide a small set of sample input files, corresponding desired sample output, and a clear explanation of the processing to be performed to produce that desired output from the input files; there isn't a high likelihood that a working solution will be provided here. (Nor is there much incentive for the volunteers here to download 150Kb of data to try to guess at what needs to be done.)

Furthermore, when asked "what have you tried and where are you stuck?", the response was two repeated demands for us to provide working code.

The UNIX & Linux Forums is here to help people learn how to use the great tools available on UNIX and Linux systems to perform wondrous computing tasks. We are not here to work as unpaid programming staff for someone who is unwilling to demonstrate any attempt to do any work to get the job done.

Last edited by Don Cragun; 01-03-2015 at 09:34 PM.. Reason: Fix typos.
# 6  
Old 01-04-2015
Hello Experts,

Apologies for the inconvenience faced, as I may have not able to present the problem.

Of course neither I am here to get my work completely done not I am a hacker but to learn and take help for experts like you for a text processing issue.

My OS version:
Code:
Linux L9AHD91 3.8.0-44-generic #66~precise1-Ubuntu SMP Tue Jul 15 04:01:04 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux

Shell is bash shell

Work done so far:

Code:
a=$( cat CNAI_DUMP_raw.txt | awk '{print $4}') ;

awk '{print $2}' paramaters.txt > b.txt

b= cat b.txt

c=$( cat paramaters.txt | awk '{print $1}') ;

echo $a;
echo $b;
echo $c;

Sample i/p files are CNAI_DUMP_raw.txt and paramaters.txt already attached
The output file required is in .csv format. How can I attach it here?

Last edited by Don Cragun; 01-04-2015 at 04:33 AM.. Reason: Change multi-line ICODE tags to CODE tags.
# 7  
Old 01-04-2015
Quote:
Originally Posted by Gautam Banerjee
... ... ...
Sample i/p files are CNAI_DUMP_raw.txt and paramaters.txt already attached
The output file required is in .csv format. How can I attach it here?
Rather than uploading well over a hundred kilobytes of data, it would be much better to:
  1. describe the number of fields, number of bytes / line, and total sizes of your real input and expected output files,
  2. provide abbreviated (fewer fields and fewer lines, but enough to show the structure of your data) sample input (in CODE tags) in your post instead of hoping that some of the volunteers here have space to burn on the systems they use to try to help you,
  3. provide sample output corresponding to your sample input (also in CODE tags), and
  4. describe in English what processing needs to be done to produce the output you want from the input files you have supplied (including case translations, input and output field separators [and when an input file uses space as a field separator and as data in a field, explain how you know which spaces are data and which spaces are separators]).

Don't just show us the code you have written. Explain what it is trying to do and how that relates to producing the output you want. We can help you fill in missing pieces of code once we understand what you're trying to do.

If you absolutely must upload a .csv file; assuming you have the desired output in a file named output.csv, move or link it to output.txt and upload output.txt.
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to get the shell script to read the .txt file as an input/data?

i have written my shell script in notepad however i am struggling to pass the data file to be read to the script the data file is of .txt format. My target is to run the shell script from the terminal and pass 3 arguments e.g. polg@DESKTOP-BVPDC5C:~/CS1420/coursework$ bash valsplit.sh input.txt... (11 Replies)
Discussion started by: Gurdza32
11 Replies

2. Shell Programming and Scripting

Help with Shell Scrip in Masking particular columns in .csv file or .txt file using shell script

Hello Unix Shell Script Experts, I have a script that would mask the columns in .csv file or .txt file. First the script will untar the .zip files from Archive folder and processes into work folder and finally pushes the masked .csv files into Feed folder. Two parameters are passed ... (5 Replies)
Discussion started by: Mahesh G
5 Replies

3. Shell Programming and Scripting

Copy data to CSV file from txt output

Hi Everyone , Below is output from a TL1 , I want just the NE Name: and beside that the Temperature and the voltages in a csv file , Is this possible? > act-user:AB1S2a:ArshadFO:493::**********; AB1S2a 2016-02-07 10:13:24 M 493 COMPLD "ArshadFO:2016-02-07 10-04-55,0" ;... (11 Replies)
Discussion started by: adgjmpt
11 Replies

4. Shell Programming and Scripting

Conversion of xhtml data into csv format using dump utility

Hi Unix Gurus, I tried to convert the attached xhtml table content into csv file using unix shell script (lynx -dump filename) and got the below results: Title ID Owner Priority Estimate Project Change Date Changed By Complexity Create Date Created By Detail Estimate Total De tail... (6 Replies)
Discussion started by: bi.infa
6 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

Shell script for CSV conversion

thanks for allowing me join your forum i have an output of linux command "who" which provides following details..... CURRENT USER/ACCT INFO 17:31:36 up 4:49, 4 users, load average: 0.03, 0.04, 0.00 USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT root :0 - 12:59 ?xdm? 4:54 0.02s /bin/sh /usr/bi... (1 Reply)
Discussion started by: ayyappancheta
1 Replies

7. UNIX for Advanced & Expert Users

shell script to format .CSV data

Hi all, I have written a shell script to search a specified directory (e.g. /home/user) for a list of specific words (shown as ${TMPDIR}/wordlist below). The script works well enough, but I was wondering if there was a way to display the line number that the word is found on? Thanks! cat... (1 Reply)
Discussion started by: tmcmurtr
1 Replies

8. Shell Programming and Scripting

shell script to insert data from gps.txt to mysql database

Hi, I have gps receiver, by using gpsd data i can read gps log data to my database(my sql). Steps: 1. telenet localhost 2947 > gps.txt (press enter) 2. r (press enter) //then i will get the data like below in gps.txt file Trying 127.0.0.1... Connected to localhost.... (1 Reply)
Discussion started by: gudivada213
1 Replies

9. Shell Programming and Scripting

Shell script to format a .CSV data

Hi There I needed to write a Unix shell script which will pick up the data from a .CSV file and reformat it as per the requirement and write it to another .CSV file. Currently I am in the proess of Data Import to "Remedy System" (A one kind of incident mangement Application) and this... (8 Replies)
Discussion started by: Uday1982
8 Replies
Login or Register to Ask a Question