Alpha Video Training
Results 1 to 7 of 7

Thread: .txt fiel imports as one field

  1. #1
    Member
    Real Name
    Susan
    Join Date
    Jun 2010
    Posts
    68

    Default .txt fiel imports as one field

    I have this .txt file that should be split into several fields and it imports as one giant memo field. I can't figure out what is the delimiter and how to split the data into separate fields. I figured I'll import it thenuse genie to split it, it seems like at least 5 spaces separates each so I'll use that. The last problem, there are 2 fields that are numbers and letters together, like WI54678 (the numbers being the zip code). In each of the 2 fields I know where to separate it, for WI after 2 letters and fot the other field first numbers (8 of them) then last name. Please help on how to separate this?
    Thanks. :)

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

    Default Re: .txt fiel imports as one field

    Quote Originally Posted by FreeBird View Post
    I have this .txt file that should be split into several fields and it imports as one giant memo field. I can't figure out what is the delimiter and how to split the data into separate fields. I figured I'll import it thenuse genie to split it, it seems like at least 5 spaces separates each so I'll use that. The last problem, there are 2 fields that are numbers and letters together, like WI54678 (the numbers being the zip code). In each of the 2 fields I know where to separate it, for WI after 2 letters and fot the other field first numbers (8 of them) then last name. Please help on how to separate this?
    Thanks. :)
    I used to live close to there... OK 60 miles from there..

    Like this?
    Code:
    field1="WI54678"
    
    state=substr(field1,1,2)
    ?state
    = "WI"
    
    zip=substr(field1,3,5)
    ?zip
    = "54678"
    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.

  3. #3
    Member
    Real Name
    Susan
    Join Date
    Jun 2010
    Posts
    68

    Default Re: .txt file imports as one field

    Here is the format this is in, i know where the column should start and end, how do i use this? (my .txt file is also attached)
    Starting
    Column Ending
    Column Field name (includes authority
    codes where appropriate) Characters
    1 8 Agent License Number 8
    9 43 Agent Last Name 35
    44 68 Agent First Name 25
    69 93 Agent Middle Name 25
    94 133 Agent Address One 40
    134 173 Agent Address Two 40
    174 198 Agent City 25
    199 200 Agent State 2
    201 211 Agent Zip Code 11
    212 213 Agent Authority (Casualty-11) 2
    214 215 Agent Authority (Property-12) 2
    216 217 Agent Authority (Automobile-13) 2
    218 219 Agent Authority (Accident and Health-14) 2
    220 221 Agent Authority (Life-16) 2
    222 223 Agent Authority (Title-18) 2
    224 225 Agent Authority (Credit-30) 2
    226 227 Agent Authority (Legal Expense-31) 2
    228 229 Agent Authority (Personal Lines P & C-32) 2
    230 231 Agent Authority (Crop-39) 2
    232 233 Agent Authority (Surety-40) 2
    234 235 Agent Authority (Travel-41) 2
    236 237 Agent Authority (Variable Life/Variable Annuity-42) 2
    238 247 Expiration Date (MM/DD/YYYY) 10
    248 257 Status Date (MM/DD/YYYY) 10
    258 329 E-Mail Address 72

  4. #4
    Member
    Real Name
    Susan
    Join Date
    Jun 2010
    Posts
    68

    Default Re: .txt fiel imports as one field

    OK, Al thanks!
    What about if the state is always different, i.e. it could be any of the 50 states?

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

    Default Re: .txt fiel imports as one field

    Quote Originally Posted by FreeBird View Post
    OK, Al thanks!
    What about if the state is always different, i.e. it could be any of the 50 states?
    That shouldn't matter - What I'm seeing in your previous post is that the state abbrev is always a 2 character code..

    Looking at your information in the previous post (again) you should be using a Table ASCII file import(second option of import type) and use the definition listed.

    There is probably not a delimiter in this file.
    Attached Images Attached Images
    Last edited by Al Buchholz; 06-22-2010 at 07:52 PM.
    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.

  6. #6
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: .txt fiel imports as one field

    Senia.
    it seems like at least 5 spaces separates each
    Could a "tab" be the delimiter? If you actually attached your text file you are attempting to import, we may be of more help....
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  7. #7
    Member
    Real Name
    Susan
    Join Date
    Jun 2010
    Posts
    68

    Default Re: .txt fiel imports as one field

    Thanks everyone. I di what Al said and it worked, instead of importing it I saw the code and made sure it imported the fields as specified, and it worked great!
    Thank you!

Similar Threads

  1. Import Problem - Character field imports with zeroes
    By Charlain in forum Alpha Five Version 8
    Replies: 1
    Last Post: 07-09-2007, 07:56 AM
  2. get and save the path for an image fiel reference field
    By martinwcole in forum Alpha Five Version 7
    Replies: 4
    Last Post: 07-23-2006, 06:56 PM
  3. Use of calculated field in calculated fiel
    By pinzgauer in forum Alpha Five Version 5
    Replies: 6
    Last Post: 07-08-2004, 12:06 AM
  4. v6 and imports
    By Alan Lee in forum Alpha Five Version 5
    Replies: 0
    Last Post: 06-22-2004, 06:48 AM
  5. Imports new and old
    By Susan Bush in forum Alpha Five Version 4
    Replies: 8
    Last Post: 12-05-2000, 11:39 AM

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
  •