+ Reply to Thread
Results 1 to 2 of 2

Date problems when importing txt files

  1. #1
    GC
    Guest

    Date problems when importing txt files

    Hello,

    I have an application that reads data into Access. It accepts different file
    types (.txt, .xls), opens them in excel, then manipulates the data and stores
    the date in SQL-Server. The files cannot be opened manually (there are lots
    of them!), but using the command:

    dim booki As Excel.Workbook
    set booki = General.Excel.Workbooks.Open(fileName)

    Where fileName is the file name provided by the user.

    The problem is that when providing a .txt file, Excel reads the date as
    mm/dd/yyyy,
    while I need it as dd/mm/yyyy. My regional setting is set to dd/mm/yyyy, but
    it seems to be ignored.

    It is intersting that when I open the .txt files manually, the dates are
    formatted correctly (dd/mm/yyyy)!

    Does anyone have any idea how to solve the problem? I tried to formatNumber
    the date column, but even this does not help!
    I tried both on win2000 and xp - and I am using excel 2003 and Acess 2003.

    Thanks!
    GC.


  2. #2
    Sharad Naik
    Guest

    Re: Date problems when importing txt files

    This is the basic problem with Access. Didn't know that it was problem with
    text file too.

    In the text file , of course, the date is stored purely as text and has no
    format of it's own,
    unlike a access table.

    One possible way is to use DateSerial Function to swap Day and Month
    normal DateSerial function is DateSerial(year, month, day).
    When the file is a text file (which you can at run time find out with
    Right(fileName, 3)) Then you first get the year, month and day
    and in DateSerial feed (year, day, month). E.g:

    Dim myDate As Date, myDay As String, myMonth As String, myYear As String
    myDate = 'your code to assing the date from the text file
    myDay = DatePart("d", myDate)
    myMonth = DatePart("m", myDate)
    myYear = DatePart("yyyy", myDate)
    myDate = DateSerial(myYear, myDay, myMonth)
    'Note Day and Month are swapeed in above DateSerial statement.

    Now you can assing myDate to a cell in excel or whereever you want to

    Also while writing to Access use DateSerial Function as above, if you want
    to
    make it independent of regional settings.

    e.g. in access you want write todays date. Don't use code like
    Recordset_Name!Date = Date
    instead do as under:
    myDate = Date
    myDay = DatePart("d", myDate)
    myMonth = DatePart("m", myDate)
    myYear = DatePart("yyyy", myDate)
    Recordset_Name!Date_Field_Name = DateSerial(myYear, myMonth, myDay)
    'Note : NO Swapping here !

    This will feed the date correctly to the access file irrespective of the
    regional date setting
    of the machine.

    Sharad


    "GC" <[email protected]> wrote in message
    news:[email protected]...
    > Hello,
    >
    > I have an application that reads data into Access. It accepts different
    > file
    > types (.txt, .xls), opens them in excel, then manipulates the data and
    > stores
    > the date in SQL-Server. The files cannot be opened manually (there are
    > lots
    > of them!), but using the command:
    >
    > dim booki As Excel.Workbook
    > set booki = General.Excel.Workbooks.Open(fileName)
    >
    > Where fileName is the file name provided by the user.
    >
    > The problem is that when providing a .txt file, Excel reads the date as
    > mm/dd/yyyy,
    > while I need it as dd/mm/yyyy. My regional setting is set to dd/mm/yyyy,
    > but
    > it seems to be ignored.
    >
    > It is intersting that when I open the .txt files manually, the dates are
    > formatted correctly (dd/mm/yyyy)!
    >
    > Does anyone have any idea how to solve the problem? I tried to
    > formatNumber
    > the date column, but even this does not help!
    > I tried both on win2000 and xp - and I am using excel 2003 and Acess 2003.
    >
    > Thanks!
    > GC.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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