Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import of Text Files (Access 2000)

    I have a text file that I would like to import

    One of the fields is in the format 100000 and I would like to import it as 1000.00 thus divide the field by 100. Is that possible or how should I do it.

    One of the other fields is a date field but some of the dates is ddmmyy and some is only ddmm. How will I change it to show ddmmyy.

    Thanks for the help

    Mario

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Import of Text Files (Access 2000)

    First, some questions:

    1. For the 100000 field, will all fields be divided by 100?
    2. For the date field ddmm, How will you know which year to apply?

    I would suggest importing the data in the text file in its original format. Then develop a query to apply calculations and formatting. You could then base reports or other queries against this query

    This keeps the original dataset intact should you need to review it.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import of Text Files (Access 2000)

    Gary has given you the basic idea. I agree with him that it is best to import the text file without modifications, and then use a query with calculated fields.

    I would suggest importing the numeric field as long integer (or single if it already contains decimal fractions), and the date field as text.

    Assuming that you want the year to be the current year if it is missing in the text file, the expressions for converting them in a query could look like:

    F1: [Field1]/100

    F2: DateSerial(IIf(Len([Field2])=4,Year(Date()),Mid([Field2],5)),Mid([Field2],3,2),Left([Field2],2))

    You can format F1 as Fixed and F2 as Short Date.

    In this example, the name the numeric field is Field1 and that of the date field is Field2. Substitute the appropriate names.

Posting Permissions

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