Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
Hopefully my illustration describes why I'm baffled:
https://www.dropbox.com/s/sb8wkiypq9...le-01.jpg?dl=0 The problem must arise in this first section, before calling other three macros which further manipulate the imported text. Sub ImportFile_Copy() ' ' ImportFile_Copy Macro ' Imports the TXT file _ ' C:\Users\terry\Dropbox\Excel+VBA (Sundry)\TEMP-VariableList.txt Workbooks.OpenText Filename:= _ "C:\Users\terry\Dropbox\Excel+VBA (Sundry)\TEMP-VariableList.txt", Origin:=xlMSDOS, _ StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1)), _ TrailingMinusNumbers:=True Windows("TEMP-VariableList.txt").Activate ' Change size & position Application.Left = 782.75 Application.Top = 1.25 Application.Width = 658.5 Application.Height = 879.5 ' Set col A & B width to 26 & 88 and align both left Columns("A:A").ColumnWidth = 26 Columns("A:A").HorizontalAlignment = xlLeft 'Columns("B:B").Select Columns("B:B").ColumnWidth = 26 'Selection.ColumnWidth = 88 Columns("B:B").HorizontalAlignment = xlLeft etc It's almost as if Excel's Import Wizard totally ignores regional settings and uses the USA standard. And I'm almost certain this was working smoothly a few days ago... Could it be yet another Win 10 WU issue? Terry, East Grinstead, UK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
Terry,
This has been a contentious issue since Vista when the default date format switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text files is to 'dump' the file contents into an array so I can setup number formats to suit the template the data will get 'dumped' into. Unfortunately, Excel uses default formats when importing csv/tsv/txt files and so inaccurate data results. What's important about importing from these sources is that they are created as expected by the host template. This breaks when the file has been created in XP (for example) because date formats don't align with Vista & later OSs. Possible Solutions: Format date columns before importing the data via Excel's Import feature; Read the data into an array via VBA, then either... format the date data; format the target date cols; ..before dumping the data into the sheet/template. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
GS wrote:
Terry, This has been a contentious issue since Vista when the default date format switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text files is to 'dump' the file contents into an array so I can setup number formats to suit the template the data will get 'dumped' into. Unfortunately, Excel uses default formats when importing csv/tsv/txt files and so inaccurate data results. What's important about importing from these sources is that they are created as expected by the host template. This breaks when the file has been created in XP (for example) because date formats don't align with Vista & later OSs. Possible Solutions: Format date columns before importing the data via Excel's Import feature; Read the data into an array via VBA, then either... format the date data; format the target date cols; ..before dumping the data into the sheet/template. Thanks Garry. I've just been reading many posts about this and related problems. Those and now your detailed reply prompt me to the conclusion that it's a dog's dinner and really too much like hard work to fix confidently. The import wizard simply isn't quite smart enough IMO. A text entry of 12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th August. I may settle for popping up a message at the appropriate point, reminding the user (me) to correct a reversed date. Terry, East Grinstead, UK |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
GS wrote:
Terry, This has been a contentious issue since Vista when the default date format switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text files is to 'dump' the file contents into an array so I can setup number formats to suit the template the data will get 'dumped' into. Unfortunately, Excel uses default formats when importing csv/tsv/txt files and so inaccurate data results. What's important about importing from these sources is that they are created as expected by the host template. This breaks when the file has been created in XP (for example) because date formats don't align with Vista & later OSs. Possible Solutions: Format date columns before importing the data via Excel's Import feature; Read the data into an array via VBA, then either... format the date data; format the target date cols; ..before dumping the data into the sheet/template. Thanks Garry. I've just been reading many posts about this and related problems. Those and now your detailed reply prompt me to the conclusion that it's a dog's dinner and really too much like hard work to fix confidently. The import wizard simply isn't quite smart enough IMO. A text entry of 12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th August. The conversion seems to be based on the 1st part (12/) in that if it's over 12 it's taken as a day, otherwise a month. My approach works consistently reliable to the various tasks I use it with. Answer seems to lie in the format of target date cols BEFORE the data is entered! I may settle for popping up a message at the appropriate point, reminding the user (me) to correct a reversed date. Terry, East Grinstead, UK -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
GS wrote:
GS wrote: Terry, This has been a contentious issue since Vista when the default date format switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text files is to 'dump' the file contents into an array so I can setup number formats to suit the template the data will get 'dumped' into. Unfortunately, Excel uses default formats when importing csv/tsv/txt files and so inaccurate data results. What's important about importing from these sources is that they are created as expected by the host template. This breaks when the file has been created in XP (for example) because date formats don't align with Vista & later OSs. Possible Solutions: Format date columns before importing the data via Excel's Import feature; Read the data into an array via VBA, then either... format the date data; format the target date cols; ..before dumping the data into the sheet/template. Thanks Garry. I've just been reading many posts about this and related problems. Those and now your detailed reply prompt me to the conclusion that it's a dog's dinner and really too much like hard work to fix confidently. The import wizard simply isn't quite smart enough IMO. A text entry of 12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th August. The conversion seems to be based on the 1st part (12/) in that if it's over 12 it's taken as a day, otherwise a month. My approach works consistently reliable to the various tasks I use it with. Answer seems to lie in the format of target date cols BEFORE the data is entered! I may settle for popping up a message at the appropriate point, reminding the user (me) to correct a reversed date. Terry, East Grinstead, UK I think the Import Wizard's design should allow you to enable BOTH General and one of those DMY options. So data that it (cleverly) decides is meant to be a date (rather than text) should then be formatted as specified. Instead of unintelligently applying the USA default. Or, at the very least, in ambiguous cases like the one under discussion, it should apply the user's OS regional setting. As it's bright enough to decide that 13/8/2017 is 13th August, a few more lines of code should enable it to convert 12/8/2017 (on my UK PC) to 12th August, not 8th December. However, before dozing off last night I had a flash of inspiration. Should have thought of it before. In my annotated screenshot, B5 contains the track name. That always starts with eight digits representing the date of the walk/hike/trip. I have to leave my PC for a couple of hours now for Saturday chores. My intention after returning is to try writing another VBA macro after the import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it with Macro Express Pro. Terry, East Grinstead, UK |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it with Macro Express Pro. Terry, East Grinstead, UK Did you try to set the format to show the short name of the month instead of its numeric value? What I see in B6 and the format you've chosen makes sense for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the other way around; month=8, day=12. You cou make a macro to parse the left side of B5 and build the date format from its parts: Option Explicit Sub FixDate() Dim m, d, y, s s = Range("B5").Value y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2) With Range("B6") .NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y End With End Sub Note that I use the above format for reliable consistency with date cells so there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always have clarity!) Edit that to your liking. In financial transaction sheets where the calendar year is a fiscal year, I omit the year in the TransactionDate field so "mmm-dd" is all that displays. If you don't want macros in the project file, put this is PERSONAL.xls. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
GS wrote:
I have to leave my PC for a couple of hours now for Saturday chores. My intention after returning is to try writing another VBA macro after the import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it with Macro Express Pro. Terry, East Grinstead, UK Did you try to set the format to show the short name of the month instead of its numeric value? What I see in B6 and the format you've chosen makes sense for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the other way around; month=8, day=12. You cou make a macro to parse the left side of B5 and build the date format from its parts: Option Explicit Sub FixDate() Dim m, d, y, s s = Range("B5").Value y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2) With Range("B6") .NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y End With End Sub Note that I use the above format for reliable consistency with date cells so there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always have clarity!) Edit that to your liking. In financial transaction sheets where the calendar year is a fiscal year, I omit the year in the TransactionDate field so "mmm-dd" is all that displays. If you don't want macros in the project file, put this is PERSONAL.xls. Thanks Garry, looks good, will try it shortly. I have just been doing the same thing with this formula for extracting d/m/y from Track name's first 8 characters: =CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)) I was baffled why that failed - but then realised that I hadn't carefully thought it through. I placed it into B6 but it's getting over-written by the import. I'll provide more detail as I anticipate further discussion! 1. For all my walks/hikes/trips I get data automatically from a GPX file. I do that using MX Pro and three mapping programs (GPS Utility, Memory Map, Google Earth). Altogether over 50 items (text, numbers, times, dates). All are variables in MX Pro. 2. To get these into a worksheet I was at first using MX Pro to successively paste each into 50 cells. Kludgy and glacially slow. So now I first quickly build and save a text file (with MX Pro) with 50 entries of two columns. For example, the first 13 are destined for cols B and C of my worksheet. All, of course, are at this stage simply text. In particular, the line 6 entry contains Walk date[TAB]12/8/17 A point I've not mentioned before is that I have an alternative way of preparing that Walk Date for the text file, namely =TEXT("12/8/2017", "dddd dd mmmm yyy") If that reached B6 of my worksheet as it stands, that would display Saturday 12 August 2017. Very close to the result I want, which is Saturday 12th August 2017. 3. I then use the VBA macro we've been discussing to import that text file as a workbook TEMP-VariableList.txt. Which is where confusion starts, due to the import wizard's shortcomings. 4. As you see from the lower section of my worksheet https://www.dropbox.com/s/3wv0z1d12o...-Full.jpg?dl=0 B27 & B28 are for use in another macro, saving this sort of image: https://www.dropbox.com/s/50o092d9ul...ample.jpg?dl=0 Terry, East Grinstead, UK |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
GS wrote:
I have to leave my PC for a couple of hours now for Saturday chores. My intention after returning is to try writing another VBA macro after the import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it with Macro Express Pro. Terry, East Grinstead, UK Did you try to set the format to show the short name of the month instead of its numeric value? What I see in B6 and the format you've chosen makes sense for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the other way around; month=8, day=12. You cou make a macro to parse the left side of B5 and build the date format from its parts: Option Explicit Sub FixDate() Dim m, d, y, s s = Range("B5").Value y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2) With Range("B6") .NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y End With End Sub Note that I use the above format for reliable consistency with date cells so there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always have clarity!) Edit that to your liking. In financial transaction sheets where the calendar year is a fiscal year, I omit the year in the TransactionDate field so "mmm-dd" is all that displays. If you don't want macros in the project file, put this is PERSONAL.xls. Hi Garry, Your macro works fine here, many thanks. I've currently applied it to K6 in the DD/MM/YY format. But meanwhile I'd implemented an approach using a formula. With the date 12/08/17 in B6, the following formula I obtained elsewhere a couple of weeks ago =TEXT(B6,"dddd")&" "&DAY(B6)&IF(OR(DAY(B6)={1,2,3,21,22,23,31}),CHOOS E(1*RIGHT(DAY(B6),1),"st ","nd ","rd "),"th ")&TEXT(B6,"mmmm yyyy") clevely displayed it complete with ordinals, i.e: Saturday 12th August 2017 So I've now edited that formula and it can be applied to B5 to give the same result. But it was a VERY tedious and error-prone task. I was rather amazed that it worked! Is there a neater way to do it please? =TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"dddd")&" "&DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))&IF(OR(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))={1,2,3,21,22,23,31}),CHOOSE(1*RIGH T(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2))),1),"st ","nd ","rd "),"th ")&TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"mmmm yyyy") Finally (for now!) is there a general way to code a VBA macro to insert that or any formula? Both seem to have have pros and cons so I'd value the choice. Terry, East Grinstead, UK |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
So I've now edited that formula and it can be applied to B5 to give the
same result. But it was a VERY tedious and error-prone task. I was rather amazed that it worked! Is there a neater way to do it please? =TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"dddd")&" "&DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))&IF(OR(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))={1,2,3,21,22,23,31}),CHOOSE(1*RIGH T(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2))),1),"st ","nd ","rd "),"th ")&TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"mmmm yyyy") Claus is the "formula wizard" here but I can have a go at it. First thing I'd lose is the CONCAT() function in favor of "&" for brevity as well as clarity. Finally (for now!) is there a general way to code a VBA macro to insert that or any formula? Both seem to have have pros and cons so I'd value the choice. Having VBA insert the formula adds overhead to the workbook/sheet Calculation method. Having VBA insert the result as a Value is more efficient, IMO. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
Hi Terry,
Am Sat, 17 Feb 2018 22:46:32 +0000 schrieb Terry Pinnell: =TEXT(B6,"dddd")&" "&DAY(B6)&IF(OR(DAY(B6)={1,2,3,21,22,23,31}),CHOOS E(1*RIGHT(DAY(B6),1),"st ","nd ","rd "),"th ")&TEXT(B6,"mmmm yyyy") try: =TEXT(B6,"dddd ")&DAY(B6)&IFERROR(VLOOKUP(DAY(B6),{1,"st";2,"nd"; 3,"rd";21,"st";22,"nd";23,"rd";31,"st"},2,0),"th") &TEXT(B6," MMMM YYYY") Regards Claus B. -- Windows10 Office 2016 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
How about a UDF compromise? This gives you the VBA to use in code OR as a cell
formula as... =FixDate2(B5) Public Function FixDate2(rng As Range) Dim s, m, d, y, v() s = Left$(rng.Value, 8): ReDim v(2) v(2) = Mid(s, 1, 4): v(1) = Right$(s, 2): v(0) = Mid(s, 5, 2) s = Format(Join(v, "/"), "dddd dd mmmm, yyyy"): d = Split(s, " ") Select Case Day(CDate(Join(v, "/"))) Case 1, 21, 31: d(1) = d(1) & "st" Case 2, 22: d(1) = d(1) & "nd" Case 3, 23: d(1) = d(1) & "rd" Case Else: d(1) = d(1) & "th" End Select FixDate2 = Join(d, " ") End Function -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Day & Month reversed when importing text
Thanks both. Been away for a while but aim to try both of those suggestions ASAP.
Terry, East Grinstead, UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text month to numeric month | Excel Programming | |||
EXCEL: How to scan text reversed (like ACCESS: InStrRev)? | Excel Worksheet Functions | |||
EXCEL: How to scan text reversed (like ACCESS: InStrRev)? | Excel Worksheet Functions | |||
Converting Month Number to Month Text Abbreviation | Excel Worksheet Functions | |||
How can I show the Month in Text with the =month command | New Users to Excel |