CSV Import US Date Format

[rfletcher35]

Hi,

I'm sure this has been covered but I cannot find the answer. I have a csv import that has in column A a date and time, I have downloaded this csv for say just Feb, it works well but I cannot change the date format to UK.
This is causing an issue as when the date reaches after the 12th of the month it gets confused. I really need it to read as UK format so I can filter correctly. I've tried a number of things so far including text to columns but cannot seem to get it to work.

Can someone please help me with this?

Please!

 

[Alex Blakenburg]

Some options:
1) Import the file using Power Query and setting the Locale for the field to the Country that has the text file date format eg US
2) Change the file extention of the file from csv to txt and then import it so that you get the import wizard, you can then access the import parameters to tell it the imported date format is US mm/dd/yyyy
3) Use a macro to perform option 2 above

 

[rfletcher35]

Some options:
1) Import the file using Power Query and setting the Locale for the field to the Country that has the text file date format eg US
2) Change the file extention of the file from csv to txt and then import it so that you get the import wizard, you can then access the import parameters to tell it the imported date format is US mm/dd/yyyy
3) Use a macro to perform option 2 above

Thanks but i'm afraid i've never used Power Query and i'm struggling to get my head around your instructions, apologies.

 

[AlanY]

here is a short YT on power query

How Power Query Will Change the Way You Use Excel

via @YouTube

once you use it, you can't live without it

 

[Alex Blakenburg]

I would encourage you to look at the video posted by @AlanY.
If you still want to do it without power query then is your pc set up to show file extension ie can you see the ".csv" after the filename ?
If not you need to turn on show file extensions in Windows explorer. I am on Windows 11 and there it is in an explorer window View > Show > File Name Extensions
Either on the original file or a copy of it change the .csv to .txt
If you now use File Open and select the file you will get the import wizard.
You then need follow the image below before hitting Finish

 

[rfletcher35]

I would encourage you to look at the video posted by @AlanY.
If you still want to do it without power query then is your pc set up to show file extension ie can you see the ".csv" after the filename ?
If not you need to turn on show file extensions in Windows explorer. I am on Windows 11 and there it is in an explorer window View > Show > File Name Extensions
Either on the original file or a copy of it change the .csv to .txt
If you now use File Open and select the file you will get the import wizard.
You then need follow the image below before hitting Finish

View attachment 63824

Hi,

Thanks for the assist, worked all that out and did everything but i'm still getting the issue - see screen shot, this is after changing the column to MDY.

 

[Georgiboy]

Is this a file that is comma delimited?

I use the below code quite a bit on the fly to pull in CSV files and avoid the date issue (effectively still a query)

Sub test()
    Dim fPath As String
   
    fPath = "C:\Users\jbloggs\Desktop\test\sample.csv" '< path of csv file

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fPath, Destination:=Range("$A$1"))
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

There are ways to manage the date within this but would need to know how many columns the CSV file contains etc...

With the above code,
Open the VBE (code window 'ALT&F11')
Right click on Sheet1 in the VBE
Hover over 'Insert' and then select 'Module'
Double click on the module that was inserted
Paste the above code in there
Change the file path to the location of your CSV

 

[rfletcher35]

Is this a file that is comma delimited?

I use the below code quite a bit on the fly to pull in CSV files and avoid the date issue (effectively still a query)

Sub test()
    Dim fPath As String
  
    fPath = "C:\Users\jbloggs\Desktop\test\sample.csv" '< path of csv file

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fPath, Destination:=Range("$A$1"))
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

There are ways to manage the date within this but would need to know how many columns the CSV file contains etc...

With the above code,
Open the VBE (code window 'ALT&F11')
Right click on Sheet1 in the VBE
Hover over 'Insert' and then select 'Module'
Double click on the module that was inserted
Paste the above code in there
Change the file path to the location of your CSV

Hi,

This sounds promising but i'm getting an error when running as below.

 

[Georgiboy]

Are you running the code in a separate spreadsheet and have you pointed fPath at the CSV to be imported?

Have a look at the import options on the ribbon of Excel, you are looking for the import from text option, it will guide you on importing the data. I believe they are under the Data tab in the ribbon and over to the left.

 

[rfletcher35]

Are you running the code in a separate spreadsheet and have you pointed fPath at the CSV to be imported?

Have a look at the import options on the ribbon of Excel, you are looking for the import from text option, it will guide you on importing the data. I believe they are under the Data tab in the ribbon and over to the left.

Hi,

So on the first run I mistyped the path, correcting that I ran the code and it opened the file but the dates are still incorrect, what am I missing here?

Fletch