Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import Log File Excluding Header (2K +)

    I'm looking at trying to automate the import of a (CSV) log file. Access chokes on the Header Line - which is not required in any case. It appears that <code>DoCmd.TransferText</code> operates only on entire files. (On-line Help talks of Import Specification Files (schema.ini), but that seems to rely on the Import Text Wizard which neither my Access 2000 or 2003 seems able to start.) Thus, it would seem that a Mark II version of the log file would have to be created - perhaps using something like Seek - for the purposes of the Import. Is this right - or is there a simpler way of doing this?
    Gre

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

    Re: Import Log File Excluding Header (2K +)

    You may be able to use ADO to read the text file. Otherwise, you could use Scripting.FileSystemObject or the old BASIC text file instructions (Open, Line Input etc.) to create a copy of the file without the header line, and import the copy.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import Log File Excluding Header (2K +)

    I'm puzzled as to why you can't use an import specification - I wrote one last week for a project where I have to import 2.54M records from 26 text files and it worked like a champ. The trick is to do a manual import and create the Import Specification and save it during that import. From then on it can be used in the TransferText operation. FYI, my code looks like this:<font face="Georgia"><font color=blue>

    For FileCount=1 to 26
    DoCmd.TransferText acImportDelim, "Items Import Specification", "DataFlex Items", "ITEMS-" & Format(FileCount, "00") & ",CSV", False
    Next FileCount

    </font color=blue></font face=georgia>I generated the 26 files out of a legacy application and they all had a name of ITEMS-01,CSV which is what the complicated bit in the next to last parameter is about. Note also that you need to click the Advanced button so you can define the Import Spec and then save it. Hope this helps.
    Wendell

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Log File Excluding Header (2K +)

    Wendell, thanks for your support. <UL><LI>The Text Import Wizard only seemed to want to fire in Access 97. Neither Access 2000 or 2003 wanted to co-operate.<LI>The promised Text Import Wizard "export" procedure refused to materialise.<LI>By un-hiding the MSysIMEXColumns and MSysIMEXSpecs Tables and importing them into the DB (& suitable Table renaming), I was able to import the desired specification into the 2000 DB.<LI>The specification does automatically ignore the First Row and supplies the desired Column Headers.<LI>This delivers a much tidier set-up.<LI>As a matter of future reference, the MSysIMEXColumns and MSysIMEXSpecs Tables do not seem to be directly editable. One has to re-run the Text import Wizard to "tweak" them.[/list]It turned out somewhat messy, but we got there!
    Gre

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Import Log File Excluding Header (2K +)

    It sounds to me like something is crook with with your Access installation. The Wizards may not be properly installed, or may not be installed at all - you might check the install options, and you might also try a repair. I'm running both 2002 and 2003 and both seem to work just fine.
    Wendell

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import Log File Excluding Header (2K +)

    Thanks for confirming my suspicions. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> In fact, I have "kitchen sink" installs of all versions 97+ on the same partition. This is the first time I have had anything (in any Office app) show up as "not installed". For the time being, it could be that reinstalling could "tempt fate".
    Gre

Posting Permissions

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