Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Unanswered: Easy import .txt in Access

    Hello.

    Is there an easy way to import .txt data into Access 2000 mdb file.

    I have one .txt file which I manualy import on my computer in 7 steps:

    1. Open mdb database
    2. choose table Articles and delete all records which I need to import
    3. File -> Import -> Get External Data (select .txt and choose file articles.txt)
    4. choose Advanced Tab, select my predefined Specification, next, next
    5. choose where to store -> I choose In an Existing table and choose empty table Articles
    6. Next, Finish
    7. Confirm YES

    After that I need to upgrade this mdb on my server. My question is if it is possible to that somehow automatically, or even more advanced, to uplaoad only articles.txt on the web and then ONLINE make some this auto update of database.

    Thank you.

  2. #2
    Join Date
    Mar 2003
    Location
    Memphis, TN, USA
    Posts
    61
    May i know... what that text file contains ?

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    You can connect to a csv file (which can be opened in Excel and which an Excel file can be converted into) in good form just as you can to a regular database.

    And you can have two recordsets open at the same time.

    So I'd suggest going through this recordset one row at a time and within this loop add a new record to the "real" database's recordset.

    And for help connecting to a text file using the Jet OLE DB provider:
    http://www.able-consulting.com/MDAC/...crosoftJetText

    And based on the above link realize that the actual filename does NOT go in the connection string - rather it goes in the SQL statement (definitely a little tricky).
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Jan 2004
    Posts
    30
    Originally posted by sqlboy
    May i know... what that text file contains ?
    Yes
    Articles.txt file looks like example:

    0101001Article_name1 blabla
    0101002Article_name2 blabla
    0101003Article_name3 blabla
    0201001Article_name4 blabla

    So, every field in Articles.txt has set width of field which I have define for import in Access in specification file. In Access to do that it is easy, but I think if this possible to do all that on the fly, via Internet.

    Thank you.

  5. #5
    Join Date
    Jan 2004
    Posts
    30
    Originally posted by Bullschmidt
    You can connect to a csv file (which can be opened in Excel and which an Excel file can be converted into) in good form just as you can to a regular database.
    Thanks, so isn't possible just to import data with this default size data, you must firstly separate them somehow?
    Am I correct?

    Thanks.

  6. #6
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I'm not sure but of course you could consider it to be a .csv file with one field and then use Split() on the one field splitting using the space character.

    And the following article uses Split() with the FileSystemObject instead of setting up a connection to the text file:

    Parsing with join and split - 5/9/1999
    http://www.4guysfromrolla.com/webtech/050999-1.shtml
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  7. #7
    Join Date
    Jan 2004
    Posts
    30
    Thank you, I have managed other way, to auto import on my computer with only one click and then upload .mdb.

    Then I have come to idea why not upload zipped mdb, because zipped is arround 90% smaller file, and then use some script which do that:

    rename db.mdb db.mdb.old
    unzip db.zip

    ?

    WHat do you think, I will fisrtly check google for that, I think that this is possible.

  8. #8
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Well I gave you two hard ways to do things online and you mentioned an easy way to do things right on your desktop. And yes, it would be nice if the online stuff were just as easy!
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  9. #9
    Join Date
    Jan 2004
    Posts
    30
    No it isn't problem that way.

    Is problem because I need firstly to convert my .txt file with fixed records string to csv firstly.

    That I can manage with Excel but this is one more step in converting and I need to that daily so I it's anoying to do every morning this task againg and over again.

    I will firstly auto convert all this into .mdb file, which then will be zipped and upload it automatically to server folder.
    After that old mdb will be renamed to .old and new one will be extracted.

    It is much faster.

    Thank you anyway for help.

  10. #10
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And I doubt that the Replace(MyString, " ", ",") would work to replace each space with a comma because then could inadvertently replace spaces within a field...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  11. #11
    Join Date
    Jan 2004
    Posts
    30
    Yes you are right, but now I have some other idea, I can take all fields with MID command.

    Hm, I will see, thank you for idea .

Posting Permissions

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