Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Create csv from text file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Create csv from text file
# 1  
Old 02-08-2017
Create csv from text file
Gents,

I am trying to create a csv file using the file attached.

I have a problem to get all information required because the rows are not continues.

Here is my code till now.

Code:
awk '    /"ffid"/{if(s){print s;s=$NF}else{s=$NF}}
    /"LineNumber"/{s=s $NF}
    /"PointNumber"/{s=s $NF}
    /"x"/{s=s $NF}
    /"y"/{s=s $NF}
    /"z"/{s=s $NF}
    /"date"/{s=s $NF}
    /"Valid"/{s=s $NF}
    /"AcquisitionLength"/{s=s $NF}
    /"AcquisitionNumber"/{s=s $NF}
    /"FileDate"/{s=s $NF}
    /"FileSize"/{s=s $NF}
    /"GPSTimeOfAcquisition"/{s=s $NF}
    /"JulianDay"/{s=s $NF}
    /"NumberOfAuxes"/{s=s $NF}
    /"NumberOfDeadSeisTraces"/{s=s $NF}
    /"NumberOfLiveSeisTraces"/{s=s $NF}
    /"NumberOfSeisTraces"/{s=s $NF}
    /"RecordLength"/{s=s $NF}
    /"SampleRate"/{s=s $NF}
    /"SourceEasting"/{s=s $NF}
    /"SourceNorthing"/{s=s $NF}
    /"SourceLineNumber"/{s=s $NF}
    /"SourcePointNumber"/{s=s $NF}
    /"SwathName"/{s=s $NF}
    /"TapeNumber"/{s=s $NF}
    /"SweepLength"/{s=s $NF}
    /"TestRecordType"/{s=s $NF}
    /"TotalNumberOfTraces"/{s=s $NF}
    /"TypeOfSource"/{s=s $NF}
    /"PointIndex"/{s=s $NF}
    END{print s}' input | sed -e 's/"//g' > tmp1

Kindly can you help me to get the output desire (.csv attached)

Thanks a lot
files.rar (3.5 KB) 
# 2  
Old 02-08-2017
Is it something along these lines? Didn't validate all the values...
awk -f jiam.awk input.txt > output.txt
where jiam.awk is:
Code:
BEGIN {
      qq="\""
      FS=qq
      OFS=","

      fldL="ffid,LineNumber,PointNumber,x,y,z,date,Valid,AcquisitionLength,AcquisitionNumber,FileDate,FileSize,GPSTimeOfAcquisition,JulianDay,NumberOfAuxes,NumberOfDeadSeisTraces,NumberOfLiveSeisTraces,NumberOfSeisTraces,RecordLength,SampleRate,SourceEasting,SourceNorthing,SourceLineNumber,SourcePointNumber,SwathName,TapeNumber,SweepLength,TestRecordType,TotalNumberOfTraces,TypeOfSource,PointIndex"

      fldN=split(fldL, fldT, OFS)
      for(i=1; i in fldT; i++)
        fldA[fldT[i]]=i

      print fldL
}
$2 in fldA { printf("%s%s", $(NF-1), (fldA[$2]==fldN)?ORS:OFS)}

---------- Post updated at 04:55 PM ---------- Previous update was at 03:14 PM ----------

here's a little bit more robust version:
Code:
BEGIN {
      qq="\""

      FS=qq
      OFS=","

      fldL="ffid,LineNumber,PointNumber,x,y,z,date,Valid,AcquisitionLength,AcquisitionNumber,FileDate,FileSize,GPSTimeOfAcquisition,JulianDay,NumberOfAuxes,NumberOfDeadSeisTraces,NumberOfLiveSeisTraces,NumberOfSeisTraces,RecordLength,SampleRate,SourceEasting,SourceNorthing,SourceLineNumber,SourcePointNumber,SwathName,TapeNumber,SweepLength,TestRecordType,TotalNumberOfTraces,TypeOfSource,PointIndex,preEasting,preNorthing,postEasting,postNorthing,offset,cogStatus,fleet_nb,VIB1_drive,VIB2_drive,VIB1_vib_status,VIB2_vib_status,VIB1_id,VIB2_id,VIB1_gps_quality,VIB2_gps_quality,VIB1_over_mass,VIB2_over_mass,VIB1_dist_avg,VIB2_dist_avg,VIB1_dist_peak,VIB2_dist_peak"

      fldN=split(fldL, fldT, OFS)
      for(i=1; i in fldT; i++)
        fldA[fldT[i]]=i

      print fldL
}

function arrayFull(a,   i)
{
  for(i=1; i<= fldN; i++)
     if (!(i in a))
        return 0
 return 1
}

arrayFull(outputA) {
  for(i=1; i in outputA; i++)
      printf("%s%s", outputA[i], (i==fldN)?ORS:OFS)
  split("", outputA)
}
$2 in fldA { outputA[fldA[$2]]=$(NF-1) }

Last edited by vgersh99; 02-08-2017 at 05:03 PM..
This User Gave Thanks to vgersh99 For This Post:
jiam912
# 3  
Old 02-09-2017
Dear vgersh99

The code works perfect..

Kindly can u adapt it to grep also the info for this fields:

Code:
RMS_noise,RMS_refracted,RMS_GR,RMS_AW,RMS_signal1,RMS_signal2,FreqDom_noise,FreqDom_refracted,FreqDom_GR,FreqDom_AW,FreqDom_signal1,FreqDom_signal2,Bandw_noise,Bandw_refracted,Bandw_GR,Bandw_AW,Bandw_signal1,Bandw_signal2,SN_noise,SN_refracted,SN_GR,SN_AW,SN_signal1,SN_signal2

I notice the code greps only the values within "", then if there is fields without " the code doesn't work..

example:
Code:
"RMS_noise": -74.176453,

..

It is because the FS
Code:
 qq="\""

Please it is possible to change the code to grep values with "" and not ""

Thanks and regards.
# 4  
Old 02-09-2017
Code:
BEGIN {
      qq="\""
      tab=sprintf("\t")

      FS=":"
      OFS=","

      fldL="ffid,LineNumber,PointNumber,x,y,z,date,Valid,AcquisitionLength,AcquisitionNumber,FileDate,FileSize,GPSTimeOfAcquisition,JulianDay,NumberOfAuxes,NumberOfDeadSeisTraces,NumberOfLiveSeisTraces,NumberOfSeisTraces,RecordLength,SampleRate,SourceEasting,SourceNorthing,SourceLineNumber,SourcePointNumber,SwathName,TapeNumber,SweepLength,TestRecordType,TotalNumberOfTraces,TypeOfSource,PointIndex,preEasting,preNorthing,postEasting,postNorthing,offset,cogStatus,fleet_nb,VIB1_drive,VIB2_drive,VIB1_vib_status,VIB2_vib_status,VIB1_id,VIB2_id,VIB1_gps_quality,VIB2_gps_quality,VIB1_over_mass,VIB2_over_mass,VIB1_dist_avg,VIB2_dist_avg,VIB1_dist_peak,VIB2_dist_peak"
      #fldL="ffid,LineNumber,PointNumber,x,y,z,date,Valid,AcquisitionLength,AcquisitionNumber,FileDate,FileSize,GPSTimeOfAcquisition,JulianDay,NumberOfAuxes,NumberOfDeadSeisTraces,NumberOfLiveSeisTraces,NumberOfSeisTraces,RecordLength,SampleRate,SourceEasting,SourceNorthing,SourceLineNumber,SourcePointNumber,SwathName,TapeNumber,SweepLength,TestRecordType,TotalNumberOfTraces,TypeOfSource,PointIndex,preEasting,preNorthing,postEasting,postNorthing,offset,cogStatus,fleet_nb,VIB1_drive,VIB2_drive,VIB1_vib_status,VIB2_vib_status,VIB1_id,VIB2_id,VIB1_gps_quality,VIB2_gps_quality,VIB1_over_mass,VIB2_over_mass,VIB1_dist_avg,VIB2_dist_avg,VIB1_dist_peak,VIB2_dist_peak,RMS_noise,RMS_refracted,RMS_GR,RMS_AW,RMS_signal1,RMS_signal2,FreqDom_noise,FreqDom_refracted,FreqDom_GR,FreqDom_AW,FreqDom_signal1,FreqDom_signal2,Bandw_noise,Bandw_refracted,Bandw_GR,Bandw_AW,Bandw_signal1,Bandw_signal2,SN_noise,SN_refracted,SN_GR,SN_AW,SN_signal1,SN_signal2"

      fldN=split(fldL, fldT, OFS)
      for(i=1; i in fldT; i++)
        fldA[fldT[i]]=i

      print fldL
}

function normStr(str)
{
   gsub("[" OFS qq tab " ]", "", str)
   return str
}

function arrayFull(a,   i)
{
  for(i=1; i<= fldN; i++)
     if (!(i in a))
        return 0
 return 1
}

arrayFull(outputA) {
  for(i=1; i in outputA; i++)
      printf("%s%s", outputA[i], (i==fldN)?ORS:OFS)
  split("", outputA)
}
normStr($1) in fldA { outputA[fldA[normStr($1)]]=normStr($NF) }

The newly added FreqDom_*, Bandw_* - don't follow the previous pattern and are broken at the moment.
Uncomment the last definition of fdlL to see it broken Smilie

test if the current implementation takes care of fields with " and withOUT.

---------- Post updated at 10:50 AM ---------- Previous update was at 10:37 AM ----------

here's the version taking care of freqDom_* and Bandw_* fields:
Code:
BEGIN {
      qq="\""
      tab=sprintf("\t")

      FS=":"
      OFS=","

      #fldL="ffid,LineNumber,PointNumber,x,y,z,date,Valid,AcquisitionLength,AcquisitionNumber,FileDate,FileSize,GPSTimeOfAcquisition,JulianDay,NumberOfAuxes,NumberOfDeadSeisTraces,NumberOfLiveSeisTraces,NumberOfSeisTraces,RecordLength,SampleRate,SourceEasting,SourceNorthing,SourceLineNumber,SourcePointNumber,SwathName,TapeNumber,SweepLength,TestRecordType,TotalNumberOfTraces,TypeOfSource,PointIndex,preEasting,preNorthing,postEasting,postNorthing,offset,cogStatus,fleet_nb,VIB1_drive,VIB2_drive,VIB1_vib_status,VIB2_vib_status,VIB1_id,VIB2_id,VIB1_gps_quality,VIB2_gps_quality,VIB1_over_mass,VIB2_over_mass,VIB1_dist_avg,VIB2_dist_avg,VIB1_dist_peak,VIB2_dist_peak"
      fldL="ffid,LineNumber,PointNumber,x,y,z,date,Valid,AcquisitionLength,AcquisitionNumber,FileDate,FileSize,GPSTimeOfAcquisition,JulianDay,NumberOfAuxes,NumberOfDeadSeisTraces,NumberOfLiveSeisTraces,NumberOfSeisTraces,RecordLength,SampleRate,SourceEasting,SourceNorthing,SourceLineNumber,SourcePointNumber,SwathName,TapeNumber,SweepLength,TestRecordType,TotalNumberOfTraces,TypeOfSource,PointIndex,preEasting,preNorthing,postEasting,postNorthing,offset,cogStatus,fleet_nb,VIB1_drive,VIB2_drive,VIB1_vib_status,VIB2_vib_status,VIB1_id,VIB2_id,VIB1_gps_quality,VIB2_gps_quality,VIB1_over_mass,VIB2_over_mass,VIB1_dist_avg,VIB2_dist_avg,VIB1_dist_peak,VIB2_dist_peak,RMS_noise,RMS_refracted,RMS_GR,RMS_AW,RMS_signal1,RMS_signal2,FreqDom_noise,FreqDom_refracted,FreqDom_GR,FreqDom_AW,FreqDom_signal1,FreqDom_signal2,Bandw_noise,Bandw_refracted,Bandw_GR,Bandw_AW,Bandw_signal1,Bandw_signal2,SN_noise,SN_refracted,SN_GR,SN_AW,SN_signal1,SN_signal2"

      fldN=split(fldL, fldT, OFS)
      for(i=1; i in fldT; i++)
        fldA[fldT[i]]=i

      print fldL
}

function normStr(str)
{
   gsub("[" OFS qq tab " ]", "", str)
   return str
}

function arrayFull(a,   i)
{
  for(i=1; i<= fldN; i++)
     if (!(i in a))
        return 0
 return 1
}

arrayFull(outputA) {
  for(i=1; i in outputA; i++)
      printf("%s%s", outputA[i], (i==fldN)?ORS:OFS)
  split("", outputA)
}
normStr($1) in fldA {
   fld1=normStr($1)
   fld2=normStr($NF)
   if (normStr($2) == "[") {
       getline fld2
       fld2=normStr(fld2)
   }
   outputA[fldA[fld1]]=fld2
}

This User Gave Thanks to vgersh99 For This Post:
jiam912
# 5  
Old 02-09-2017
Dear vgersh99

Appreciate your help.

Thanks a lot for you time and help..

I will try the code..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Process text file to create CSV

I am working on a text file where I have to get data from a text file and convert it into either CSV format or Column format as shown below. OUTPUT Expected GRP Name Pair Size DName DNumber PName PNumber adm_grp Pair1 150.00KG Pair_0ABC_1 0396 Pair_0267_s 1292 ... (6 Replies)
Discussion started by: shunya
6 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Need help. How to create csv file?

Hi I'm a beginner and I have some problem. I have multiple files in the same directory which has one column but rows following the format. File: directory/Disk.txt Content: a b c d e File: directory/Memory.txt a b c d e File: directory/CPU.txt (3 Replies)
Discussion started by: thenuie
3 Replies

4. Shell Programming and Scripting

Script to create a CSV file

I created a script that will go out and so a "/sbin/chkconfig --list | egrep XXX" against a server list that would create an output file like the following example: ---------------------------------------------------------------------------------- SERVER1 RC_Script_1 0:off 1:off 2:off... (4 Replies)
Discussion started by: asnatlas
4 Replies

5. UNIX for Dummies Questions & Answers

How to create a .csv file from 2 different .txt files?

Hi, I need to create a .csv file from information that i have in two different tab delimited .txt file. I just want to select some of the columns of each .txt file and paste them into a .cvs file. My files look like: File 1 transcript_id Seq. Description Seq. Length ... (2 Replies)
Discussion started by: alisrpp
2 Replies

6. Shell Programming and Scripting

Create an .csv/excel file using shellscript

In my file, i have 4 Product names(For ex:Microsoft excel, Oracle,.Net,IBM websphere,..etc.,) I want this 4 Products in 4 individual .csv/excel file after running the script with those products related information. (12 Replies)
Discussion started by: Navya
12 Replies

7. Shell Programming and Scripting

How can I create a CSV file from PL/Sql in UNIX?

Can someone help me on creating a script that will manage/create a csv file from Pl/Sql using UNIX?Any advice is welcome,thank you so much,:) (2 Replies)
Discussion started by: Atrap
2 Replies

8. Shell Programming and Scripting

how to create csv file using shell script

I have a file in multiple directory which has some records in the following format File: a/latest.txt , b/latest.txt, c/latest.txt -> Name=Jhon Age=27 Gender=M Street=LA Road Occupation=Service I want to generate a csv file from the above file as follows File: output.csv -> ... (9 Replies)
Discussion started by: rjk2504
9 Replies

9. Shell Programming and Scripting

Command to create and update csv file

Hi, I need to create a csv file to store oracle query output. This report need to be created on hourly basis. The csv file report format as "Report_22_Sep_09_13IST.csv". I have the oracle query. Now i need to create and move the oracle query output to the report row by row starting from 3rd row.... (6 Replies)
Discussion started by: Sekar1
6 Replies

10. Shell Programming and Scripting

Merging files to create CSV file

Hi, I have different files of the same type, as: Time: 100 snr: 88 perf: 10 other: 222 Each of these files are created periodically. What I need to do is to merge all of them into one but having the following form: (2 Replies)
Discussion started by: Ravendark
2 Replies
Login or Register to Ask a Question