0

I have millions of rows of data my boss has asked me to reformat for them.

The format is:

06/28/2022,04:00,142.01,142.38,141.59,142.15,3151
06/28/2022,04:01,142.1,142.1,142.1,142.1,196

I need to reformat the first date field to:

20220628,04:00,142.01,142.38,141.59,142.15,3151
20220628,04:01,142.1,142.1,142.1,142.1,196

%Y%m%d

I have the following:

gawk -F"," '{OFS=","; $1=strftime("%Y%m%d", $1); print $0}' AAPL.txt > AAPL.csv

but the weird thing is it works, but produces a date in 1969.

19691231,04:00,142.01,142.38,141.59,142.15,3151
19691231,04:01,142.1,142.1,142.1,142.1,196

I don't understand why. I chose gawk because awk on MacOS doesn't have strftime and calling date externally create a huge performance hit.

0

2 Answers 2

6

Your code does not do what you expect it to do because the GNU awk strftime() expects a Unix timestamp as its second argument. It's unable to parse an arbitrary datetime string.

However, we don't really need strftime() here.

$ awk -F , 'BEGIN { OFS=FS } { split($1,a,"/"); $1 = a[3] a[1] a[2] }; 1' file
20220628,04:00,142.01,142.38,141.59,142.15,3151
20220628,04:01,142.1,142.1,142.1,142.1,196

This treats each line of input as simple comma-delimited fields and splits the first such field up on / into the array a. The first field is then re-formed as the elements of the array concatenated in the wanted order.

The lone 1 at the end of the awk code causes the modified record to be outputted.

This would work with the default awk on macOS. It does not need special date formatting functions as it treats the input date as a string and simple reorganises it. The only assumption about the date is that it always is in the DD/MM/YYYY format in the input and that it should be in the YYYYMMDD format in the output.

0

You don't need function like strftime to do date-handling since all you need to do is swap fields. sed works nice here :

sed 's@\([^/]*\)/\([^/]*\)/\([^,]*\)@\3\1\2@' sample

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .