Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Modify CSV file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Modify CSV file
# 8  
Old 11-20-2016
@durden_tyler thank you for the response. You are correct the file is being FTPed to the location however in ASCII. The challenge is that the FTP transfer cannot be changed as this is a 3rd party system, how can this be overcome? Here is the output of the:
Code:
od -bc output.txt

Code:
0000000 063 062 070 061 070 066 073 061 060 060 063 062 061 067 070 066
          3   2   8   1   8   6   ;   1   0   0   3   2   1   7   8   6
0000020 073 102 151 040 141 156 144 145 162 073 063 062 070 061 070 066
          ;   B   i       a   n   d   e   r   ;   3   2   8   1   8   6
0000040 073 120 117 123 124 073 102 151 040 141 156 144 145 162 073 062
          ;   P   O   S   T   ;   B   i       a   n   d   e   r   ;   2
0000060 067 066 063 065 064 066 071 071 073 066 065 065 060 063 061 060
          7   6   3   5   4   6   9   9   ;   6   5   5   0   3   1   0
0000100 060 061 070 060 061 071 067 070 073 101 143 164 151 166 145 073
          0   1   8   0   1   9   7   8   ;   A   c   t   i   v   e   ;
0000120 124 145 162 040 120 141 143 153 073 061 065 057 060 071 057 062
          T   e   r       P   a   c   k   ;   1   5   /   0   9   /   2
0000140 060 061 066 040 060 060 072 060 060 072 060 060 073 123 164 141
          0   1   6       0   0   :   0   0   :   0   0   ;   S   t   a
0000160 156 144 141 162 144 040 120 154 141 156 050 160 157 051 015 012
          n   d   a   r   d       P   l   a   n   (   p   o   )  \r  \n
0000200 063 062 070 061 071 060 073 062 060 060 070 067 061 061 067 062
          3   2   8   1   9   0   ;   2   0   0   8   7   1   1   7   2
0000220 073 112 141 040 157 156 141 073 063 062 070 061 071 060 073 120
          ;   J   a       o   n   a   ;   3   2   8   1   9   0   ;   P
0000240 117 123 124 073 112 141 040 157 156 141 073 062 067 065 064 066
          O   S   T   ;   J   a       o   n   a   ;   2   7   5   4   6
0000260 060 060 060 060 073 066 065 065 060 063 061 060 060 061 067 063
          0   0   0   0   ;   6   5   5   0   3   1   0   0   1   7   3
0000300 067 071 071 060 073 101 143 164 151 166 145 073 123 120 040 120
          7   9   9   0   ;   A   c   t   i   v   e   ;   S   P       P
0000320 154 165 163 040 114 151 073 063 061 057 060 065 057 062 060 061
          l   u   s       L   i   ;   3   1   /   0   5   /   2   0   1
0000340 066 040 060 060 072 060 060 072 060 060 073 123 164 141 156 144
          6       0   0   :   0   0   :   0   0   ;   S   t   a   n   d
0000360 141 162 144 040 120 154 141 156 050 160 157 051 015 012 063 062
          a   r   d       P   l   a   n   (   p   o   )  \r  \n   3   2
0000400 070 061 071 062 073 061 060 060 063 062 070 071 062 063 073 116
          8   1   9   2   ;   1   0   0   3   2   8   9   2   3   ;   N
0000420 040 110 157 154 144 151 156 147 040 050 120 124 131 051 114 124
              H   o   l   d   i   n   g       (   P   T   Y   )   L   T
0000440 104 073 063 062 070 061 071 062 073 120 117 123 124 073 116 040
          D   ;   3   2   8   1   9   2   ;   P   O   S   T   ;   N
0000460 110 157 154 144 151 156 147 040 050 120 124 131 051 114 124 104
          H   o   l   d   i   n   g       (   P   T   Y   )   L   T   D
0000500 073 062 067 065 065 060 067 066 070 060 073 066 065 065 060 063
          ;   2   7   5   5   0   7   6   8   0   ;   6   5   5   0   3
0000520 061 060 060 061 067 061 061 060 060 067 073 101 143 164 151 166
          1   0   0   1   7   1   1   0   0   7   ;   A   c   t   i   v
0000540 145 073 111 156 040 123 111 040 124 145 155 040 107 160 073 062
          e   ;   I   n       S   I       T   e   m       G   p   ;   2
0000560 071 057 060 066 057 062 060 061 062 040 060 060 072 060 060 072
          9   /   0   6   /   2   0   1   2       0   0   :   0   0   :
0000600 060 060 073 123 164 141 156 144 141 162 144 040 120 154 141 156
          0   0   ;   S   t   a   n   d   a   r   d       P   l   a   n
0000620 050 160 157 051 015 012 063 062 070 061 071 063 073 061 060 060
          (   p   o   )  \r  \n   3   2   8   1   9   3   ;   1   0   0
0000640 063 062 070 071 062 063 073 116 040 110 157 154 144 151 156 147
          3   2   8   9   2   3   ;   N       H   o   l   d   i   n   g
0000660 040 050 120 124 131 051 114 124 104 073 063 062 070 061 071 063
              (   P   T   Y   )   L   T   D   ;   3   2   8   1   9   3
0000700 073 120 117 123 124 073 116 040 110 157 154 144 151 156 147 040
          ;   P   O   S   T   ;   N       H   o   l   d   i   n   g
0000720 050 120 124 131 051 114 124 104 073 062 067 065 065 060 067 066
          (   P   T   Y   )   L   T   D   ;   2   7   5   5   0   7   6
0000740 070 061 073 066 065 065 060 063 061 060 060 061 067 061 061 060
          8   1   ;   6   5   5   0   3   1   0   0   1   7   1   1   0
0000760 060 070 073 101 143 164 151 166 145 073 111 156 040 123 111 040
          0   8   ;   A   c   t   i   v   e   ;   I   n       S   I
0001000 124 145 155 040 107 160 073 062 071 057 060 066 057 062 060 061
          T   e   m       G   p   ;   2   9   /   0   6   /   2   0   1
0001020 062 040 060 060 072 060 060 072 060 060 073 123 164 141 156 144
          2       0   0   :   0   0   :   0   0   ;   S   t   a   n   d
0001040 141 162 144 040 120 154 141 156 050 160 157 051 015 012 063 062
          a   r   d       P   l   a   n   (   p   o   )  \r  \n   3   2
0001060 070 061 071 064 073 061 060 060 063 062 070 071 062 063 073 116
          8   1   9   4   ;   1   0   0   3   2   8   9   2   3   ;   N
0001100 040 110 157 154 144 151 156 147 040 050 120 124 131 051 114 124
              H   o   l   d   i   n   g       (   P   T   Y   )   L   T
0001120 104 073 063 062 070 061 071 064 073 120 117 123 124 073 116 040
          D   ;   3   2   8   1   9   4   ;   P   O   S   T   ;   N
0001140 110 157 154 144 151 156 147 040 050 120 124 131 051 114 124 104
          H   o   l   d   i   n   g       (   P   T   Y   )   L   T   D
0001160 073 062 067 065 065 060 067 066 070 062 073 066 065 065 060 063
          ;   2   7   5   5   0   7   6   8   2   ;   6   5   5   0   3
0001200 061 060 060 061 067 061 061 060 060 071 073 101 143 164 151 166
          1   0   0   1   7   1   1   0   0   9   ;   A   c   t   i   v
0001220 145 073 111 156 040 123 111 040 124 145 155 040 107 160 073 062
          e   ;   I   n       S   I       T   e   m       G   p   ;   2
0001240 071 057 060 066 057 062 060 061 062 040 060 060 072 060 060 072
          9   /   0   6   /   2   0   1   2       0   0   :   0   0   :
0001260 060 060 073 123 164 141 156 144 141 162 144 040 120 154 141 156
          0   0   ;   S   t   a   n   d   a   r   d       P   l   a   n
0001300 050 160 157 051 015 012
          (   p   o   )  \r  \n
0001306

# 9  
Old 11-20-2016
This looks like your "subsdata" file.
The "output" file is supposed to have a number and a colon character ( : ) at the beginning.

(1) Does your "subsdata" file have "\r\n" end-of-line characters to begin with?
(2) Are you able to use RudiC's awk script to transform your "subsdata" file and write new content to "output.txt" file?
(3) Does your "output" file have "\r\n" end-of-line characters as well?
(4) The ftp transfers your "output" file from a source OS to a target OS. Is your source OS Unix/Linux and the target OS Windows?
(4) Do you notice the missing last character in your source OS or target OS or both?
# 10  
Old 11-20-2016
Change RudiC's suggestion from:
Code:
awk -F";" -vDQ='"' '{print DQ $7 DQ ": " DQ $0 DQ}' file

to:
Code:
awk -F";" -vDQ='"' '{sub(/\r$/,"");print DQ $7 DQ ": " DQ $0 DQ}' file

to get rid of the <carriage-return> at the end of each line in your input file instead of copying it to your output before the closing <double-quote> character.
Last edited by Don Cragun; 11-20-2016 at 05:59 AM.. Reason: Fix typo: s/};/);/
# 11  
Old 11-20-2016
Thank you for the suggestion, I am currently getting an error:
Code:
awk -F";" -vDQ='"' '{sub(/\r$/,""};print DQ $7 DQ ": " DQ $0 DQ}' output.txt
Error:
awk: cmd. line:1: {sub(/\r$/,""};print DQ $7 DQ ": " DQ $0 DQ}
awk: cmd. line:1:              ^ syntax error

# 12  
Old 11-20-2016
Sorry,
That should have been:
Code:
awk -F";" -vDQ='"' '{sub(/\r$/,"");print DQ $7 DQ ": " DQ $0 DQ}' file

# 13  
Old 11-20-2016
Got it thank you very much Don Cragun it is working now:
Code:
awk -F";" -vDQ='"' '{sub (/\r$/,"");print DQ $7 DQ ": " DQ $0 DQ}' output.txt

---------- Post updated at 12:54 PM ---------- Previous update was at 12:08 PM ----------

Hi, thank you very much. Have now run the awk filter against the entire file and found as there is free text fields some of them contain information that is not working or causing problems with post processing. How can I remove the following before running the awk statement? Here are the characters I would like to remove from the file before formatting the file:
Code:
"
/
\

---------- Post updated at 02:14 PM ---------- Previous update was at 12:54 PM ----------

Got it, thank you very much for the assistance, it is truly appreciated:
Code:
awk -F";" -vDQ='"' '{sub(/\r$/,"");gsub("\"","");gsub("/","");gsub(/\\/,"");print DQ $7 DQ ": " DQ $0 DQ}' output.txt

# 14  
Old 11-20-2016
Or
Code:
awk -F";" -vDQ='"' '{gsub (/["\/\\]|\r$/,""); print DQ $7 DQ ": " DQ $0 DQ }' file
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Modify csv-files with awk

Hello everyone! I have thousands of csv files I have to import into a Database table. As usually the files aren't perfect. For example they have a different number of columns and some weird columns. The second problem is, that I have to add 3 parts of the filename into 3 rows in the... (6 Replies)
Discussion started by: elRonaldo
6 Replies

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

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

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

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

6. Shell Programming and Scripting

Perl script to modify csv file

Hi Friends, I want to convert a csv file into a ordinary .txt file. I am able to convert but I want the output to look as shown below in the .txt file table findhost= { {"xyz","abc"}, {"rxz","mmz"}, {"vrr","nnz"}, } default={"NONE"} My current perl script #!/usr/bin/env perl... (12 Replies)
Discussion started by: dbashyam
12 Replies

7. Shell Programming and Scripting

Calling Pl/sql function in shell script to modify csv

I need to 1.Open a csv 2.Process the csv i.e. Modify 2 column in the csv. To modify the column the value needs to be passed to a pl/sql function and the return value should be updated For eg: If column 2 E,then E will be passed in database function which will return Employee. 3. Write a... (5 Replies)
Discussion started by: Chinky23
5 Replies

8. Shell Programming and Scripting

Read data from .csv file through shell script & modify

I need to read data from a file called "test.csv" through shell script where the file contains values like name,price,descriptor etc. There are rows where descriptor (& in some rows name) are written as string & other characters like "car_+" OR "bike*" etc where it should contains strings like... (3 Replies)
Discussion started by: raj100
3 Replies

9. Shell Programming and Scripting

Need to modify csv-file with bash script

Hi Guys, I need to write a script, that exports the "moz_places" table of the "places.sqlite"-file (firefox browser history) into a csv-file. That part works. After the export, my csv looks like this: ... 4429;http://www.sqlite.org/sqlite.html;"Command Line Shell For... (11 Replies)
Discussion started by: Sebi0815
11 Replies
Login or Register to Ask a Question