Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    Dallas, Texas, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Text Files Into Access (Access 97)

    I am having difficulty importing a text file that has a negative number in it. I am using the fixed width import. The problem is that the negative sign is at the beginning of the field with several spaces between it and the actual number. Ecxel, however, has no problem recognizing this as a negative number. I am working with a report that has been turned into a non-delimitted text file from my IS Dept. This is because the system they use cannot export delimitted for some reason. I don't believe them, but have no authority. Has anyone dealt with this type of situation?

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Importing Text Files Into Access (Access 97)

    I haven't hit this myself, but I guess I can offer a couple of suggestions on how to cope with it. You could, I suppose, import into Excel and then into Access. Or, you might want to import into a temp table, then use an append query to add to your other table. The problem field would be defined as a text in your temp table, then write a function to examine it and remove those troublesome blanks during the append.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text Files Into Access (Access 97)

    Adding to Mark's suggestions... import it as fixed width text, then use Ctrl+H (Edit | Replace) to eliminate all blanks in that field.

  4. #4
    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: Importing Text Files Into Access (Access 97)

    If the file is non delimited, are each of the fields the same length? If the length is 5, would negative 2 be
    -0002 or -00002? Could you post an example of your file. I have dealt with this before with data conversions but need to know a little more about the data being supplied to you. Could you please define the number of fields, length of fields, and any other specifics that may apply (i.e., field length, properties, etc)

    The fixed width import should still work. When using the import, have you tried going to the advance tab to set the width and starting point of each field?
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text Files Into Access (Access 97)

    One more possibility:
    Import the file with fixed length, but make a field for the sign.
    Run an update query to multiply the number with -1 if the sing field contains -
    Francois

  6. #6
    New Lounger
    Join Date
    Apr 2002
    Location
    Dallas, Texas, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text Files Into Access (Access 97)

    Thanks for your help! I have been going crazy trying to come up with ideas or resources. Attached is one of the files I work with. If you will notice, all numeric fields have the same amount of character space. The negative number when applicable, is always located to the left of the number in the field. However, it is so far away from the number that Access seems not recognize it and in addition, it completely leaves out that number all together, leaving that individual part of the record in that column as a null value.

    If I import this into Excel, Excel recognizes it as a negative number....I find it odd how within the same Office Suite, that one of the apps recognizes it and one does not. Of course, I am a novice quasi - programmer want-to-be in training without books, cuz I iz a poor person...lol. Anyway, I appreciate all your ideas and suggestions. Thanks in advance
    Attached Files Attached Files

  7. #7
    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: Importing Text Files Into Access (Access 97)

    Since the format of your text file is very inconsistent, try this approach. You will need to physically count the width of the columns. I believe they are something like this, 14,15,16,16,16,16,16,20,20,15,15,16 and so on.

    In access use File, Get External Data, Import and select the text file. Select Delimited Width and then select the Advance Button. You will need to tell Access how to import the data by specifying the field widths (See Attachment) For example, Field 1, Start at position1, Width 15. Then Field 2, Start at position 15, Width 15, and so on. This works as I tried it against your file.

    Tell your departments it would be much easier if they could make all columns the same width. You should also ask them to save the file as a .csv (Comma Separated Value) file which will make this much easier. They should be able to make a .csv file.

    Good luck.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    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: Importing Text Files Into Access (Access 97)

    See attachment
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  9. #9
    New Lounger
    Join Date
    Apr 2002
    Location
    Dallas, Texas, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text Files Into Access (Access 97)

    Thanks to each of you for your suggestions. I have started using some of them and actually mixing some of your ideas together and I am already having some progress. I may post again for some other things I am trying to accomplish with the same set of files....but first I want to see how far I get with the help you have already given me. Please let me know how I can promote anything that you do. I have a personal web page that I can add links to for you if you have any business that you are affiliated with. Just let me know.... [img]/forums/images/smilies/smile.gif[/img] Thanks again to ALL!

  10. #10
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Text Files Into Access (Access 97)

    Use the Edit Profile here at the lounge to include your homepage, so we can surf over and take a look <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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