-4

I have a daily CSV export of files of about 200-50 Gb per/day. Each CSV file's first field is a date information like that. Each CSV file has 22 fields

/data/exported$ head  0000_processmessages_export_20170509_000144.csv | awk -F ";" '{print $1}'
2017-05-08T21:59:10.263Z
2017-05-08T21:59:10.000Z
2017-05-08T21:59:10.000Z
2017-05-08T18:59:11.000Z
2017-05-08T18:59:11.000Z
2017-05-08T21:00:00.000Z
2017-05-08T21:00:00.000Z
2017-05-08T21:00:00.000Z
2017-05-08T21:00:00.000Z
2017-05-08T21:00:00.000Z

So basically I want to add 2 more fields (Field22 and Field23) to the end of each row in a csv file and these fields like YYYYDDMMHH and YYYYMMDD format and I must extract them from the first field in row. So

Field 23    Field 24 
2017050821  20170508
2017050821  20170508
2017050821  20170508
2017050818  20170508
2017050818  20170508
2017050821  20170508
2017050821  20170508
2017050821  20170508
2017050821  20170508
2017050821  20170508

How can I do that with sed and/or bash commands?

  • Why don't you simplify your task? Is the question about dates conversions or about adding columns? Why don't you use awk, not sed? I think awk may be better with this. – Yaroslav Nikitenko Jun 8 '17 at 13:49
  • 8
    Please clarify your question and its title. Please use an informative title. – Yaroslav Nikitenko Jun 8 '17 at 13:52
  • By the way, why do you want to add these fields? What is your task? – Yaroslav Nikitenko Jun 8 '17 at 13:53
  • 1
    Could I ask for a better title than "another sed question"? Maybe ask for what you're trying to do, instead. – Jeff Schaller Jun 8 '17 at 14:04
  • if possible, consider having one column with unix timestamp instead of 3 fields – RomanPerekhrest Jun 8 '17 at 14:31
0

As mentioned in the comments, it seems silly to have redundant information like that. But if you really need to do it, use capture groups and back-references:

sed 's/^\(....\)-\(..\)-\(..\)T\(..\).*/&;\1\2\3\4;\1\2\3/'
  • thx for solution I just replaced your solution with sed 's/^(....)-(..)-(..)T(..):&#‌​40;..):(..&#‌​41;.*/&;\1\2\3\4;\1\‌​2\3/' – Murat Jun 9 '17 at 11:17
  • I misread your required output, I thought the first column had the whole time. But you shouldn't have &#40 in the pattern, that doesn't appear in the original. I've updated the answer I think it should work now. – Barmar Jun 9 '17 at 14:01
0
perl -F\; -pale '$_ .= join ";", q{}, $F[0] =~ tr/T-//dr =~ /^((\d{8})\d{2})/' CSV.file
0

Your input seems to be ;-delimited, so I'm assuming that you'd like the output to be similarly delimited.

$ cat file.csv
2017-05-08T21:59:10.263Z;some;other;fields
2017-05-08T21:59:10.000Z;some;other;fields
2017-05-08T21:59:10.000Z;some;other;fields
2017-05-08T18:59:11.000Z;some;other;fields
2017-05-08T18:59:11.000Z;some;other;fields
2017-05-08T21:00:00.000Z;some;other;fields
2017-05-08T21:00:00.000Z;some;other;fields
2017-05-08T21:00:00.000Z;some;other;fields
2017-05-08T21:00:00.000Z;some;other;fields
2017-05-08T21:00:00.000Z;some;other;fields
$ awk -F ';' -v OFS=';' '{ split($1,a,":"); gsub("[^0-9]","",a[1]); $(NF+1)=a[1]; $(NF+1)=substr(a[1],0,8); print}' file.csv
2017-05-08T21:59:10.263Z;some;other;fields;2017050821;20170508
2017-05-08T21:59:10.000Z;some;other;fields;2017050821;20170508
2017-05-08T21:59:10.000Z;some;other;fields;2017050821;20170508
2017-05-08T18:59:11.000Z;some;other;fields;2017050818;20170508
2017-05-08T18:59:11.000Z;some;other;fields;2017050818;20170508
2017-05-08T21:00:00.000Z;some;other;fields;2017050821;20170508
2017-05-08T21:00:00.000Z;some;other;fields;2017050821;20170508
2017-05-08T21:00:00.000Z;some;other;fields;2017050821;20170508
2017-05-08T21:00:00.000Z;some;other;fields;2017050821;20170508
2017-05-08T21:00:00.000Z;some;other;fields;2017050821;20170508

The awk program takes the first ;-delimited field and splits it on :. It then removes all non-digits from the first part of the field (the bit before the first :) and adds it as a new field at the end. A second new field is then appended to the end of the line, consisting of only the first eight characters of the first new field. The new line is then printed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.