Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Randomly inserting extra columns into csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Randomly inserting extra columns into csv file
# 1  
Old 09-19-2013
Code Randomly inserting extra columns into csv file
Hi Tech Guru,

I have a test file as below [in reality its a very big file], which needs some more fields to be populated randomly :

Code:
dks3243;12;20130823;1420;25m;0;syt
dks3243;rocy;10
dks3243;kiop;18
sde21p4;77;20151210;8479;7py;9;vfr
sde21p4;temp;67
sfq6i01;12;20120123;3412;4rd;7;jui
sfq6i01;uymk;90
sfq6i01;kiop;51



requirement 1) I need to insert 3 columns with random date values [format yyyymmdd] inside one test file.
Condition:
A. These 3 new random date fields should be inserted only if the total number of the columns are more than 3.
B. These new columns should be insert in the position of 5,6,7 in test file.
C. I need to insert a flag value 'Y' or 'N' as new position 9. [if possible 'Y' for some lines and 'N' for some lines randomly]

Code:
dks3243;12;20130823;1420;20120101;20141231;20150812;25m;J;0;syt
dks3243;rocy;10
dks3243;kiop;18
sde21p4;77;20151210;8479;20080812;20111012;20030218;7py;N;9;vfr
sde21p4;temp;67
sfq6i01;12;20120123;3412;20070315;20180918;20121217;4rd;Y;7;jui
sfq6i01;uymk;90
sfq6i01;kiop;51




Shell script, sed, awk or perl any thing is OK for me to achieve this tough work. Any help will be much appreciated.

Thanks,
Lokesh
Last edited by Lokesha; 09-19-2013 at 07:02 PM.. Reason: Correction - changed flag to 'Y' from 'J'.
# 2  
Old 09-19-2013
Here is a GNU awk solution, note 1167573600 and 1577800800 are your starting and ending dates:

Code:
$ date -d 'Jan 1 2007' +%s
1167573600

$ date -d 'Jan 1 2020' +%s
1577800800

Code:
awk -F ';' 'BEGIN {
   OFS=FS
   sd=1167573600
   ed=1577800800
   srand()
}
NF>3{
   d1=strftime("%Y%m%d",rand()*(ed-sd)+sd)
   d2=strftime("%Y%m%d",rand()*(ed-sd)+sd)
   d3=strftime("%Y%m%d",rand()*(ed-sd)+sd)
   if(NF>6) $6=(rand()>0.5?"Y":"N") OFS $6
   else $6=rand()>0.5?"Y":"N"
   $4=$4 OFS d1 OFS d2 OFS d3
} 1' infile

# 3  
Old 09-19-2013
solution not worked and got error!
I've inserted your code inside 'addColumn.sh' and tried, but got the error:

Code:
awk -F ';' 'BEGIN {
   OFS=FS
   sd=1167573600
   ed=1577800800
   srand()
}
NF>3{
    d1=strftime("%Y%m%d",rand()*(ed-sd)+sd)
   d2=strftime("%Y%m%d",rand()*(ed-sd)+sd)
   d3=strftime("%Y%m%d",rand()*(ed-sd)+sd)
   if(NF>6) $6=(rand()>0.5?"Y":"N") OFS $6
   else $6=rand()>0.5?"Y":"N"
   $4=$4 OFS d1 OFS d2 OFS d3
} 1' text.txt

where text.txt contains the sample file contents mentioned in my first post.

$ addColumn.sh
awk: syntax error near line 1
awk: bailing out near line 1
# 4  
Old 09-19-2013
Hi
In bash and sed (gnu):
Code:
sed 's/\(\([^;]*;\)\{4\}\)\([^;]*;\)\(.*\)/echo -n "\1"$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")"\3YESNO$(($RANDOM%2));\4"/e;s/YESNO0/N/;s/YESNO1/Y/' file

But slow...Smilie

Regards.
# 5  
Old 09-19-2013
Solution did not work and errored !
Thanks for the reply, but your solution didn't work and errored!

sed 's/\(\([^;]*;\)\{4\}\)\([^;]*;\)\(.*\)/echo -n "\1"$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")"\3YESNO$(($RANDOM%2));\4"/e;s/YESNO0/N/;s/YESNO1/Y/' text.txt


Error:

sed: command garbled: s/\(\([^;]*;\)\{4\}\)\([^;]*;\)\(.*\)/echo -n "\1"$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")$(date -d-$((${RANDOM}%4000))day "+%Y%m%d;")"\3YESNO$(($RANDOM%2));\4"/e;s/YESNO0/N/;s/YESNO1/Y/

SmilieSmilieSmilie
# 6  
Old 09-19-2013
What OS are you on?
# 7  
Old 09-19-2013
Work fine at home, I use gnu sed (4.2.1) and bash.

Regards.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need to Insert three extra columns in csv file

Hello Experts, I got a requirement i have a input file which am getting from different source,Now i want to add extra 3 columns to this file like BASE,ACTUAL and DATE. Input File Looks like QUAL CHGE TYP LAW COM1 COM2 A 1 X SED HO ASE B 3 Z CDE SE ... (5 Replies)
Discussion started by: ahmed.vaghar
5 Replies

2. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

3. Shell Programming and Scripting

Inserting blank columns in already present CSV file

Hi, i have a csv file which have headers and values of it like below : headers --> CI Ref SerialNumber LastScanDate values --> VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000 i want to have a above csv in below format : headers --> CI Name CI Description CI Ref... (6 Replies)
Discussion started by: omkar.jadhav
6 Replies

4. Shell Programming and Scripting

Inserting new fields to a csv file

hi I have a csv file with few rows > cat job_stat 1,jobname1,somthing,somthing 2,jobname2,somthing,somthing 3,jobname3,somthing,somthing 4,jobname4,somthing,somthing I want to add few columns after the 2nd column and then append rest of the columns after the 3rd newly added... (3 Replies)
Discussion started by: midhun19
3 Replies

5. UNIX for Dummies Questions & Answers

To Add extra commas to a CSV file.

Hi All, I got this requirement to process a complex CSV file. Eg File. Line 1: Name:,XYz Line 2: Age:,15 Line 3: Grade:,7 Line 4: Line 5: English, Maths, Science,Spanish Line 6:10,11,13,14 As you can see the maximum column is 4 . The file i need to make is Line 1: Name:,XYz,,... (12 Replies)
Discussion started by: chillblue
12 Replies

6. UNIX for Dummies Questions & Answers

To Add extra commas to a CSV file using 2 files...

Hi , Based on my previous requirement the code works fine for comma as delimiter. Now my Req is widened up a bit .. There will be two set of files .. one with comma as delimiter and other with semi-colon ; as delimiter. Second Sample file. With Double Quotes (Semi-Colon... (1 Reply)
Discussion started by: chillblue
1 Replies

7. Shell Programming and Scripting

Adding Extra Commas to a CSV file

Trying in this forum. Not sure if it is permitted.... but in need of help. Please find the requirements in the below link. https://www.unix.com/unix-dummies-questions-answers/191503-add-extra-commas-csv-file-2.html#post302665179 Thanks in Advance. (1 Reply)
Discussion started by: chillblue
1 Replies

8. Shell Programming and Scripting

Ascii Mode appending extra records to csv file

I am relatively new to this forum and Unix scripting. ksh script: part 1 :will call a PL\SQL program will create 3 CSV file at the unix directory. part 2 : will sftp the files to the EFT server. Once the EFT server receives these file , it will transfer them to a shared windows folders. ... (3 Replies)
Discussion started by: developerpa
3 Replies

9. Shell Programming and Scripting

Inserting 2 columns from a file to another with nawk

Hello all, I have these 2 files File1 123 100 456 200 789 300 File2 |1|2|3||4|5||6| |1|2|3||4|5||6| |1|2|3||4|5||6| I need an output like : |1|2|3|123|4|5|100|6| |1|2|3|456|4|5|200|6| |1|2|3|789|4|5|300|6| (3 Replies)
Discussion started by: rany1
3 Replies

10. Shell Programming and Scripting

use awk to read randomly located columns in an excel file

Hi, I have an excel file that have a random count of columns/fields and what im trying to do is to only retrieve all the rows under 2 specific field headers. I can use the usually command for awk which is awk 'print{ $1 $2}' > output.txt, but the location of the 2 specific field headers is... (9 Replies)
Discussion started by: mdap
9 Replies
Login or Register to Ask a Question