Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Read multiple text files and copy data to csv

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Read multiple text files and copy data to csv
# 15  
Old 03-31-2016
@ RudiC: sorry for the code tag. will pay more attention in the future
@Scrutinizer: you didnt waste your time. I ve learnt a lot from the thread. i wanted to start simple and built around it. Sorry if you see it as waste of time

---------- Post updated at 10:43 AM ---------- Previous update was at 10:11 AM ----------

i had a look on the other thread, replicated the test on my pc. tried to adjust it to

Code:
awk -F, -v HD="IP address,PC name,destination,UTC begin,UTC end,destination IP address,duration (ms),local begin,local end,source IP address,actual extension,original called party,original calling party" '
BEGIN           {for (N=i=split (HD, T); i>0; i--) SRCH[T[i]]
                 for (i=1; i<=N; i++) printf "%s\t", T[i]
                 print ""
                }
FNR==1 && L     {for (i=1; i<=N; i++) printf "%s\t", PR[T[i]]
                 print ""
                 delete PR
                }

$1 in SRCH      {PR[$1]=$NF
                 L=1
                }
END             {for (i=1; i<=N; i++) printf "%s\t", PR[T[i]]
                 print ""
                }
'  *

but got this

Code:
IP address      PC name destination     UTC begin       UTC end  destination IP address  duration (ms)   local begin     local end        source IP address       actual extension        original called party    original calling party

thanks
# 16  
Old 03-31-2016
Try setting the field separator by adding FS="=" or "-F=" (remove -F,); this will get you one step further.
# 17  
Old 03-31-2016
And this is the one step further:
Code:
awk -F= -vOFS="," '
BEGIN           {HD = "IP address=PC name=destination=UTC begin=UTC end=destination IP address=duration (ms)=local begin=local end=" \
                        "source IP address=actual extension=original called party=original calling party"
                 for (N=i=split (HD, T); i>0; i--) SRCH[T[i]]
                 for (i=1; i<N; i++) printf "%s%s", T[i], OFS
                 print T[N]
                }
FNR==1 && L     {for (i=1; i<N; i++) printf "%s%s", PR[T[i]], OFS
                 print PR[T[N]]
                 delete PR
                }

$1 in SRCH      {PR[$1]=$NF
                 L=1
                }
END             {for (i=1; i<N; i++) printf "%s%s", PR[T[i]], OFS
                 print PR[T[N]]
                }
' file[12]
IP address,PC name,destination,UTC begin,UTC end,destination IP address,duration (ms),local begin,local end,source IP address,actual extension,original called party,original calling party
192.168.10.25,RecSrv,,20150729T075150.991582,20150729T075234.659672,192.168.11.1,43668,20150729T085150.991582,20150729T085234.659672,192.168.11.254,,,
,,D:/recording/20150729/filename2.audio,20150729T075150.991582,20150729T075234.659672,192.168.11.1,43668,20150729T085150.991582,20150729T085234.659672,192.168.11.254,12345,08865412596,12345

This User Gave Thanks to RudiC For This Post:
lp.descamps
# 18  
Old 04-01-2016
ok i did this
Code:
awk "-F=", -v HD="IP address,PC name,destination,UTC begin,UTC end,destination IP address,duration (ms),local begin,local end,source IP address,actual extension,original called party,original calling party" '
BEGIN           {for (N=i=split (HD, T); i>0; i--) SRCH[T[i]]
                 for (i=1; i<=N; i++) printf "%s\t", T[i]
                 print ""
                }
FNR==1 && L     {for (i=1; i<=N; i++) printf "%s\t", PR[T[i]]
                 print ""
                 delete PR
                }

$1 in SRCH      {PR[$1]=$NF
                 L=1
                }
END             {for (i=1; i<=N; i++) printf "%s\t", PR[T[i]]
                 print ""
                }
'  *

and i get this now

Code:
IP address,PC name,destination,UTC begin,UTC end,destination IP address,duration (ms),local begin,local end,source IP address,actual extension,original called party,original calling party

as you said... one step closer :-)

but what i dont understand is why i dont value below
thanks

---------- Post updated 01-04-16 at 05:16 AM ---------- Previous update was 31-03-16 at 11:16 AM ----------

nearly there! :-)
how can I add a column for FILENAME?
thanks

---------- Post updated at 05:22 AM ---------- Previous update was at 05:16 AM ----------

i tried this
Code:
awk -F= -vOFS="," '
BEGIN           {HD = "FILENAME=IP address=PC name=destination=UTC begin=UTC end=destination IP address=duration (ms)=local begin=local end=" \
                        "source IP address=actual extension=original called party=original calling party"
                 for (N=i=split (HD, T); i>0; i--) SRCH[T[i]]
                 for (i=1; i<N; i++) printf "%s%s", T[i], OFS
                 print T[N]
                }
FNR==1 && L     {for (i=1; i<N; i++) printf "%s%s", PR[T[i]], OFS
                 print PR[T[N]]
                 delete PR
                }

$1 in SRCH      {PR[$1]=$NF
                 L=1
                }
END             {for (i=1; i<N; i++) printf FILENAME "%s%s", PR[T[i]], OFS
                 print PR[T[N]]
                }
' *.ipfx_audio > test.csv

but i guess i need to liunk the filename column with FILENAME
# 19  
Old 04-01-2016
Try
Code:
awk -F= -vOFS="," '
NR == 1         {HD = "IP address=PC name=destination=UTC begin=UTC end=destination IP address=duration (ms)=local begin=local end=" \
                        "source IP address=actual extension=original called party=original calling party"
                 for (N=i=split (HD, T); i>0; i--) SRCH[T[i]]
                 for (i=1; i<N; i++) printf "%s%s", T[i], OFS
                 print T[N]
                 FN = FILENAME
                }
FNR==1 && L     {printf "%s%s", FN, OFS
                 for (i=1; i<N; i++) printf "%s%s", PR[T[i]], OFS
                 print PR[T[N]]
                 FN = FILENAME
                 delete PR
                }

$1 in SRCH      {PR[$1]=$NF
                 L=1
                }
END             {printf "%s%s", FN, OFS
                 for (i=1; i<N; i++) printf "%s%s", PR[T[i]], OFS
                 print PR[T[N]]
                }
'

This User Gave Thanks to RudiC For This Post:
lp.descamps
# 20  
Old 04-01-2016
i tried to a filename header like this

Code:
awk -F= -vOFS="," '
> NR == 1         {HD = "FILENAME=IP address=PC name=destination=UTC begin=UTC end=destination IP address=duration (ms)=local begin=local end=" \
>                         "source IP address=actual extension=original called party=original calling party"
>                  for (N=i=split (HD, T); i>0; i--) SRCH[T[i]]
>                  for (i=1; i<N; i++) printf "%s%s", T[i], OFS
>                  print T[N]
>                  FN = FILENAME
>                 }
> FNR==1 && L     {printf "%s%s", FN, OFS
>                  for (i=1; i<N; i++) printf "%s%s", PR[T[i]], OFS
>                  print PR[T[N]]
>                  FN = FILENAME
>                  delete PR
>                 }
>
> $1 in SRCH      {PR[$1]=$NF
>                  L=1
>                 }
> END             {printf "%s%s", FN, OFS
>                  for (i=1; i<N; i++) printf FILENAME "%s%s", PR[T[i]], OFS
>                  print PR[T[N]]
>                 }
> ' *

and get this
Code:
FILENAME,IP address,PC name,destination,UTC begin,UTC end,destination IP address,duration (ms),local begin,local end,source IP address,actual extension,original called party,original calling party
20090130T165309.474091.ipfx_audio,,10.11.40.61,D01309,,20090130T165309.474090,20090130T170003.303469,10.11.41.151,413829,20090130T165309.474091,20090130T170003.303469,10.11.0.241,,,
20090130T165558.886637.ipfx_audio,,10.11.40.67,D01332,,20090130T165558.886637,20090130T165839.247671,10.11.41.136,160361,20090130T165558.886637,20090130T165839.247671,10.11.0.240,,,
20090130T170010.074146.ipfx_audio,,10.11.40.61,D01309,,20090130T170010.074146,20090130T170019.325071,10.11.41.151,9250,20090130T170010.074146,20090130T170019.325071,10.11.41.97,,,

but i shouldn't get a ,, between 20090130T165309.474091.ipfx_audio and 10.11.40.61

where do i need to remove it?
thanks
# 21  
Old 04-01-2016
Applied to your samples posted earlier, there's no unnecessary double comma. Please post your input files that result in above.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Generate files and use csv data to replace multiple variables in a template

I have a source csv file consists of first field as variable name, and the rest are site-specific information (converted from excel file, where site -specific values in columns). I am trying to create a file for every site using a template and replace the multiple variables with values from the... (3 Replies)
Discussion started by: apalex
3 Replies

2. UNIX for Beginners Questions & Answers

Copy data at specified location from multiple files

Hello everyone, Im super new to coding but increasingly in need of it at work. Im have task stacked because of this problems, that I cannot figure out how to solve looking on the internet after trying many many things that looked similar to me. I have multiple data files of the form (see below).... (2 Replies)
Discussion started by: Xfiles_fan
2 Replies

3. Shell Programming and Scripting

In PErl script: need to read the data one file and generate multiple files based on the data

We have the data looks like below in a log file. I want to generat files based on the string between two hash(#) symbol like below Source: #ext1#test1.tale2 drop #ext1#test11.tale21 drop #ext1#test123.tale21 drop #ext2#test1.tale21 drop #ext2#test12.tale21 drop #ext3#test11.tale21 drop... (5 Replies)
Discussion started by: Sanjeev G
5 Replies

4. Shell Programming and Scripting

Read csv file, convert the data and make one text file in UNIX shell scripting

I have input data looks like this which is a part of a csv file 7,1265,76548,"0102:04" 8,1266,76545,"0112:04" I need to make the output data should look like this and the output data will be part of text file: 7|1265000 |7654899 |A| 8|12660000 |76545999 |B| The logic behind the... (6 Replies)
Discussion started by: RJG
6 Replies

5. UNIX for Beginners Questions & Answers

awk GSUB read field values from multiple text files

My program run without error. The problem I am having. The program isn't outputting field values with the column headers to file.txt. Each of the column headers in file.txt has no data. MEMSIZE SECOND SASFoundation Filename The output results in file.txt should show: ... (1 Reply)
Discussion started by: dellanicholson
1 Replies

6. Shell Programming and Scripting

Extracting data from specific rows and columns from multiple csv files

I have a series of csv files in the following format eg file1 Experiment Name,XYZ_07/28/15, Specimen Name,Specimen_001, Tube Name, Control, Record Date,7/28/2015 14:50, $OP,XYZYZ, GUID,abc, Population,#Events,%Parent All Events,10500, P1,10071,95.9 Early Apoptosis,1113,11.1 Late... (6 Replies)
Discussion started by: pawannoel
6 Replies

7. UNIX for Dummies Questions & Answers

Need help combining txt files w/ multiple lines into csv single cell - also need data merge

:confused:Hello -- i just joined the forums. I am a complete noob -- only about 1 week into learning how to program anything... and starting with linux. I am working in Linux terminal. I have a folder with a bunch of txt files. Each file has several lines of html code. I want to combine... (2 Replies)
Discussion started by: jetsetter
2 Replies

8. Shell Programming and Scripting

Read multiple files, parse data and append to a file

Hi..Can anyone suggest a simple way of achieving this. I have several files which ends with extension .vcf . I will give example with two files In the below files, we are interested in File 1: 38 107 C 3 T 6 C/T 38 241 C 4 T 5 C/T 38 247 T 4 C 5 T/C 38 259 T 3 C 6 T/C... (8 Replies)
Discussion started by: empyrean
8 Replies

9. Shell Programming and Scripting

Combine Multiple text or csv files column-wise

Hi All I am trying to combine columns from multiple text files into a single file using paste command but the record length being unequal in the different files the data is running over to the closest empty cell on the left. Please see below. What can i do to resolve this ? File 1 File... (15 Replies)
Discussion started by: venky_ibm
15 Replies

10. Shell Programming and Scripting

Read the data from multiple files and sum the value

Hi all, I have a requirement where i have to read multiple files using Shell Script in Korn Shell. each file will have the 3rd line as the amount field, i have to read this amount field and sum it for all the files. any idea on how to achieve this?? (i think i can achieve it using a loop,... (9 Replies)
Discussion started by: nvuradi
9 Replies
Login or Register to Ask a Question