Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date conversion on text file import
I am in the UK and I am trying to import a tab-delimited text file which
contains all dates in US date-time format, eg 03/14/2008 9:12:25 AM. I import the file into Excel and it goes through the wizard. I set the date field to MDY, but when I press Finish the dates appear either as a UK date (when both month and day are less than/equal 12) or as text (when it cannot convert). I have tried it by editing the text file and separating date and time with tabs before importing and that works. But I don't want to have to do editing like that. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date conversion on text file import
If A2 contains text looking like a date in mm/dd/yyyy then the
following formula will generate the date: =DATE(--RIGHT(A2,4),--LEFT(A2,2),--MID(A2,4,2)) HTH Kostis Vezerides On Nov 11, 4:52*pm, Calmack wrote: I am in the UK and I am trying to import a tab-delimited text file which contains all dates in US date-time format, eg 03/14/2008 9:12:25 AM. *I import the file into Excel and it goes through the wizard. *I set the date field to MDY, but when I press Finish the dates appear either as a UK date (when both month and day are less than/equal 12) or as text (when it cannot convert). *I have tried it by editing the text file and separating date and time with tabs before importing and that works. *But I don't want to have to do editing like that. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date conversion on text file import
The code below will open your input text file and create a new output text
file. the code will modify the dates in the file. You need to modify the following items 1) MyPath 2) Input Filename 3) Output Filename 4) DateCol which is the tab column starting at 0 where the date is located in th einput file. column A would be 0, colun B would be 1. Option Base 0 Sub Gettext() Const ForReading = 1, ForWriting = 2, ForAppending = 3 Const MyPath = "C:\temp\" Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0 TABCR = Chr(9) Dim StrDate As String Set fsread = CreateObject("Scripting.FileSystemObject") Set fswrite = CreateObject("Scripting.FileSystemObject") ReadFileName = "longtext.txt" WriteFileName = "longtext.csv" DateCol = 0 'open files ReadPathName = MyPath + ReadFileName Set fread = fsread.GetFile(ReadPathName) Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault) WritePathName = MyPath + WriteFileName fswrite.CreateTextFile WritePathName Set fwrite = fswrite.GetFile(WritePathName) Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault) Do While tsread.atendofstream = False 'Extract String Date from Input Line InputLine = tsread.ReadLine SplitData = Split(InputLine, CR) StrDate = SplitData(DateCol) DateArray = Split(StrDate, "/") 'Swap Month and Date Temp = DateArray(0) DateArray(0) = DateArray(1) DateArray(1) = Temp 'combine data back to original format StrDate = Join(DateArray, "/") SplitData(DateCol) = StrDate OutputLine = Join(SplitData, TABCR) tswrite.writeline OutputLine Loop tswrite.Close tsread.Close Exit Sub End Sub "Calmack" wrote: I am in the UK and I am trying to import a tab-delimited text file which contains all dates in US date-time format, eg 03/14/2008 9:12:25 AM. I import the file into Excel and it goes through the wizard. I set the date field to MDY, but when I press Finish the dates appear either as a UK date (when both month and day are less than/equal 12) or as text (when it cannot convert). I have tried it by editing the text file and separating date and time with tabs before importing and that works. But I don't want to have to do editing like that. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date conversion on text file import
I flipped to UK settings and got exactly your issue with a text file in US
format when manually opening the file. But with a macro it came in fine. Workbooks.OpenText Filename:="c:\datetime.txt", DataType:=xlDelimited, Tab:=True This is because, when a macro is running, Excel is in 'US settings mode' despite your local settings. So it reads the US format file correctly. -- Jim "Calmack" wrote in message ... |I am in the UK and I am trying to import a tab-delimited text file which | contains all dates in US date-time format, eg 03/14/2008 9:12:25 AM. I | import the file into Excel and it goes through the wizard. I set the date | field to MDY, but when I press Finish the dates appear either as a UK date | (when both month and day are less than/equal 12) or as text (when it cannot | convert). I have tried it by editing the text file and separating date and | time with tabs before importing and that works. But I don't want to have to | do editing like that. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date conversion on text file import
I've solved the problem. Just needed to add English (United States) format
to my regional settings. I've now got the language bar to appear on my taskbar, though I do not need to change settings now as Excel is automatically recognising the US format. "Jim Rech" wrote: I flipped to UK settings and got exactly your issue with a text file in US format when manually opening the file. But with a macro it came in fine. Workbooks.OpenText Filename:="c:\datetime.txt", DataType:=xlDelimited, Tab:=True This is because, when a macro is running, Excel is in 'US settings mode' despite your local settings. So it reads the US format file correctly. -- Jim "Calmack" wrote in message ... |I am in the UK and I am trying to import a tab-delimited text file which | contains all dates in US date-time format, eg 03/14/2008 9:12:25 AM. I | import the file into Excel and it goes through the wizard. I set the date | field to MDY, but when I press Finish the dates appear either as a UK date | (when both month and day are less than/equal 12) or as text (when it cannot | convert). I have tried it by editing the text file and separating date and | time with tabs before importing and that works. But I don't want to have to | do editing like that. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date conversion on text file import
Just needed to add English (United States) format to my regional settings.
Most people do not consider that a 'solution' as they do not want foreign regional settings.. -- Jim "Calmack" wrote in message ... | I've solved the problem. Just needed to add English (United States) format | to my regional settings. I've now got the language bar to appear on my | taskbar, though I do not need to change settings now as Excel is | automatically recognising the US format. | | "Jim Rech" wrote: | | I flipped to UK settings and got exactly your issue with a text file in US | format when manually opening the file. But with a macro it came in fine. | | Workbooks.OpenText Filename:="c:\datetime.txt", DataType:=xlDelimited, | Tab:=True | | This is because, when a macro is running, Excel is in 'US settings mode' | despite your local settings. So it reads the US format file correctly. | | -- | Jim | "Calmack" wrote in message | ... | |I am in the UK and I am trying to import a tab-delimited text file which | | contains all dates in US date-time format, eg 03/14/2008 9:12:25 AM. I | | import the file into Excel and it goes through the wizard. I set the date | | field to MDY, but when I press Finish the dates appear either as a UK date | | (when both month and day are less than/equal 12) or as text (when it | cannot | | convert). I have tried it by editing the text file and separating date | and | | time with tabs before importing and that works. But I don't want to have | to | | do editing like that. | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date to text conversion | Excel Discussion (Misc queries) | |||
Date & Time Conversion from xml import | Excel Discussion (Misc queries) | |||
Text Date Conversion | Excel Discussion (Misc queries) | |||
import /paste -stop automatic conversion of text 05-12345 to date | Excel Discussion (Misc queries) | |||
import csv without automatic date conversion | Excel Discussion (Misc queries) |