data help...excel access

Discussion in 'OT Technology' started by speedlife, Apr 2, 2003.

  1. speedlife

    speedlife Guest

    i have information im trying to pull out of a program called ceridian, export to a file and import to access. preferably export as a txt delimited file.

    each name has about 5 different records associated with it. but the name is not associated with every record. it sort of looks like this.
    ex.
    jim xyz red
    blue
    green
    rich xyz red
    blue
    green

    so when i import it to a txt, the 2nd and 3rd record of each group gets dumped to the bottom and there is no way to know where it belongs, either to jim or rich. i guess it is the way it is sorted. is there a solution to this? or must it be done manually by copying the name over and over again
     
  2. Early Apex

    Early Apex Guest

    Not sure I understand...

    You're saying that each "name" (=record?) has about 5 different fields associated with it, but in the file coming out of ceridian, the fields are exported randomly ("there is no way to know where it belongs, either to jim or rich")? If there is no way to associate a field with a record, what you're asking is impossible.

    However, if you can define patterns in the export file (ie, all fields after a "name" field and before the start of the next "name field" belong to the same record), then we can work with that. It'll involve quite a bit of custom VBA work, though.

    It'd be much easier if you could get a more regular file/export from ceridian.
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    I would import it into Excel first just like you have as a text file but delimited by spaces. That should give you a grid like this:

    Code:
    jim    | xyz    | red
    blue   |
    green  |
    rich   | xyz    | red
    blue   |
    green  |
    Then create a formula in d1 that says =a2 and a formula in e1 that says = a3. Copy those formulas for all records. Now import into access and eliminate all records where the 2nd field is null
     

Share This Page