Converting csv file to flat file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Converting csv file to flat file
# 1  
Old 09-20-2018
Converting csv file to flat file

Hi All,

I have a csv file which is comma seperated. I need to convert to flat file with preferred column length

Code:
country,id
Australia,1234
Africa,12399999

Expected output
Code:
country id
Australia 1234
Africa 12399999

the flat file should predefined length on respective columns. Here country - 1- 20 , id - 21-30
# 2  
Old 09-20-2018
Given that the sample output you provided is not formatted at all like you said you wanted and, instead, just changes the <comma>s in your CSV file to <space>s; I don't know whether you want the input data to be left-justified in the output or right-justified.

Assuming that you want left-justified text in your twenty character and ten character output fields, two (of many) simple ways to do what you want include an awk script:
Code:
awk -F, '{printf("%-20.20s%-10.10s\n",$1,$2)}' file.csv

and a shell while read loop:
Code:
while IFS=, read c i
do	printf '%-20.20s%-10.10s\n' "$c" "$i"
done < file.csv

if you want right-justified text instead of left-justified text, remove both of the minus signs from the printf format strings in either of the above scripts.
This User Gave Thanks to Don Cragun For This Post:
# 3  
Old 09-20-2018
Thanks don, Is there a way i can predine the columns length in shell, because here there are two columns. What if the columns are increased and the length needs to be defined accorindly instead of hardcoding every time
Code:
country,id,role
Australia,1234,engineer
Africa,12399999,doctor

role column will start from 31-35.
# 4  
Old 09-20-2018
Of course you can do that. But, you'll need to be much clearer about what you mean by "length needs to be determined (sic) accorindly". I'm not at all clear about how you want to fit eight characters from engineer or six characters from doctor in a five character output field. (And you still haven't told us whether you want left-justified or right-justified text in fields that do not completely fill the output field.)

Note that in awk you can use length($1) to get the number of characters contained in field 1 on the current input line and in shell you can use ${#c} to get the number of characters contained the shell variable c. That should be everything you need to determine how many characters are in your widest input fields and to produce a format string that will give you appropriately sized output fields.

Why don't you try clearly specifying your output requirements, and try to write a corresponding awk or shell (or both) script to produce the output you want. If you can't make it work, show us where you get stuck.
# 5  
Old 09-20-2018
Basically the text file have fixed length like country - 1-20,id 21-25,role - 26-30.
Country - will accept only 20 characters
id - 5 characters
role - 5 characters
# 6  
Old 09-20-2018
Quote:
Originally Posted by rohit_shinez
Basically the text file have fixed length like country - 1-20,id 21-25,role - 26-30.
Country - will accept only 20 characters
id - 5 characters
role - 5 characters
Great. I can't wait to see what you come up with to meet these requirements. How you will fit 12399999 into a 5 character id field and how you will fit engineer and doctor into a 5 character role field is beyond me. Please explain how you determined that two fields need to be 5 characters wide when they need to hold values that contain more than 5 characters.

And, of course, you still haven't specified justification for values that don't completely fill a field???

Please show us the code you've come up with to meet these requirements!
These 2 Users Gave Thanks to Don Cragun For This Post:
# 7  
Old 09-20-2018
Sorry to below example the lenght are fixed like
Country - will accept only 20 characters
id - 20 characters
role - 20 characters

Code:
country,id,role
Australia,1234,engineer
Africa,12399999,doctor

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

reading a csv file and creating a flat file

hi i have written a script for reading a csv file and creating a flat file, suggest if this script can be optimized #---------------- FILENAME="$1" SCRIPT=$(basename $0) #-----------------------------------------// function usage { echo "\nUSAGE: $THIS_SCRIPT file_to_process\n"... (3 Replies)
Discussion started by: mprakasheee
3 Replies

2. Shell Programming and Scripting

Help with converting XML to Flat file

Hi Friends, I want to convert a XML file to flat file. Sample I/p: <?xml version='1.0' encoding='UTF-8' ?> <DataFile xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' contactCount='4999' date='2012-04-14' time='22:00:14' xsi:noNamespaceSchemaLocation='gen .xsd'> <Contact... (3 Replies)
Discussion started by: karumudi7
3 Replies

3. UNIX for Advanced & Expert Users

Converting the date format in a flat file

Hi All, I am new to this forum, could any one help me out in resolving the below issue. Input of the flat file contains several lines of text for example find below: 5022090,2,4,7154,88,,,,,4/1/2011 0:00,Z,L,2 5022090,3,1,6648,88,,,,,4/1/2011 0:00,Z,,1... (0 Replies)
Discussion started by: av_sagar
0 Replies

4. Shell Programming and Scripting

Converting a flat file in XML

Hello Friends, I am new to UNIX shell scripting. Using bash....Could you please help me in converting a flat file into an XML style output file. Flat file: (Input File entries looks like this) John Miller: 617-569-7996:15 Bunting lane, staten Island, NY: 10/21/79: 60600 The... (4 Replies)
Discussion started by: humkhn
4 Replies

5. UNIX for Dummies Questions & Answers

cleaning up spaces from fixed width file while converting to csv file

Open to a sed/awk/or perl alternative so that i can stick command into my bash script. This is a problem I resolve using a combination of cut commands - but that is getting convoluted. So would really appreciate it if someone could provide a better solution which basically replaces all... (3 Replies)
Discussion started by: svn
3 Replies

6. UNIX for Advanced & Expert Users

Converting .csv file into .xls file and send it to inbox

Hi All, I wrote a script to extract data from Oracle DB and place it in a text file , and I have coverted .txt file into comma seperated .csv file and I sent it to my mail box . I can get .xls file in my inbox.I am getting all data in same column and in different rows , without column... (1 Reply)
Discussion started by: krthkmuthu
1 Replies

7. UNIX for Advanced & Expert Users

Problem in converting password protected excel file to csv file in unix

I need to convert a password protected excel file which will be in UNIX server to a comma separated file. For this I need to open the excel file in UNIX box but the UNIX box doesn't prompt for password instead it is opened in an encrypted manner. I could manually ftp the excel file to local... (2 Replies)
Discussion started by: Devivish
2 Replies

8. Shell Programming and Scripting

Converting Column to Rows in a Flat file

Hi, Request To guide me in writing a shell program for the following requirement: Example:if the Input File contains the follwing data Input File Data: 80723240029,12,323,443,88,98,7,98,67,87 80723240030,12,56,6,,,3,12,56,6,7,2,3,12,56,6,7,2,3,88,98,7,98,67,87... (5 Replies)
Discussion started by: srinikal
5 Replies

9. Shell Programming and Scripting

Awk to convert a flat file to CSV file

Hi , I have a file with contents as below: Contract Cancellation Report UARCNCL LOS CODE DATE REAS TYPE AMOUNT AMOUNT LETTER BY ========= ======= ==== ==== ==== ========= ==== ==== 8174739 7641509 1S NONE CRCD 30-JUN-2008 NPAR N .00 .00 CCAN 8678696 8091709 1S NONE DDEB 30-JUN-2008... (14 Replies)
Discussion started by: rkumudha
14 Replies

10. Shell Programming and Scripting

Converting Pivot file to flat file

I have a file in this format. P1 P2 P3......................... A001 v11 v21 v31...................... A002 v12 v22 v32............................ A003 v13 v23 v33.......................... A004 v14 v24 v34.............................. . . . A00n... (2 Replies)
Discussion started by: vskr72
2 Replies
Login or Register to Ask a Question