Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Read csv file, convert the data and make one text file in UNIX shell scripting

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read csv file, convert the data and make one text file in UNIX shell scripting
# 1  
Old 11-01-2016
Read csv file, convert the data and make one text file in UNIX shell scripting
I have input data looks like this which is a part of a csv file
Code:
7,1265,76548,"0102:04"
8,1266,76545,"0112:04"

I need to make the output data should look like this and the output data will be part of text file:

Code:
7|1265000  |7654899   |A|
8|12660000   |76545999    |B|

The logic behind the output data

1st output field= 1st input field

2nd output field= 2nd input field+(1st input field-length(2nd input field))zeros+(1st input field-length(3rd input field))spaces

3rd output field= 3rd input field+(1st input field-length(3rd input field))nines+(1st input field-length(2nd input field))spaces

4th Output field=
If the 3rd field of the quoted string is 0, 4th output will be A
If the 3rd field of the quoted string is 1, 4th output will be B


How can I do it using UNIX shell scripting?



Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!
Last edited by RudiC; 11-01-2016 at 10:27 AM.. Reason: Added CODE tags.
# 2  
Old 11-01-2016
That problem can certainly be solved in *nix shell or text utilities. What escapes me is the logics defining the second and third output field composition. Why does the first record have three zeroes and two nines, and the second four zeroes and three nines? And, if you are talking of "the 3rd field of the quoted string", is that the third character?
# 3  
Old 11-01-2016
Quote:
Originally Posted by RudiC
That problem can certainly be solved in *nix shell or text utilities. What escapes me is the logics defining the second and third output field composition. Why does the first record have three zeroes and two nines, and the second four zeroes and three nines? And, if you are talking of "the 3rd field of the quoted string", is that the third character?

Hi Rudi,


first record has three zeros because (7-length of (1265)) that is (7-4)=3 zeros
first record has two nines because (7-length of 76548)) that is 7-5=2 nines


And for 2 nd record

secondrecord has four zeros because (8-length of (1266)) that is (8-4)=4 zeros
second record has three nines because (8-length of 76545)) that is 8-5=3 nines


And yes, I was trying to say that "the 3rd character of the quoted string"

i.e for 1st field it will be 0
for 2nd field it will be 1

And I am currently using bash shell.I have tried to use the printf command for padding zeros and nines. But it does not work.

i have calculated the number of zeros diff_in_length_zero variable and the number of nines in diff_in_length_nine variablee and tried the following command

Code:
$(printf '%*s' {diff_in_length_zero} '0') 
$(printf '%*s' {diff_in_length_nine} '9')

But it does not work.Previously I used the same command for adding space in a line.

Thanks,
RJG
# 4  
Old 11-01-2016
OK, understood, your syntax was a bit difficult to interpret. Mind an awk solution?
Code:
awk -F, -vOFS="|" '
        {$2 = sprintf ("%s%0*d%*s", $2, $1-length($2), 0, $1-length($3), " ")
         $3 = sprintf ("%s%d%*s", $3, 10^($1-length($3))-1, $1-length($2), " ")
         $4 = substr($4, 4, 1)=="0"?"A":"B"
         $5 = ""
        }
1
' file
7|1265000  |7654899  |A|
8|12660000   |76545999   |B|

# 5  
Old 11-01-2016
In fact, the modification of $2 prevents the correct calculation of $3. Try instead:
Code:
awk -F, -vOFS="|" '
        {L2 = $1-length($2)
         L3 = $1-length($3)
         $2 = sprintf ("%s%0*d%*s", $2, L2, 0, L3, "")
         $3 = sprintf ("%s%d%*s", $3, 10^L3-1, L2, "")
         $4 = substr($4, 4, 1)=="1"?"B":"A"
         $5 = ""
        }
1
' file
7|1265000  |7654899   |A|
8|12660000   |76545999    |B|

# 6  
Old 11-02-2016
Quote:
Originally Posted by RudiC
In fact, the modification of $2 prevents the correct calculation of $3. Try instead:
Code:
awk -F, -vOFS="|" '
        {L2 = $1-length($2)
         L3 = $1-length($3)
         $2 = sprintf ("%s%0*d%*s", $2, L2, 0, L3, "")
         $3 = sprintf ("%s%d%*s", $3, 10^L3-1, L2, "")
         $4 = substr($4, 4, 1)=="1"?"B":"A"
         $5 = ""
        }
1
' file
7|1265000  |7654899   |A|
8|12660000   |76545999    |B|



Thanks Rudi for your reply

---------- Post updated 11-02-16 at 06:09 AM ---------- Previous update was 11-01-16 at 10:08 AM ----------

Quote:
Originally Posted by RudiC
In fact, the modification of $2 prevents the correct calculation of $3. Try instead:
Code:
awk -F, -vOFS="|" '
        {L2 = $1-length($2)
         L3 = $1-length($3)
         $2 = sprintf ("%s%0*d%*s", $2, L2, 0, L3, "")
         $3 = sprintf ("%s%d%*s", $3, 10^L3-1, L2, "")
         $4 = substr($4, 4, 1)=="1"?"B":"A"
         $5 = ""
        }
1
' file
7|1265000  |7654899   |A|
8|12660000   |76545999    |B|


Quote:
Originally Posted by RJG
Thanks Rudi for your reply
Hi Rudi,

I have tried with your code; It worked fine with the given input set.
But if the input and output field numbers are changed, then I am facing issue .

I tried with the following input data set:

Code:
7,1265,76548,"0102:04"
8,1266,76545,"0112:04"

And modified output set will be

Code:
E|1265000  |7654899   |7|A|
E|12660000   |76545999    |8|B|

where
1st field 'E' is common for both lines
4th field will be the 1st input field

Rest of the field logics are same

$1(E) field of the o/p file is different right now. And the $1 field of the i/p file will be reused in reused in 4th field of o/p file.

I have not worked much with AWk. Could you please help me.
# 7  
Old 11-02-2016
May i ?

with little change and without interim you can use RudiC solution, following might work:

Code:
awk -F, -vOFS="|" '
        {L2 = $1-length($2)
         L3 = $1-length($3)
         $5 = substr($4, 4, 1)=="1"?"B":"A"
         $4 = $1
	 $1 = "E" 
         $2 = sprintf ("%s%0*d%*s", $2, L2, 0, L3, "")
         $3 = sprintf ("%s%d%*s", $3, 10^L3-1, L2, "")
        }
1
'  file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell - Read a text file with two words and extract data

hi I made this simple script to extract data and pretty much is a list and would like to extract data of two words separated by commas and I would like to make a new text file that would list these extracted data into a list and each in a new line. Example that worked for me with text file... (5 Replies)
Discussion started by: dandaryll
5 Replies

2. UNIX for Dummies Questions & Answers

Shell script to read lines in a text file and filter user data Shell Programming and Scripting

sxsaaas (3 Replies)
Discussion started by: VikrantD
3 Replies

3. Shell Programming and Scripting

Shell scripting:from text file to CSV

Hello friends, I have a file as follows: "empty line" content1 content2 content3 content1 content2 content3 content1 content2 content3 It starts with an empty line, how can i get a csv like this: (12 Replies)
Discussion started by: kraterions
12 Replies

4. Shell Programming and Scripting

Awk to convert a text file to CSV file with some string manipulation

Hi , I have a simple text file with contents as below: 12345678900 971,76 4234560890 22345678900 5971,72 5234560990 32345678900 71,12 6234560190 the new csv-file should be like: Column1;Column2;Column3;Column4;Column5 123456;78900;971,76;423456;0890... (9 Replies)
Discussion started by: FreddyDaKing
9 Replies

5. Shell Programming and Scripting

Request for file read option in Unix shell scripting

Hi Friends, I would like to read all the record from one txt file to other file txt For example I have two txt file a.txt and b.txt. I need to read a.txt record by record and I need add the system date @ end of each record before moving it to b.txt. Could you please share the coding for... (4 Replies)
Discussion started by: vinoth124
4 Replies

6. Shell Programming and Scripting

Read data from .csv file through shell script & modify

I need to read data from a file called "test.csv" through shell script where the file contains values like name,price,descriptor etc. There are rows where descriptor (& in some rows name) are written as string & other characters like "car_+" OR "bike*" etc where it should contains strings like... (3 Replies)
Discussion started by: raj100
3 Replies

7. Shell Programming and Scripting

Exporting data as a CSV file from Unix shell script

Friends...This is the first time i am trying the report generation using shell script... any suggestions are welcome. Is there a way to set the font size & color when i am exporting the data from unix shell script as a CSV file ? The following sample data is saved as a .csv file in the... (2 Replies)
Discussion started by: appu2176
2 Replies

8. Shell Programming and Scripting

How to read the data from the text file in shell script?

I am having one text file and i need to read that data from my shell script. I will expain you the scenario: Script look like: For name type 1: For age type 2: For Salary type3: echo "Enter the input:" read the data if input is 1 then go to the Text file and print the... (2 Replies)
Discussion started by: dineshmurs
2 Replies

9. Shell Programming and Scripting

Shell script to read lines in a text file and filter user data

hi all, I have this file with some user data. example: $cat myfile.txt FName|LName|Gender|Company|Branch|Bday|Salary|Age aaaa|bbbb|male|cccc|dddd|19900814|15000|20| eeee|asdg|male|gggg|ksgu|19911216||| aara|bdbm|male|kkkk|acke|19931018||23| asad|kfjg|male|kkkc|gkgg|19921213|14000|24|... (4 Replies)
Discussion started by: srimal
4 Replies

10. Shell Programming and Scripting

shell script to read data from text file and to load it into a table in TOAD

Hi....can you guys help me out in this script?? Below is a portion text file and it contains these: GEF001 000093625 MKL002510 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL003604 000001 000000 000000 000000 000000 000000 000001 GEF001 000093625 MKL005675 000001... (1 Reply)
Discussion started by: pallavishetty
1 Replies
Login or Register to Ask a Question