UK Date (TEXT) to US Data

[britinva]

Hi, I have a CSV file where the 1st Column has the Date/Time as a Text String in format "DD/MM/YYYY hh:mm"

e.g. 21/11/2020 00:15

I need to change this to a US Date only format "MM/DD/YYYY"

e.g. 11/21/2020

Anyone help with the code to do this?

Sample file attached.

Thanks,
Mark

[alansidman]

I see that your profile indicates you are using 2007. If you have upgraded, please adjust your profile to reflect a newer version. If you are using a newer version then Power Query is an option for you.

Here is the Mcode for PQ

Please Login or Register  to view this content.

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	Date	SFN	BW	Metric Name	Value
2	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	1.387
3	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	1.437
4	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	1.337
5	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	1.441
6	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	2.276
7	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	2
8	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	2.54
9	11/21/2020	Atlanta_GigabitEthernet0/3/2	500M	To Site Port Utilisation	2.479

Sheet: Sheet1

[britinva]

Thanks alansidman, I'm using 2013 now so have updated and will try suggestion.

Never used PQ so may need to look that up

[hrlngrv]

You could fix such files using batch files at a command line.

CORRECTION: I misread the part about reducing date+time to date only.

fix-csv.cmd

Please Login or Register  to view this content.

Save this in a directory in your PATH, then open a console window, change to the directory containing sample.csv, and run fix-csv sample.csv . That would create a new sample.csv file with dates in ISO format, yyyy-mm-dd, which Excel would interpret correctly under ALL Windows regional settings, not just UK and US. The original file would be renamed sample.csv.save just in case.

Better still would be to change the date format in the upstream system which generates these CSV files to use ISO date format.

[jindon]

This swops the position of Date/Month and create a new csv file named _New in the same folder of original csv file.

Please Login or Register  to view this content.

[hrlngrv]

OP's sample file may only be an abbreviated sample. A TextStream .ReadAll call and the RegExp replace on the entire file in one go may be suboptimal if the OP's actual CSV files are large.

[britinva]

Thanks everyone - a few things to try the CSV will typically have circa 20,000 rows.

[jindon]

the CSV will typically have circa 20,000 rows.

Not huge...

[alansidman]

Not large for PQ either.

[hrlngrv]

The question is best tool for the task. In part, that'd mean least code needed. FWIW, this would be simpler using command line tools.

gawk "{ print gensub("^([0-9]+)/([0-9]+)/([0-9]+) [0-9:]+", "\\3-\\2-\\1", 1) }" sample.csv > new.csv

There may be a clever Powershell 1-liner, but I just can't force myself to use Powershell.

Actually, if one had Vim, so ex for the command line, one could edit sample.csv in place.

ex -c "g;^\([0-9]*\)/\([0-9]*\)/\([0-9]*\)[ :0-9]*;s;;\3-\2-\1;" -c 'wq' sample.csv

My point here is that while PQ and VBA with or without Windows Script Host embellishment can handle this, they're considerably wordier than command line tools. Also, command line tools could run asynchronously in the background.

[jindon]

The question is best tool for the task. In part, that'd mean least code needed.

That's only your opinion.
This is a VBA forum, so choice is up to OP.

[hrlngrv]

. . . That's only your opinion. . . .

Did I ever state or imply otherwise?

However, I'd note that for others with similar problems but using Macs, Windows Script Host and full Power Query aren't available. Neither would be Windows batch files, but command line tools like ex would be.

[britinva]

Thanks again everyone, for me the VBA sub was what was looking for as will be part of a larger VBA script to manipulate the data