Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing several text files with different specs (2003)

    Hi,

    Wondering...

    I have a set of 11 textfiles coming in, which need to be imported into 11 distinct tables.

    The text files have no headers
    The text files may have different field separators and different data formats.

    My problem:

    I want to use VBA to import the text files. Do I really need to have (number of different formats) * 11 import specifications?

    Being a simple Excel expert <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I thought perhaps I could pass the formats as a parameter to some import function that looks something like:

    Private Function ImportTable(sTextFileName as String, sTargetTable as String, sFieldDelim as String, sDateFormat As String, bFourDigitYear as Boolean, bLeadingZeroes as Boolean)

    But TransferText doesn't allow this, or does it?

    Question 2: If I save an import spec, Access insists on creating field names, if I try to import another text file (targetting same table, same # of fields), Access complains that the field names don't match. WHat gives?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Importing several text files with different specs (2003)

    DoCmd.TransferText does not let you specify the delimiter etc.
    You do need a separate import specification for each layout.
    There is no support in the Access VBA object model for creating or modifying import/export specifications, but if you're careful, you can manipulate them: they are stored in two system tables:
    MSysIMEXSpecs contains a record for each specification.
    MSysIMEXColumns contains a record for each column (field) in each specification.
    You can use DAO or ADO to work with these tables.
    If you want to see what they look like, (temporarily) tick the System Objects check box in the View tab of Tools | Options...

    About question 2: do you have a mixture of text files with and without field names in the first row?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing several text files with different specs (2003)

    I cracked Q2 already, it is a matter of first opening the textfile, then defining and saving the import spec, then setting the target table, then stepping back through the wizard two times, clicking advanced again and lo and behold, Access has now updated the field names. Save import spec again and we're done. Far from user friendly, nor as they call "Discoverable". Duh.

    I've also found a way using DAO and SQL in conjunction with a schema.ini file, but I cannot get that to work either.

    I guess I'll bite the bullet and start defining import specs for each of them. Tedious, but it should do the job.

    Does anyone know of a tool that can help you manage import specs?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Importing several text files with different specs (2003)

    Some time ago unkamunka mentioned the free V-Tools collection. It includes an import/export specification editor/manager.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing several text files with different specs (2003)

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Importing several text files with different specs (2003)

    It's nice that Access 2007 finally exposes import/export specifications in the object model! Better late than never...

Posting Permissions

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