Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing a text file (Access 2003)

    Hello All,

    I'm currenly working on a project that requires me to grab information that is kept in our PDM system (Agile), such as manufacture, manufacture part number, compliance, notes, etc. Since I have no way to link to this data (corporate policy for people like me), I've been exporting entire data tables (20K to 40K records) from this program in a comma delimited text file then loading it up into Access via Access' import. When using Access' import feature, I'm able to load the data in a table in the correct format as long as a comma doesn't exist in the data itself. If a comma does exist, in say the notes field, Access thinks that it must go to the next field when actually it's part of the data in the same field. Is their some way to get this to work? I've tried changing the delimator (find and replace in Word, Notepad, and Wordpad) to a special character that I know is not used in our data but this takes a LONG time for the find a replace to do it's thing.

    Ideally, I would like a user (myself and a couple others) to be able to click a button, open a text file selected by the user, and then have the file load directly into a specific table. However, this is easier said than done when your a novice user of Access with no VB training and limited background. I don't know how I could do a lot of this, but if I could get the text into a variant or string, I could use MID, LEN, and other easy functions to extract the data to the table. I have a strong feeling that this wouldn't be the best way, but not knowing VB that we'll, I don't know of any other way. Any help or suggestions would be greatly appreciated.

    Thanks in advance.
    Regards,
    Drew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    If you have data values containing the delimiter, you have a problem whether you import manually or using code. Access has no way of determining whether a comma is a delimiter or part of a value, nor has VBA code.

    Does the export from your PDM system have an option to specify a text qualifier such as "? If so, text values will be enclosed in text qualifiers, and you can specify the same text qualifier when importing into Access.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    Yes, it does enclose data in double quotes (data","data","data).
    I thought you could only specify one character as a delimator when doing the Access import?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    You can specify only one field delimiter, but you can also specify a text qualifier. See screenshot below.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    Hans,

    Sorry I haven't replied sooner. I've been very busy.
    Yes, for every Import I do, I use the double-quote as the qualifier and the data is imported incorrectly. It's seems like I've tried many thing and always get data that doesn't load correctly, which is the reason why I was asking about using VBA.

    Thanks,
    Drew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    VBA uses the same method as the Import Text Wizard, so if importing interactively fails, importing using code will fail too.

    If you wish, you can attach a small sample file. Replace sensitive information with dummy data.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    Hi Hans,

    I must of overlooked the text qualifier in our data on the last couple of test loads. When I done a test this morning, I noticed that we had used one double-qoute to indicate inches followed by a comma. From this point down, my data was off. From what I can tell, Access' Import only uses one double-quote as the text qualifier, but what is really needed for my data is a "," qualifier. This is the reason why I had brought up VBA.
    My thought was to use MID and INSTR functions in a loop to load a table from the text file, looking for "," (I have the knowledge to write the loop; can't figure out how to get a text file declared in VBA). Maybe this would be to processor intensive? The only other way I have to solve this problem is to open the text file in Word and do a find and replace on ",". Changing it to something I know is not used in our data and then indicate that character in the Access Import. This ussually takes quite a while (good part of a morning). Ussually I just won't pull fields containing notes due to this reason (unless absolutely needed).

    Regards,
    Drew

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    Are ALL fields in the text file surrounded by quotes?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    Addition: if all fields are surrounded by quotes, you can use the attached code. You must, of course, substitute the correct names. There are 2 assumptions:
    - You have already created a table in the database with the correct structure.
    - You have set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...
    - The fields do not contain ","

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    Hans,

    Attached is the export from our PDM system of hand picked records that I would have problems loading when using Access' import.

    Your have assumed correctly...
    - A table already exist
    - 3.6 Object Library is selected
    - No field contains ","

    I've tried your code, but the data is not loading correctly. Please note the format of the file. In prior post, I didn't indicate that the extract includes unwanted carrage returns. This is fixable, but I would have to go through all records that include an unwanted carriage return. Is their anyway to compinsate for this? If not, I'll just fix it but will take some time.

    Thanks!
    Drew

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    This is horrible. Is there any chance that you can get the PDM system to deliver usable exports?
    If not, you'll have to clean the exports manually before importing into Access.

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    I can't agree with you more. :-)
    We are a small division of a large corporate comany. Our PDM system is nearly 5 years old and service packs have not been installed due to going to the next version of the software incorporating all other business units. The corporate deadline for this keeps on getting pushed out. In the mean time, it makes it very difficult for myself and others to report on what we put into our system which is the reason why I continue to use extracts along with Access.
    For my problem, I'll either try to clean the extracts manually or just leave the problem fields out.

    Thank you very much for your help.
    Regards,
    Drew

  13. #13
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    Hans,

    Logically, I think I have a way to parse this out, but I have a VBA question.
    When opening a text file in VBA and using loops with it, how does VB know what location in the file you are at? I see that your code continues to loop by using the string found in Line Input #, but I don't know how it knows to go to the next line. In addition, is their a way to use Left(), Mid(), and instr() functions with text files?

    Thanks,
    Drew

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing a text file (Access 2003)

    Line Input automatically shifts the pointer into the file to the next line, ready for the next Line Input instruction. If the end of the file is reached, the EOF(...) function will return True.
    You can use string functions such as Left, Mid, Right and InStr to parse the string variable filled by Line Input.

  15. #15
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing a text file (Access 2003)

    Must line input always be used when working with text files? And, is their anyway to set the pointer to the previous line (or is it pointer forward only)?

    Thanks,
    Drew

Page 1 of 2 12 LastLast

Posting Permissions

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