Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Insert data between comma delimiters-large file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Insert data between comma delimiters-large file
# 1  
Old 04-08-2013
Insert data between comma delimiters-large file
Having a huge file in the following format.
Code:
2,3,1,,,4
1,2,3,,,,,5,
8,7,3,4,,,,

Output needed is:
Code:
2,3,1,0.0,0.0,4
1,2,3,0.0,0.0,0.0,0.0,5,
8,7,3,4,0.0,0.0,0.0,

I have tried reading the file each line, using AWK to parse to find out ",," and then insert 0.0 . It works but very slow. Need some faster solution please.
Last edited by Franklin52; 04-09-2013 at 03:27 AM.. Reason: Please use code tags for data and code samples
# 2  
Old 04-08-2013
Can you also post the awk code that you are using?
# 3  
Old 04-08-2013
Quote:
Originally Posted by Yoda
Can you also post the awk code that you are using?
Code:
while read line
do
echo $line|awk -F"," '{
  
   for (i = 1; i <= NF; ++i) {
      if ($i == "")
         printf("0.0,")
      else {
        
            printf("%s,", $i)
      
      }
   }
   printf("\n")
}'

done <  ./test.txt

# 4  
Old 04-08-2013
You don't need a while loop to read each record and pipe it to awk. This will indeed slow down the execution speed.

Try using just an awk code and see if the performance improved:
Code:
awk -F, ' {
                for ( i = 1; i <= NF; i++ )
                {
                        if ( $i == "" )
                               $i = "0.0"
                }
} 1 ' OFS=, test.txt

# 5  
Old 04-08-2013
The output needed is
Code:
2,3,1,0.0,0.0,4
1,2,3,0.0,0.0,0.0,0.0,5,
8,7,3,4,0.0,0.0,0.0,

However I am getting
Code:
2,3,1,0.0,0.0,4
1,2,3,0.0,0.0,0.0,0.0,5,0.0
8,7,3,4,0.0,0.0,0.0,0.0

# 6  
Old 04-08-2013
That is because you have a trailing comma in your original input file:
Code:
2,3,1,,,4
1,2,3,,,,,5,
8,7,3,4,,,,

You can ignore it by running:
Code:
awk -F, '{for(i=1;i<=NF;i++) {if($i==""&&i!=NF) $i="0.0"}} 1 ' OFS=, test.txt

# 7  
Old 04-08-2013
Yes I would need the trailing comma. The idea is if I have ",," it needs to be replaced with ",0.0,"

I ran your script and it seems to work. let me try on my large file
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Insert a hyphen between two delimiters using sed

Hey guys, I have a file that is delimited by | and I am trying to write a sed command to convert this: abc|def||ghi|jkl||||mnop into this: abc|def|-|ghi|jkl|-|-|-|mnop The output I am getting out of: sed -e "s/+//g" /tmp/opt.del > /tmp/opt2.del is like: ... (9 Replies)
Discussion started by: prohank
9 Replies

2. Shell Programming and Scripting

Insert Columns before the last Column based on the Count of Delimiters

Hi, I have a requirement where in I need to insert delimiters before the last column of the total delimiters is less than a specified number. Say if the delimiters is less than 139, I need to insert 2 columns ( with blanks) before the last field awk -F 'Ç' '{ if (NF-1 < 139)} END { "Insert 2... (5 Replies)
Discussion started by: arunkesi
5 Replies

3. UNIX for Dummies Questions & Answers

Large file data handling issue

I have a single record large file, semicolon ';' and pipe '|' separated. I am doing a vi on the file. It is throwing an error "File to long" I need to actually remove the last | symbol from this file. sed -e 's/\|*$//' filename is working fine for small files. But not working on this big... (13 Replies)
Discussion started by: Gurkamal83
13 Replies

4. UNIX for Dummies Questions & Answers

insert comma

my file looks like this: 297 PC Closed 07/10/12 999000098 AMERICAN SOCIETY FOR HEALTHCAR 0.00 USD 1 NAI i want to look line this: 297,PC,Closed,07/10/12,999000098,AMERICAN SOCIETY FOR HEALTHCAR,0.00,USD,1,NAI (4 Replies)
Discussion started by: lawsongeek
4 Replies

5. Shell Programming and Scripting

Inserting additional comma delimiters in a csv file, after and before certian fields.

Hello I have a csv file which I need to insert addtional commas into. The csv is of the format field1,field2,field3,field4,...etc...,field13,field14 I need to add extra commas in each record so that the final output looks like ... (1 Reply)
Discussion started by: kamal_p_99
1 Replies

6. UNIX for Dummies Questions & Answers

insert comma in a text file

Hi all, I have a text file and I need to insert comma after every 2 digit. -1-1-1-1-1-1-1-1-1 0 0 0 -1-1-1 2 0 0 3 311-1 0 1 -1-1 021 0 011-1-1 033 0I'd like to have this: -1,-1,-1,-1,-1,-1,-1,-1,-1, 0, 0, 0 -1,-1,-1, 2, 0, 0, 3, 3,11,-1, 0, 1 -1,-1, 0,21, 0, 0,11,-1,-1, 0,33, 0Thanks for... (7 Replies)
Discussion started by: GoldenFire
7 Replies

7. Shell Programming and Scripting

Program to insert Delimiters at fixed locations in a file, Can you please Debug it for me??

Can someone please help?I have a file - fixed.txt----------------------------AABBBBCCCCCCDDDEEFFFFGGGGGGHHHIIJJJJKKKKKKLLL----------------------------To insert delimiters at fixed lengths of 2, 4, 6, 3, I created a file text1.txt as-------------------2463----------------------and trying to execute... (10 Replies)
Discussion started by: jd_mca
10 Replies

8. Shell Programming and Scripting

Pull Data After Comma if 2 word before comma

Hi, I am trying to truncate word after comma in a file ONLY if there are already 2 words BEFORE comma. If there is one word or 3 or more words BEFORE comma, then I have to leave the data AS IS. See below for example. Input File : John Smith, Manager Smith, John Frank J F K... (2 Replies)
Discussion started by: msalam65
2 Replies

9. Shell Programming and Scripting

Insert lines between delimiters

I'm working with a file like: somestuff somemorestuff ... someadditionalstuff STARTTAG ENDTAG someotherstuff somecoolstuff ... somefinalstuffI've got some text (either in a file or piped) to put between STARTTAG and ENDTAG. I was thinking something like grepping for the line number of... (2 Replies)
Discussion started by: BMDan
2 Replies

10. UNIX for Dummies Questions & Answers

How do I insert commas/delimiters in File

Hi, Newbie here. Need to convert a txt file to .csv format. There's no character to replace so not sure if I can use sed :confused: . The comma is to be inserted after every certain number of characters in each line... Help! Thanks. (4 Replies)
Discussion started by: mbelen
4 Replies
Login or Register to Ask a Question