+ Reply to Thread
Results 1 to 26 of 26

Imported text 12/8/2017 becomes 8/12/2017

  1. #1
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Imported text 12/8/2017 becomes 8/12/2017

    Hopefully my illustration describes why I'm baffled by this issue:

    DatePuzzle-01.jpg

    I've shown the import macro below in full. But the problem must arise in the first section, before calling the other three macros which further manipulate the imported text.

    Please Login or Register  to view this content.
    It's almost as if Excel's Import Wizard 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?

    I've spent hours trying in vain to see what's wrong, so help would be much appreciated please!
    Last edited by terrypin; 02-16-2018 at 05:24 PM.
    Terry, East Grinstead, UK
    Excel 365, Win 10 Pro, i7 PC, 4.0 GHz

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Imported text 12/8/2017 becomes 8/12/2017

    I think the problem is in step 2 -- where you specify to import the columns by general settings. By specifying general settings, you leave yourself at the mercy of Excel's whims to decide whether to interpret a text date as d/m/y or m/d/y or whatever else Excel might choose to do. If I were doing this, I would try being more specific about the data type for these date columns by changing the FieldInfo parameter of the text import.
    text import wizard VBA help: https://msdn.microsoft.com/en-us/vba...t-method-excel
    list of constants for different column type designations: https://msdn.microsoft.com/en-us/vba...meration-excel

    In your point #4, you mention that the number formatting looks right. I would point out that the cell's number formatting is applied after the text import -- after Excel has decided whether to interpret the text as DMY or MDY and converted to the corresponding serial number. The problem is not in the number/date formatting being applied after the import occurs. The problem seems to be what Excel is seeing as it imports the data before it applies the number formatting.

    To test I created a simple tab delimited text file
    Please Login or Register  to view this content.
    Then I turned on the macro recorder while importing the text file. During step 3, I specified general for column 1, Date MDY for column 2, and Date DMY for column 3. The result was as expected -- columns 1 and 2 both imported as 8 dec 2017, column 3 imported as 12 aug 2017. The resulting FieldInfo parameter from the macro recorder was:
    Please Login or Register  to view this content.
    meaning, if I understand the help files correctly, column 1 is treated as general, column 2 is MDY, and column 3 is DMY.

    My advice is to edit your text import/open step and explicitly tell Excel to treat those dates as DMY dates.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Thanks Mr Shorty, appreciate your taking the time to get on the case. I agree fully with your key conclusion.

    IMO Excel's Import Wizard is not quite as smart as it should and could be. I think its 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 apparently (unintelligently) applying the USA default. 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.

    "My advice is to edit your text import/open step and explicitly tell Excel to treat those dates as DMY dates."

    That was my thinking too, but I'm still a VBA novice and don't see how to achieve it. Any pointers would be welcomed.

    However, before dozing off last night I had a flash of inspiration. Should have seen 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 but my intention after returning is to try writing another VBA macro after the import to convert 20170812 to 12/8/2017 in B6.
    Last edited by terrypin; 02-17-2018 at 04:36 AM.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Try changing this line
    Please Login or Register  to view this content.
    as
    Please Login or Register  to view this content.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    kvsrinivasamurthy,

    Thank you, valuable feedback as it shows how a problem like this could sometimes be solved with one character changed. Unfortunately it doesn't fix this case. B6 is now correct, but several other rows in that column are now wrong. That was why I couldn't use the D/M/Y option when recording the macro.

    BTW, can you please tell me how I would most easily find that information about the array parameters? When I placed my cursor in 'FieldInfo' and used the F1 key I got a page headed
    "Keyword Not Found, Office Shared [vblr6.chm1009553]". And Help on 'Array' was generic - and over my head anyway!
    Last edited by jeffreybrown; 02-17-2018 at 04:20 PM. Reason: Removed full quote!

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    If you need to change only one date after "Walk date" then this will edit the txt file, so you can import after the change.
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Thanks jindon, I'll try that tomorrow morning.

  8. #8
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    @jindon: As a VBA novice I'm having difficulty understanding your macro and how to use it. Applying it to 12/08/17 in B6 did not change or display anything. Could you suggest an example of applying it on a fresh worksheet please?

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    My code changes the order of D/M for one date from the text file, so run my code first, then run your code and see if the questioned date changes after import.

    Otherwise I need to see the part of text file that you have problem.

  10. #10
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    I've tried placing the macro in a module in TrackData-New.xlsm, TEMP-VariableList.TXT and PERSONAL.XLSB but in each case I get the same error #70, 'Run time error Permission Denied' and this line is highlighted:
    Open fn For Output As #1

  11. #11
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Imported text 12/8/2017 becomes 8/12/2017

    You might try a small change
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    OK, problem DropBox.

    This will create a .txt file on your desktop so, try import this .txt file.
    Please Login or Register  to view this content.
    So your code should look like
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Thanks Don, neat, works a treat!

  14. #14
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Thanks jindon, now have your macro working as you intended.

    But it doesn't deal with the inherent poorly designed behaviour of an Excel import, as I suggested up-thread, post #3.

    Don's simple solution prompted further research and I found this article, which confirms my view and offers that workaround.

    https://support.microsoft.com/en-in/...macro-to-conve

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    As I said in my first post, my code just swaps the date/month part for one date, and don't even guess the outcome of importation from there.

    Dates are troublesome anyway.

  16. #16
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Quote Originally Posted by jindon View Post
    As I said in my first post, my code just swaps the date/month part for one date, and don't even guess the outcome of importation from there.

    Dates are troublesome anyway.
    They sure are! I've spent countless hours over the last week or so going from baffled to enlightened and back again, several times over

  17. #17
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    A further puzzle has arisen. After applying Don's edit, the basic import macro works fine as I reported. But when I run ImportFile_Copy the Track/Walk date in the orginal text file gets D/M reversed to M/D. It's obviously due to my extra code. By commenting out each of the three called macros I got to the status shown below. That implies that saving the text file causes the reversal. Yet my illustration shows no reason I can see for that.

    PuzzlingReversal.jpg

    Or here at full resolution: https://www.dropbox.com/s/hlga0l9i87...ersal.jpg?dl=0

    And full code:
    Please Login or Register  to view this content.
    Last edited by terrypin; 02-20-2018 at 07:13 AM.

  18. #18
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Apologies if I've missed it but have we seen the text file you're dealing with. If not please upload the .txt file.
    Also upload it to Excel and in a column alongside any dates which are wrong add the correct date.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Please Login or Register  to view this content.
    Do above procedures make any change to the text file?

  20. #20
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Quote Originally Posted by jindon View Post
    Do above procedures make any change to the text file?
    No. As shown, I successively commented out each of them to test.

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    And you still need to save it as .txt?

  22. #22
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Quote Originally Posted by Richard Buttrey View Post
    Apologies if I've missed it but have we seen the text file you're dealing with. If not please upload the .txt file.
    Also upload it to Excel and in a column alongside any dates which are wrong add the correct date.
    I showed the part including the one relevant entry, Track Date (or 'Walk Date') in my detailed illustration, covering all three files, including the original text file and its import into Excel. Here's another example:
    https://www.dropbox.com/s/z2ylzbmch7...zzle.jpg?raw=1

  23. #23
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Quote Originally Posted by terrypin View Post
    I showed the part including the one relevant entry, Track Date (or 'Walk Date') in my detailed illustration, covering all three files, including the original text file and its import into Excel. Here's another example:
    https://www.dropbox.com/s/z2ylzbmch7...zzle.jpg?raw=1
    I wasn't looking for a picture but the .txt file itself. Along with an Excel sheet that has imported the text file and where the incorrect dates are identfied and corrected - a representative sample will suffice.

  24. #24
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Quote Originally Posted by Richard Buttrey View Post
    Apologies if I've missed it but have we seen the text file you're dealing with. If not please upload the .txt file.
    Also upload it to Excel and in a column alongside any dates which are wrong add the correct date.
    OK, Richard, here's the text file as it appears in my text editor:
    https://www.dropbox.com/s/3kh5oo1jps...List.txt?raw=1
    As described, line 6 has been reversed, even though it still appears correctly within Excel, both in the imported text file and the target worksheet as illustrated earlier. That's the puzzle.

    And here's the imported text file, now exported as an xlsx file:
    https://www.dropbox.com/s/7nzvdb9zk7...ist.xlsx?raw=1

  25. #25
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Hi,

    Not sure exactly where you are with this but using this Data Connection macro it seems to work out that 8/14/2017 is a date and since the default assumption for all the date syntax in VBA is US format it presumably takes that as the base format, looks at your regional settings and decides it needs to convert it to UK style.

    Please Login or Register  to view this content.

  26. #26
    Forum Contributor terrypin's Avatar
    Join Date
    01-06-2010
    Location
    East Grinstead, UK
    MS-Off Ver
    MS Office 365
    Posts
    533

    Re: Imported text 12/8/2017 becomes 8/12/2017

    Xlnitwit’s edit solved that problem, which I first raised in post #3.

    Anyway, it’s only a matter of curiosity to me now. As its name implies, that text file is a temporary one. The correct date gets to B6 on the track data worksheet. It remains an unsolved puzzle as to why my last subsequent commands change just the original text file to the wrong format, but leaves the Excel version corrected by that Local=True parameter.
    Last edited by terrypin; 02-21-2018 at 10:50 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 23
    Last Post: 01-03-2022, 10:45 AM
  2. [SOLVED] Format Jan 10, 2017 as 1/10/2017
    By elkhornbabe in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-26-2018, 09:11 AM
  3. Replies: 2
    Last Post: 10-01-2017, 02:38 PM
  4. Replies: 10
    Last Post: 05-13-2017, 01:58 AM
  5. [SOLVED] How to format dates like 9/25/2017 to 09/25/2017
    By chessmonsta in forum Excel General
    Replies: 11
    Last Post: 03-17-2017, 03:11 AM
  6. To all and Excel-lent 2017
    By Keebellah in forum Excel General
    Replies: 1
    Last Post: 01-01-2017, 06:09 AM
  7. Not sure how to explain this but I need to do it about 2017 times!!
    By Andrewm2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-22-2012, 09:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1