CODEWITHSUNDEEP
perlshellsedawk
user1676605
user1676605

Reputation: 1447

Convert a file from using one date format to another

I have a bunch of files that contain a bunch of quotes in the following csv format:

"Date","Time","Open","High","Low","Close","Volume"
12/30/2002,0930,24.53,24.65,24.53,24.65,762200
12/30/2002,0931,24.65,24.68,24.52,24.6,90400

I need to convert the file(s) to a new file(s) with almost the same name (for example file.txt to file-NTF.txt) to look like this:

"Date Time","Open","High","Low","Close","Volume"
20021230 093000;24.53;24.65;24.53;24.65;762200
20021230 093100;24.65;24.68;24.52;24.6;90400

So the date and time columns got merged into one, and the format for the date is different is different. Also the commas became semi-colon. I got this far in awk

awk -F "," '{print $1,$2}' file.txt

which would print the date and time of the original file, but now I am stuck.

Upvotes: 1

Views: 136

Answers (3)

potong
potong

Reputation: 58488

This might work for you (GNU sed):

sed -r '1{s/","/ /;b};s/^(..).(..).(....).(....)/\3\1\2 \400/;y/,/;/' file

For the first line only replace the first double-quote, comma double-quote by space.

For second and subsequent lines apply the substitution and the translation.

However to replace all commas by semi-colons use:

sed -r '1s/","/ /;1!s/^(..).(..).(....).(....)/\3\1\2 \400/;y/,/;/' file

Upvotes: 0

shellter
shellter

Reputation: 37298

Try

awk -F, 'BEGIN{
  print "\"Date Time\";\"Open\";\"High\";\"Low\";\"Close\";\"Volume\""
}
NR>1 {
  printf("%04d%02d%02d %04d00;%s;%s;%s;%s\n", \
    substr($1,7,4), substr($1,1,2), substr($1,4,2),  $2, \
    $3, $4, $5, $6 $7)
}' file

output

"Date Time";"Open";"High";"Low";"Close";"Volume"
20021230 093000;24.53;24.65;24.53;24.65762200
20021230 093100;24.65;24.68;24.52;24.690400

I have taken the liberty of using the field separator that you show in your sample output records and use that in the Header as well. commas as delimiters are a hassle, IHMO.

IHTH

Upvotes: 0

Chris Seymour
Chris Seymour

Reputation: 85875

Using GNU awk:

awk 'NR>1{print $3$1$2" "$4"00",$5,$6,$7,$8,$9;next}{sub(/","/," ")}1' FS='[/,]' OFS=';' file
"Date Time","Open","High","Low","Close","Volume"
20021230 093000;24.53;24.65;24.53;24.65;762200
20021230 093100;24.65;24.68;24.52;24.6;90400

Upvotes: 2

Related Questions