Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    6

    Unanswered: Splitting Field into Fields

    Hey guys this is my first post and looks a pretty cool place and im hoping some of you could help me out.

    Currently i have a database which is made up of exported files from our backend systems as a tab delimited files. These are cool and they get pretty much what i want but now i have a bit of a problem.

    Each record has 5 fields but the last field has quite a bit of data in it which looks something like this

    \Product Codes\2003 WEB CATALOGUE\Office Furniture\Filing Cabinets

    The thing is i now need to split this up into new field for everything inbetween the \'s. There is no limit the the amount of records made from this field but 7 is proberly the most it ever gets too.

    Has anyone got an suggestions to point me in the right way of doing this.

    Thanks in advance

    BigStu

  2. #2
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: Splitting Field into Fields

    Here is what I would do:
    1.Use the "InStr" function. This will return the position of one string whithin another. For example:

    InStr("Product Codes\2003 WEB CATALOGUE\Office Furniture\Filing Cabinets" , "\")

    =>will return 14.

    2. When you found the position of \ then you can use the "Left" function that returns the left most characters of a string. For example:

    Left("Product Codes\2003 WEB CATALOGUE\Office Furniture\Filing Cabinets", 14)

    =>will return "Product Codes".


    Hope this helps





    Originally posted by bigstu
    Hey guys this is my first post and looks a pretty cool place and im hoping some of you could help me out.

    Currently i have a database which is made up of exported files from our backend systems as a tab delimited files. These are cool and they get pretty much what i want but now i have a bit of a problem.

    Each record has 5 fields but the last field has quite a bit of data in it which looks something like this

    \Product Codes\2003 WEB CATALOGUE\Office Furniture\Filing Cabinets

    The thing is i now need to split this up into new field for everything inbetween the \'s. There is no limit the the amount of records made from this field but 7 is proberly the most it ever gets too.

    Has anyone got an suggestions to point me in the right way of doing this.

    Thanks in advance

    BigStu

  3. #3
    Join Date
    Apr 2003
    Posts
    6
    cheers mate that will be cool i could us this but i need to get all the references between the slashes so that would get me the 1st how would i get the second is there any thing to make it skip the 1st or 2nd slash it come across

    Bigstu

  4. #4
    Join Date
    Mar 2003
    Posts
    46
    Hmmmm...

    This is a bit of a workaround but...

    If you export your field to a delimited textfile (with field delimiter set to "\" and Text Qualifier set to "None" and the re-import your textfile into a table with exactly the same settings you should get a table with as many columns as you need...

    C

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164
    CCC your way is much better if you still have access to the original file.
    I thought BigStu doesn't have access to the original files.

    If all I had was a table with the "/"s I would use the below query. I'm sure there are better ways

    Say your table is table1 and your filed is called "field":

    SELECT
    InStr([field],"\") AS P1,
    Left([field],[P1]) AS F1,
    Right([field],Len([field])-[P1]) AS R1,

    InStr([R1],"\") AS P2,
    Left([R1],[P2]) AS F2,
    Right([r1],Len([R1])-[P2]) AS R2,

    InStr([R2],"\") AS P3, Left([R2],[P3]) AS F3,
    Right([r2],Len([R2])-[P3]) AS R3


    FROM Table1;


    F1,F2 and F3 will be your fields.

    You can repeat the above patters for F1 through Fn by replacing R's and P's with the same pattern.

  6. #6
    Join Date
    Apr 2003
    Posts
    6
    Sia

    Thank you very much that does exactly what i want.

    Cheers

    Stu

Posting Permissions

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