Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

writing to a csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting writing to a csv file
# 1  
Old 03-27-2008
writing to a csv file
Hi buddies i have doubt. I 'm new to unix script and learning. I have a req.
I need to find the values mentioned in the double quoutes of each line and write it into a csv file. How do I handle this? Please help me.
SCTY_ISS_ID (SCTY_ISS_IDSmilieec18Fixed.0Smilie: "37927320080309"
TRD_DT (TRD_DTSmilieateSmilie: "03/06/2008 00:00:00"
RFR_NUM (RFR_NUM:Char.20Smilie: "233"
SEQ_NUM (SEQ_NUMSmilieec18Fixed.0Smilie: "2"
POS_TYP_ID (POS_TYP_ID:IntSmilie: "17"
SYMB_ID (SYMB_ID:Char.10Smilie: "RRTNX "

Thanks in advance
pstanand
# 2  
Old 03-27-2008
Use sed to replace away all the stuff you don't want to keep. Is your CSV file supposed to have each entry on a separate line, or do you need to collect one record per line? Then how do you know when a new record starts? (If the first field is the field name then you can use that, and simply do a slightly different substitution for that field. Maybe a unique character which you can then convert into a newline with tr.)
# 3  
Old 03-27-2008
writing to a csv file
Hi buddy,
actually the first line contains the value in the double quotes is one column ,second line goes to second column and similarly it goes on. I don't know how to use the sed. can you please give sample code for the data I gave.

Thanks
pstanand
# 4  
Old 03-27-2008
Code:
sed 's/.*"/"/;s/$/,/' file | tr -d '\012'

That will create one big whopping line because you did not tell us how we know when to start a new line. Also there will be a final comma which you can substitute away with another sed invocation. Any decent Unix intro should end up with a discussion of regular expressions and sed; see if you can find some good links elsewhere on this site.

You are posting in the wrong forum anyway, but I guess the forum moderator will move this thread to a better place. (oops, sorry, didn't notice until now.)
# 5  
Old 03-27-2008
writing to a csv file
Hi Buddy,

sorry for not giving a clear picture on the query. Please see my file attached. Here i have to find the Row# for each row and then i have to find the values in the double quoutes on each line. Each line of record will be saved into the csv file as a column. This is my query. Please tell me how to solve this.

Thanks in advance
pstanand
Tmp1.tmp.txt (1.8 KB) 
# 6  
Old 03-27-2008
Quote:
Code:
WRITER_1_*_1> Wed Mar 12 14:50:42 2008
WRITER_1_*_1> WRT_8114
Row # [1] in bad file

WRITER_1_*_1> CMN_1053 : Rowdata: ( RowType=0(insert) Src Rowid=6765 Targ Rowid=6765
  ACCT_ID (ACCT_ID:Dec18Fixed.0:): "82601030700287638"
  SCTY_ISS_ID (SCTY_ISS_ID:Dec18Fixed.0:): "37927320080309"
  TRD_DT (TRD_DT:Date:): "03/06/2008 00:00:00"
  RFR_NUM (RFR_NUM:Char.20:): "233"
  SEQ_NUM (SEQ_NUM:Dec18Fixed.0:): "1"
  POS_TYP_ID (POS_TYP_ID:Int:): "17"
  SYMB_ID (SYMB_ID:Char.10:): "RRTNX     "
  BUY_SL_ID (BUY_SL_ID:Int:): "1"
  CANC_IND (CANC_IND:Char.1:): "N"
  MKT_NUM (MKT_NUM:Int:): "1"
  SETL_DT (SETL_DT:Date:): "03/06/2008 00:00:00"
  BLTTER_NUM (BLTTER_NUM:Int:): "0"
  QTY (QTY:Dec18Fixed.5:): "-251.25650"
  PRC_AMT (PRC_AMT:Dec18Fixed.7:): "11.9400000"
  INVST_AMT (INVST_AMT:Dec18Fixed.4:): "-3000.0000"
  CRT_TS (CRT_TS:Date:): "03/12/2008 14:47:36"
  CRT_ID (CRT_ID:Char.25:): "m_CS_Bkeep_Trans_or_Trade"
)

WRITER_1_*_1> Wed Mar 12 14:50:42 2008
WRITER_1_*_1> WRT_8114 
Row # [2] in bad file

WRITER_1_*_1> CMN_1053 : Rowdata: ( RowType=0(insert) Src Rowid=6766 Targ Rowid=6766
  ACCT_ID (ACCT_ID:Dec18Fixed.0:): "82601030700287638"
  SCTY_ISS_ID (SCTY_ISS_ID:Dec18Fixed.0:): "37927320080309"
  TRD_DT (TRD_DT:Date:): "03/06/2008 00:00:00"
  RFR_NUM (RFR_NUM:Char.20:): "233"
  SEQ_NUM (SEQ_NUM:Dec18Fixed.0:): "2"
  POS_TYP_ID (POS_TYP_ID:Int:): "17"
  SYMB_ID (SYMB_ID:Char.10:): "RRTNX     "
  BUY_SL_ID (BUY_SL_ID:Int:): "1"
  CANC_IND (CANC_IND:Char.1:): "N"
  MKT_NUM (MKT_NUM:Int:): "1"
  SETL_DT (SETL_DT:Date:): "03/06/2008 00:00:00"
  BLTTER_NUM (BLTTER_NUM:Int:): "0"
  QTY (QTY:Dec18Fixed.5:): "0.00000"
  PRC_AMT (PRC_AMT:Dec18Fixed.7:): "11.9400000"
  INVST_AMT (INVST_AMT:Dec18Fixed.4:): "514.2700"
  CRT_TS (CRT_TS:Date:): "03/12/2008 14:47:36"
  CRT_ID (CRT_ID:Char.25:): "m_CS_Bkeep_Trans_or_Trade"
)

So the closing parenthesis can be used to signal end of record? Right.

Code:
sed -n '
# Replace everything up to the first quote with nothing, then print
s/^[^"]"/"/p
# Print a lone closing parenthesis, too
m/^)$/p
' |
# replace all newlines with commas, then replace closing parenthesis with newline
tr '\012)' ',\012' |
# finish it up by replacing comma at end of line with nothing
sed 's/,$//'

This is not particularly elegant but if you are learning shell scripting, anything involving awk or perl is probably too much to tackle at this point. Peel off stuff from the end of the pipeline to see the intermediate processing stages.

What's that about line numbers; you didn't mention anything about the row number in the original question? Where do you want the row numbers?
Last edited by era; 03-27-2008 at 07:47 AM.. Reason: Oops, last char is parenthesis, not brace; minor formatting fixes
# 7  
Old 03-27-2008
With awk you could do something like:

Code:
awk -F "\"" '{
i++
if(i<6){next}
if(i<22){line=line $2 ",";next}
if(i==22){print line $2;line ="";getline;getline;i=0}
}' file

Regards
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Save output of updated csv file as csv file itself, part 2

Hi, I have another problem. I want to sort another csv file by the first field. result.csv SourceFile,Airspeed,GPSLatitude,GPSLongitude,Temperature,Pressure,Altitude,Roll,Pitch,Yaw /home/intannf/foto5/2015_0313_090651_219.JPG,0.,-7.77223,110.37310,30.75,996.46,148.75,180.94,182.00,63.92 ... (2 Replies)
Discussion started by: refrain
2 Replies

2. Shell Programming and Scripting

Save output of updated csv file as csv file itself

Hi, all I want to sort a csv file based on timestamp from oldest to newest and save the output as csv file itself. Here is an example of my csv file. test.csv SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0739.JPG,2015:02:17 11:32:21 /home/intannf/foto/IMG_0749.JPG,2015:02:17 11:37:28... (10 Replies)
Discussion started by: refrain
10 Replies

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

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

5. UNIX for Dummies Questions & Answers

Writing multiple outputs in to separate cells of CSV file

Hi I am writing a script which has multiple awk statements and each statement gives me a numeric count as an output. I want those output to be stored in different cells of a csv file. say 12 awk statements give 12 output and i want them in diffrenet cells of csv file. Thank you guys..!! (4 Replies)
Discussion started by: prabhat.diwaker
4 Replies

6. Shell Programming and Scripting

Reading from a CSV and writing in same CSV file

Hi, I am tryng to read from a csv file and based on some grep command output I will modify one of the column in the same csv. Example:- Input CSV:- 20120829001415,noneAA,google.com 20120829001415,dfsafds,google.com 20120829001415,noneAA,google.com Intermediate Step:- If 2nd column of... (3 Replies)
Discussion started by: kmajumder
3 Replies

7. Shell Programming and Scripting

Need help in writing a routine for sorting a CSV file

Hi, I have a CSV file in following manner: server1,env1,patch1 server1,env1,patch2 server1,env1,patch3 server1,env2,patch1 server1,env2,patch3 server2,env3,patch1 server2,env3,patch5 server2,env4,patch1 server3,env6,patch1 server3,env7,patch2 server3,env7,patch3 I want to... (6 Replies)
Discussion started by: avikaljain
6 Replies

8. UNIX for Dummies Questions & Answers

Writing awk script to read csv files and split them

Hi Here is my script that calls my awk script #!/bin/bash set -x dir="/var/local/dsx/csv" testfile="$testfile" while getopts " f: " option do case $option in f ) testfile="$OPTARG";; esac; done ./scriptFile --testfile=$testfile >> $dir/$testfile.csv It calls my awk... (1 Reply)
Discussion started by: ladyAnne
1 Replies

9. Shell Programming and Scripting

Need to compare two csv files values and write into another csv file

Hi all, Am new to scripting. So i just need your ideas to help me out. Here goes my requirement. I have two csv files 1.csv 2.csv abc,1.24 abc,1 def,2.13 def,1 I need to compare the first column of 1.csv with 2.csv and if matches then need to compare... (2 Replies)
Discussion started by: chinnahyd
2 Replies
Login or Register to Ask a Question