Alpha Video Training
Results 1 to 8 of 8

Thread: Import custom formatted time field from Excel

  1. #1
    Member
    Real Name
    Ron Borum
    Join Date
    Dec 2005
    Location
    Nashville TN
    Posts
    53

    Default Import custom formatted time field from Excel

    I have an excel file and have imported it inot Alpha 5 V 7 table. The problem is the spreadsheet contains some fields that have a custom excel format and I can't get that data to import correctly. Here is the skinny on the data....

    In excel I have a field called "logged in Time per week". The data entered is represented in HHH:MM:SS. If I enter 107:19:24 into the cell all is well. Note in excel if you look at the formula line it shows as a date of 1/14/1900 11:19:24 AM

    If I try to format the cell as text it changes the data in the cell to a value of4.47180555555556.

    When the data is imported into Alpha it imports as 01/04/1900 11:19:24 00 am which alpha sees as a date format. If I change the format to character the data disappears.

    I cannot figure out how to either get this changed in excel so it will export as a character field for A% or how I can get it reformatted in A5 so the data displays as hhh:mm:ss. Anyone out there solved this already. Thanks in advance.

  2. #2
    Member
    Real Name
    Ron Borum
    Join Date
    Dec 2005
    Location
    Nashville TN
    Posts
    53

    Default Re: Import custom formatted time field from Excel

    I think I may have figured it out.....rather than imorting it as from an excel spreadsheet, I saved the spreadsheet as a text file and imported it as a text file...that seems to have done the trick...now I need to change the field types for some and see what challenges that brings....at least the hhh:mm:ss are character fields and as such I can change them to seconds and / or time for the calculations I need.

  3. #3
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,644

    Default Re: Import custom formatted time field from Excel

    Sounds like a plan.

    Please post your findings...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  4. #4
    Member
    Real Name
    Ron Borum
    Join Date
    Dec 2005
    Location
    Nashville TN
    Posts
    53

    Default Re: Import custom formatted time field from Excel

    OK....importing the data as a text file worked fine for the original issue for my time fields. I introduced another issue that I am having a little trouble with and could use some help.....when I imported as a txt file the date field in excel was formatted as Jan-05, Feb-05, Mar-05 etc. This of course now exist as characters in the date field in A5. Since I will be appending this data to an existing table where the date field is an 8 character Date structure.

    Because of this I really need to covert the Jan-05 character to a date field before I append the data (at least I think I do). I can't seem to accomplish this....any ideas on this. Thanks in advance.

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Import custom formatted time field from Excel

    Quote Originally Posted by borumrm
    OK....importing the data as a text file worked fine for the original issue for my time fields. I introduced another issue that I am having a little trouble with and could use some help.....when I imported as a txt file the date field in excel was formatted as Jan-05, Feb-05, Mar-05 etc. This of course now exist as characters in the date field in A5. Since I will be appending this data to an existing table where the date field is an 8 character Date structure.

    Because of this I really need to covert the Jan-05 character to a date field before I append the data (at least I think I do). I can't seem to accomplish this....any ideas on this. Thanks in advance.
    What does "Jan-05" signify? 1-5-2006?

    Code:
    dt = "Jan-05"
    ? ctod(dt+"-2006")
    = {01/05/2006}

  6. #6
    Member
    Real Name
    Ron Borum
    Join Date
    Dec 2005
    Location
    Nashville TN
    Posts
    53

    Default Re: Import custom formatted time field from Excel

    What does "Jan-05" signify? 1-5-2006?

    It actually signifies mmyy. In the master table to which this data will be appened the Date field is stored as mmddyyyy. I just want to be able to convert the character strings of Jan-05, Feb-05 etc within the transaction table to a date structure of mmddyyyy so it will append correctly to the master table when I run the append operation.

  7. #7
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Import custom formatted time field from Excel

    Quote Originally Posted by borumrm
    I just want to be able to convert the character strings of Jan-05, Feb-05 etc within the transaction table to a date structure of mmddyyyy so it will append correctly to the master table when I run the append operation.
    How do you want to pick the dd to put in the field and can we assume that the first yy of yyyy wil be 20?

    Code:
    dt = "Jan-05"
    ? ctod(left(dt,3)+"-01-20"+right(dt,2))
    = {01/01/2005}

  8. #8
    Member
    Real Name
    Ron Borum
    Join Date
    Dec 2005
    Location
    Nashville TN
    Posts
    53

    Default Re: Import custom formatted time field from Excel

    Thanks for the reply Stan....

    You posted the following code

    dt = "Jan-05"
    ? ctod(left(dt,3)+"-01-20"+right(dt,2))
    = {01/01/2005}

    As it turns out this worked great...I did have a little problem at first because it was returning 01/01/1920 instead of 01/01/2005. I figured out the issue was because the data structure is 8 character but only 6 are in the table. JAN-05 is six characters not 8...all I had to do was change your expression ctod(left(dt,3)+"-01-20"+right(dt,4)) to account for the two spaces. I guess I could have trimmed the field first also. Thanks for the help.

Similar Threads

  1. Replies: 0
    Last Post: 06-22-2006, 05:17 AM
  2. Import Date/Time field from MySql
    By EdwardTsai in forum Alpha Five Version 7
    Replies: 3
    Last Post: 06-21-2006, 02:42 AM
  3. Import date time field
    By dparkins in forum Alpha Five Version 5
    Replies: 1
    Last Post: 08-20-2004, 10:04 AM
  4. Formatted calculated field problem
    By Mario Prieto in forum Alpha Five Version 5
    Replies: 4
    Last Post: 11-22-2003, 01:00 AM
  5. Export custom report to Excel
    By Glen Smith in forum Alpha Four Version 6 and Prior
    Replies: 1
    Last Post: 07-26-2003, 04:31 PM

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
  •