Date conversion on text file import

[Calmack]

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.

 

[vezerid]

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

 

[Joel]

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

 

[Jim Rech]

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
|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.

 

[Calmack]

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]

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
| 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
| > | > |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.
| >
| >