converting text to csv format


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting converting text to csv format
# 1  
Old 05-18-2006
converting text to csv format

I am trying to check each line and based on first two digits, the comma needs to be place. I checked in the earlier post where the text is converted to csv with a tab delimited.
Here is the test file that needs to be changed to csv

11 051701
22 051701
330123405170105170112345
0100001123456789012987654321
0100002123456789012987654321
0100003123456789012987654321
0100004123456789012987654321
0100017123456789012987654321
.......


if the first 2 digits are 11, then a comma is placed after 2nd column and just before the 051701. If the first two digits are 01 then the commas should be place after 01, another comma after 00001, another after 123456789012. In short the output should look like this

11, ,051701
22, ,051701
33,01234,051701,051701,12345
01,00001,123456789012,987654321
01,00002,123456789012,987654321
01,00003,123456789012,987654321
01,00004,123456789012,987654321
01,00017,123456789012,987654321
.......


Any help is appreciated. Thanks
~
# 2  
Old 05-18-2006
here's something to start with:

nawk -f gt.awk myFile.txt

gt.awk:
Code:
/^11/ && NF == 2 { $1=$1 "," ;$2="," $2; print; next }
/^01/ {
   $0 = substr($0,1,2) "," substr($0,3,5) "," substr($0,8,12) "," substr($0, 13)
   print
}

# 3  
Old 05-19-2006
MySQL

Thanks a lot. I was able to modify accordingly with my text file and was able to get the desired csv format file. Appreciate and thank you for your quick response.
# 4  
Old 06-07-2006
converting text to csv format

I was able to get the csv file without any problem. I have one quick question. Is it possible to take the 2nd column on line 3, '01234' and have it on each and every line where you see '01' as the first two characters, like for example

11, ,051701
22, ,051701
33,01234,051701,051701,12345
01,00001,123456789012,987654321
01,00002,123456789012,987654321
01,00003,123456789012,987654321
01,00004,123456789012,987654321
01,00017,123456789012,987654321
.......


to

11, ,051701
22, ,051701
33,01234,051701,051701,12345
01234,01,00001,123456789012,987654321
01234,01,00002,123456789012,987654321
01234,01,00003,123456789012,987654321
01234,01,00004,123456789012,987654321
01234,01,00017,123456789012,987654321
.......
# 5  
Old 06-07-2006
this one,

Code:
sed -n /^01/p filename | sed "s/^01/`awk -F"," '{ if(NR==3) { print $2 } }' filename`/g"

# 6  
Old 06-07-2006
[not tested]
nawk -f gt.awk filename

gt.awk:
Code:
BEGIN {
   FS=OFS=","
}
FNR==3 { num=$2 }
FNR != 3 && $1 == "01" { $1 = num OFS $1 }
1


Last edited by vgersh99; 06-07-2006 at 11:49 AM..
# 7  
Old 06-07-2006
Thanks for your reply. Actually the suggestion you gave is replacing the value from '01' to '01234' and eliminating the other rows. Is there any way to keep the other rows as is and concatenate the '01234' from '33' line to '01' lines. This should be included in the code that was suggested earlier by vgersh99 in gt.awk
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting csv to html format

Below is the code I have - How can I convert the data in the csv into 3 tables in html. instead of 1 table. Attached is the format I am getting. (1 Reply)
Discussion started by: archana25
1 Replies

2. Shell Programming and Scripting

Converting data for text file to csv

Gents Using the script attached (raw2csv). i use to create the file .csv.. The input file is called 201.raw. Kindly can you check if there is easy way to do it. The script works fine but takes a lot time to process Thanks for your help (8 Replies)
Discussion started by: jiam912
8 Replies

3. Shell Programming and Scripting

Converting text files to xls through awk script for specific data format

Dear Friends, I am in urgent need for awk/sed/sh script for converting a specific data format (.txt) to .xls. The input is as follows: >gi|1234|ref| Query = 1 - 65, Target = 1677 - 1733 Score = 8.38, E = 0.6529, P = 0.0001513, GC = 46 fd sdfsdfsdfsdf fsdfdsfdfdfdfdfdf... (6 Replies)
Discussion started by: Amit1
6 Replies

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

5. Shell Programming and Scripting

Converting CSV to ascii format

Hej All, I have a file like this which is a comma dilimited: input: 6000318,-263.011520678,-59.05869872409,587.67924868792 6000319,-265.6996842902,-50.24902479999,590.65693082607 6000320,-238.1333898366,-288.801232595,633.75332173496... (5 Replies)
Discussion started by: Johanni
5 Replies

6. Shell Programming and Scripting

Converting text to certain format

Hi Forum. I'm trying to convert the following text using "sed" or "awk" or "tr" but with no luck. From: EDW_WHOLESALE_VORTEX To: __ Any help is greatly appreciated. (9 Replies)
Discussion started by: pchang
9 Replies

7. Shell Programming and Scripting

Format text to bold from perl script to csv

Hi everyone, is there any way in perl using which we can print the selective words in bold when we write the output to a csv file? Please find the example below 1. Filename: A 2. name age 12 3. city add 23 Line1 should only be bold. Outputs from other files being read in the... (2 Replies)
Discussion started by: ramakanth_burra
2 Replies

8. Shell Programming and Scripting

Sybase Interface file and converting in text format.

Does anyone knows how to decode the address in interface file using shell , i have done it using perl but can it be done in shell. master tli tcp /dev/tcp \x00021004ac1414230000000000000000 query tli tcp /dev/tcp \x00021004ac1414230000000000000000 (0 Replies)
Discussion started by: dinjo_jo
0 Replies

9. Shell Programming and Scripting

converting config file to csv format

Hello, For 2 days now i've been searching for a solution to this. I am now beginning to doubt this is even possible. It's even harder when you don't know how to search for it. (which keywords generate enough relevancy etc..) I need to parse a config file to generate a CSV file in return. It... (7 Replies)
Discussion started by: zer0dvide
7 Replies

10. UNIX for Advanced & Expert Users

converting PDF to text, rtf doc format

Hi all Is there any program which can convert PDF to word processor file ? If the PDF has smart quotes, bullet icons, copyright and trademark symbols, etc. what happens to them intext format? So ideally would like to conver into rtf or doc. Thanks SS (1 Reply)
Discussion started by: saurya_s
1 Replies
Login or Register to Ask a Question