Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Conversion of spaces Text file into CSV format file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Conversion of spaces Text file into CSV format file
# 1  
Old 04-29-2011
Conversion of spaces Text file into CSV format file
Input file (each line is separaed by spaces )given below:

Code:
Name                    Domain                                           Contact Phone Email                             Location
----------------------- ------------------------------------------------ ------- ----- --------------------------------- ----------------------------------------
clients                 /                                                                                                5fdf97049abdd582976d954bba8ff256c4beedd8
GEN-I                   /                                                                                                Service Provider
MC_RETIRED              /                                                                                                acb53a6470ba5fdd6efc93a0d7228e9e014a199e
REPLICATE               /                                                                                                5cf5275db7ceb0a7c8e29c7d66fd548f61ced14d
CUSTOMER_01             /GEN-I                                                                                           <<Cust01 PARTY NUMBER>>
CUSTOMER_02             /GEN-I                                                                                           <<Cust02 PARTY NUMBER>>
RCB_INSTANCE_01         /GEN-I/CUSTOMER_01                                             "Product Instance 01 Description" <<Cust01_Inst01 PRODUCT ID>>
RCB_INSTANCE_02         /GEN-I/CUSTOMER_01                                             "Product Instance 02 Description" <<Cust01_Inst02 PRODUCT ID>>
RCB_INSTANCE_01         /GEN-I/CUSTOMER_02                                             "Product Instance 01 Description" <<Cust02_Inst01 PRODUCT ID>>
wn1naeavu101.sdp.net.nz /REPLICATE                                                                                       1eb4ad6f161e2be6644c13270adaa8dc7e7aa6a8
clients                 /REPLICATE/wn1naeavu101.sdp.net.nz                                                               d93a694ed05147f8f9895ffa57fb91a0b15ed25d
GEN-I                   /REPLICATE/wn1naeavu101.sdp.net.nz                                                               a4af9e4cc65ba65550b3365759280cf1197cb7da
MC_RETIRED              /REPLICATE/wn1naeavu101.sdp.net.nz                                                               892e8133bb46f16caeb4667811457e5a9126838c
REPLICATE               /REPLICATE/wn1naeavu101.sdp.net.nz                                                               b983f7c9a69545b4f0a63a30f28447279a4524c2
WLGtest                 /REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I                                                         4d6c41eb132caae70e8a9f990201e5c83c1104b5
RCB_INSTANCE_01         /REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I/CUSTOMER_02                                             0aec9c024ae8c9b60a81a371980e58be0eb2f27e
RCB_INSTANCE_02         /REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I/CUSTOMER_03                                             95e059107f165bb603aabae42b59c8f51e64e855

NOte: Output file shoud be CSV format and
an error check for the number of "commas" in each line of the created csv output file.
There should be 5 commas in every line of the csv file. If there are less or more that this then the script should log an error and preferably rename the created file to .err (for example).
command is below . Can somebody help me in commas condition.
Code:
# egrep -v '^$|^#' inputfile.txt | tr ' ' ',' >outputfile.csv


Moderator's Comments:
Mod Comment
Please use code tags when posting data and code samples!
Last edited by vgersh99; 04-29-2011 at 12:39 PM.. Reason: code tags, please!
# 2  
Old 04-29-2011
You have a 'fixed width' file. Search forums for the applicable solutions/hints.
# 3  
Old 04-29-2011
I think you should first try to format it directly in SQL code using all the available formatting option
newpage, wrap, linesize, pagesize, heading ...
# 4  
Old 04-29-2011
Output file in CSV format below(each line should have 5 commas ., if more than 5 commas then shell script should thrown error..please help wit the code):

Code:
Name,Domain,Contact,Phone,Email,Location
-----------------------,------------------------------------------------,-------,-----,---------------------------------,----------------------------------------
clients,/,,,,5fdf97049abdd582976d954bba8ff256c4beedd8
GEN-I,/,,,,Service Provider
MC_RETIRED,/,,,,acb53a6470ba5fdd6efc93a0d7228e9e014a199e
REPLICATE,/,,,,5cf5275db7ceb0a7c8e29c7d66fd548f61ced14d
CUSTOMER_01,/GEN-I,,,,<<Cust01 PARTY NUMBER>>
CUSTOMER_02,/GEN-I,,,,<<Cust02 PARTY NUMBER>>
RCB_INSTANCE_01,/GEN-I/CUSTOMER_01,,,"Product Instance 01 Description",<<Cust01_Inst01 PRODUCT ID>>
RCB_INSTANCE_02,/GEN-I/CUSTOMER_01,,,"Product Instance 02 Description",<<Cust01_Inst02 PRODUCT ID>>
RCB_INSTANCE_01,/GEN-I/CUSTOMER_02,,,"Product Instance 01 Description",<<Cust02_Inst01 PRODUCT ID>>
wn1naeavu101.sdp.net.nz,/REPLICATE,,,,1eb4ad6f161e2be6644c13270adaa8dc7e7aa6a8
clients,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,d93a694ed05147f8f9895ffa57fb91a0b15ed25d
GEN-I,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,a4af9e4cc65ba65550b3365759280cf1197cb7da
MC_RETIRED,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,892e8133bb46f16caeb4667811457e5a9126838c
REPLICATE,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,b983f7c9a69545b4f0a63a30f28447279a4524c2
WLGtest,/REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I,,,,4d6c41eb132caae70e8a9f990201e5c83c1104b5
RCB_INSTANCE_01,/REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I/CUSTOMER_02,,,,0aec9c024ae8c9b60a81a371980e58be0eb2f27e
RCB_INSTANCE_02,/REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I/CUSTOMER_03,,,,95e059107f165bb603aabae42b59c8f51e64e855

Last edited by Franklin52; 04-29-2011 at 01:58 PM.. Reason: Code tags
# 5  
Old 04-29-2011
Code:
awk -F, 'NF!=6{print "Error line " NR " : " $0}' youfile.csv

# 6  
Old 04-29-2011
its not working ..The input file is pasted with sapces and i need output file in CSV format.
egrep -v '^$|^#' inputfile.txt | tr ' ' ',' >outputfile.csv is working but all spaces are replaced by commas . i need onmy one comma between each field.so total 5 commas between 5 fields..
# 7  
Old 04-29-2011
What is the max length of each field ?
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Conversion of Binary csv file

Hello, I have a binary csv file that was created on 'Red Hat Enterprise Linux Server release 6.6'. Now we have transferred all files on Ubuntu 16.04.2 LTS/xenial On opening the file in Ubuntu, there are special characters ... (8 Replies)
Discussion started by: nans
8 Replies

2. Shell Programming and Scripting

Format problem while converting text file to csv

Hi , I need a help in following scenario.I tried searching in google but couldn't able to find the exact answer. Sorry if i am re-posting already answered query. While i am trying to convert into log file into csv i couldn't able to get the format which i am looking for. I converted file... (4 Replies)
Discussion started by: varmas424
4 Replies

3. Shell Programming and Scripting

Match list of strings in File A and compare with File B, C and write to a output file in CSV format

Hi Friends, I'm a great fan of this forum... it has helped me tone my skills in shell scripting. I have a challenge here, which I'm sure you guys would help me in achieving... File A has a list of job ids and I need to compare this with the File B (*.log) and File C (extend *.log) and copy... (6 Replies)
Discussion started by: asnandhakumar
6 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 spaces into CSV format file

INput file attached in thread : Column widths at 24,73,82,87,121 characters (sed 's/./,/24;s/./,/73;s/./,/81;s/./,/87;s/./,/121;s/ *, */,/g' fixedinputfile >output.csv ). The client wants instead of hard coding the column widths as they are not fixed .he has given the hint stating that ( ... (3 Replies)
Discussion started by: sreenath1037
3 Replies

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

7. Programming

awk script to convert a text file into csv format

hi...... thanks for allowing me to start a discussion i am collecting usb usage details of all users and convert it into csv files so that i can export it into some database.. the input text file is as follows:- USB History Dump by nabiy (c)2008 (1) --- Kingston DataTraveler 130 USB... (2 Replies)
Discussion started by: certteam
2 Replies

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

9. Shell Programming and Scripting

Flat file to csv conversion

Hi Guy's can someone help me in converting the following I have a flat text file which has several thousand lines which I need to convert to a csv it's got a consistent format but basically want every time it hit's txt to create a new line with the subsequent lines comma delimited for example ... (6 Replies)
Discussion started by: p1_ben
6 Replies

10. Shell Programming and Scripting

Conversion of .xls file to .csv file

Hi Folks, I've to convert manually couple of *.xls files to *.csv files everyday :( so i was just wondering if anyone could just help me with a shell script or awk script to automate the process :) PS : Problem is that i cannot use any third party software for the conversion. Thanking... (1 Reply)
Discussion started by: chaturvedi
1 Replies
Login or Register to Ask a Question