Results 1 to 2 of 2
  1. #1
    CTBarbarin is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    1

    AC2010: Table linking to text files via code using schema.ini

    (
    Is cross-posting possible? Is it kosher?

    This same topic is co-posted Access Forums > Import/Export Data. Please forgive me if it is rude to do this. This is my first time on this site.
    )
    <moderator note: duplicate thread deleted. Not rude, but we prefer it not be done. >

    Hello All,




    How can I link text files via code with a schema.ini file?


    If my schema.ini file look like this:
    Code:
    [File One.txt]
    ColNameHeader=True
    Format=Delimited(|)
    CharacterSet=ANSI
    TextDelimiter="
    Col1=LOAN_NUMBER Char Width 255
    Col2=PRODUCT_TYPE Char Width 255
    Col3=BALANCE Char Width 255
    Col4=RATE Char Width 255
    Col5=ORIGINATION_DATE Char Width 255
    Col6=MATURITY_DATE Char Width 255
    Col7=MONTH_END_DATE Char Width 255
    Col8=F_V_INDEX Char Width 255
    Col9=SPREAD Char Width 255
    Col10=PERCENT_OWNERSHIP Char Width 255
    Col11=REVOLVING_IND Char Width 255
    Col12=IS_NON_PERFORMING Char Width 255
    Col13=PRODUCT_DESCRIPTION Char Width 255
    Col14=PRODUCT_SUB_TYPE Char Width 255
    Col15=PRODUCT_TYPE_1 Char Width 255
    or even this:
    Code:
    [File Two.txt]
    ColNameHeader=True
    TextDelimiter="
    Format=Delimited(|)
    CharacterSet=ANSI
    and I run the following code:


    Code:
    Sub LinkToTextFiles()
        On Error GoTo ErrHandler
        ' include a reference to the "Microsoft Scripting Runtime"
        Dim pfdgFolder As FileDialog
        Dim pstrFolder As String
        Dim pstrFile As String
        Dim pstrTableName As String
        
        Set pfdgFolder = Application.FileDialog(msoFileDialogFolderPicker)
        pfdgFolder.AllowMultiSelect = False
        pfdgFolder.Title = "Select a Folder"
        pfdgFolder.Show
        pstrFolder = pfdgFolder.SelectedItems(1)
        
        ChDir pstrFolder
        
        pstrFile = Dir("*.txt")
        
        Do Until pstrFile = ""
            pstrTableName = Replace(Left(pstrFile, 7), "-", "")
            DoCmd.TransferText acLinkDelim, , pstrTableName, pstrFile, True
            
            pstrFile = Dir()
        Loop
        
        GoTo CleanUp
    ErrHandler:
        MsgBox Err.Description
        Resume CleanUp
        Resume 'debugging only
    CleanUp:
        MsgBox "done."
    End Sub

    ...I incorrectly wind up with a table for each file containing only one column...the first column.


    Yet, when I import with the following code (and the same schema.ini file):


    Code:
    Sub LinkToTextFiles()
        On Error GoTo ErrHandler
        Dim pfdgFolder As FileDialog
        Dim pstrFolder As String
        Dim pstrFile As String
        Dim pstrTableName As String
        
        Dim pcdb As DAO.Database
        
        
        Set pcdb = CurrentDb()
        Set pfdgFolder = Application.FileDialog(msoFileDialogFolderPicker)
        pfdgFolder.AllowMultiSelect = False
        pfdgFolder.Title = "Select a Folder"
        pfdgFolder.Show
        pstrFolder = pfdgFolder.SelectedItems(1)
        
        ChDir pstrFolder
        
        pstrFile = Dir("*.txt")
        
        Do Until pstrFile = ""
            pstrTableName = Replace(Left(pstrFile, 7), "-", "")
            
           pcdb.Execute "SELECT * INTO " & pstrTableName & " FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=" & pstrFolder & ";].[" & Replace(pstrFile, ".", "#") & "];", dbFailOnError
            pcdb.TableDefs.Refresh
           pstrFile = Dir()
        Loop
        
        GoTo CleanUp
    ErrHandler:
        MsgBox Err.Description
        Resume CleanUp
        Resume 'debugging only
    CleanUp:
        MsgBox "done."
    End Sub

    I successfully import all columns from all files into individual tables. But I don't want to import data (essentially duplicating the data). I want to link to the text files.


    Is there another way to link text files via code (using DAO methods)? Am I doing something wrong when trying to use DoCmd.TransferText? Is there something wrong with my schema.ini file (doesn't seem like it since it works for the DAO.Execute method)?


    Thanks for any help anyone can provide,


    CTB
    Last edited by pbaldy; 09-27-2016 at 09:55 PM. Reason: Add note regarding duplicate thread

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    the SELECT INTO statement is the part that's importing the data, if you want to link the file you'd have to use something like docmd.transfertext outlined here:

    https://msdn.microsoft.com/en-us/lib.../ff835958.aspx

    As long as all your files have the same setup this shouldn't be an issue, you would just need to create an import spec and apply that to any incoming file.

    If your files can have a variable number of columns and a different field names that's a bit more complex and you'd likely have to read the first column for the field names then create a table to do the import but I suspect that's not the case since you're using loops.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-04-2016, 12:50 AM
  2. Replies: 7
    Last Post: 08-25-2015, 05:19 PM
  3. Multiple Text Files Into One Table
    By raldrich in forum Import/Export Data
    Replies: 2
    Last Post: 05-20-2014, 07:59 AM
  4. Export table to several text files
    By ShadeTree in forum Programming
    Replies: 3
    Last Post: 04-24-2012, 08:02 AM
  5. Saving Module Code to Text Files
    By ioMatt in forum Modules
    Replies: 2
    Last Post: 07-02-2011, 08:18 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums