Results 1 to 8 of 8

Thread: [Access] Import Specifications

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    [Access] Import Specifications

    Does anyone have any info on how Import Specifications work behind the scenes?
    I need to replace its functionality with a faster option.

    Thanks
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: [Access] Import Specifications

    No time for give'u the elaborate answer that perhaps you are looking for, but here there're some related info:

    http://stackoverflow.com/questions/1...-specification

    HTH

  3. #3

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [Access] Import Specifications

    Thanks! I came across the same system tables that store the specs (I'll post here from your link as well) but there is teh second section of code that may work out best for me. If I can utilize teh import specs (without using the AOM) and just insert into some table with joining the system import spec table applied that would be good I think.

    Code:
    SELECT 
        MSysIMEXSpecs.SpecName,
        MSysIMexColumns.*
    FROM 
        MSysIMEXSpecs
        LEFT JOIN MSysIMEXColumns 
        ON MSysIMEXSpecs.SpecID = MSysIMEXColumns.SpecID
    WHERE
        SpecName = 'MySpecName'
    ORDER BY
        MSysIMEXSpecs.SpecID, MSysIMEXColumns.Start;
    And the procedure thats more interest...

    Code:
    Public Sub MyExcelTransfer(myTempTable As String, myPath As String)
    On Error GoTo ERR_Handler:
        Dim mySpec As ImportExportSpecification
        Dim myNewSpec As ImportExportSpecification
        Dim x As Integer
    
        For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
        If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
            CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
            x = CurrentProject.ImportExportSpecifications.Count
        End If
        Next x
        Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTempTable)
        CurrentProject.ImportExportSpecifications.Add "TemporaryImport", mySpec.XML
        Set myNewSpec = CurrentProject.ImportExportSpecifications.Item("TemporaryImport")
    
        myNewSpec.XML = Replace(myNewSpec.XML, "\\MyComputer\ChangeThis", myPath)
        myNewSpec.Execute
        myNewSpec.Delete
        Set mySpec = Nothing
        Set myNewSpec = Nothing
        exit_ErrHandler:
        For x = 0 To CurrentProject.ImportExportSpecifications.Count - 1
        If CurrentProject.ImportExportSpecifications.Item(x).Name = "TemporaryImport" Then
            CurrentProject.ImportExportSpecifications.Item("TemporaryImport").Delete
            x = CurrentProject.ImportExportSpecifications.Count
        End If
        Next x
    Exit Sub    
    ERR_Handler:
        MsgBox Err.Description
        Resume exit_ErrHandler
    End Sub
    
    Public Sub fixImportSpecs(myTable As String, strFind As String, strRepl As String)
        Dim mySpec As ImportExportSpecification    
        Set mySpec = CurrentProject.ImportExportSpecifications.Item(myTable)    
        mySpec.XML = Replace(mySpec.XML, strFind, strRepl)
        Set mySpec = Nothing
    End Sub
    
    
    Public Sub MyExcelChangeName(OldName As String, NewName As String)
        Dim mySpec As ImportExportSpecification
        Dim myNewSpec As ImportExportSpecification
        Set mySpec = CurrentProject.ImportExportSpecifications.Item(OldName)    
        CurrentProject.ImportExportSpecifications.Add NewName, mySpec.XML
        mySpec.Delete
        Set mySpec = Nothing
        Set myNewSpec = Nothing
    End Sub
    But its executed via VBA. I need to see how it runs behind the scenes so I can execute it as part of my import sql query
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  4. #4

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [Access] Import Specifications

    So it looks like the two tables that store the import specs stores nothing more than the selected options

    I need to know how Access uses this information when importing data with methods like .TransferText

    It uses the info in the method but not sure how it gets around the sql statement from breaking if the data has single or double quotes for ex.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: [Access] Import Specifications

    I may be wrong, but I believe that Office text import uses the Text File Driver to convert text files to tables. For the TransferText method you referenced, see seems also to be the case because can use a "schema.ini" file instead of a import specification; however, it may just read the schema file to create the specification on the fly.

    In any case, I doubt that you find a code list for this method. You indicated that you are looking to speed up the process.
    • Do you intend to write the code in .Net? There are several possibilities here to parse the file.
    • Is this a one time (one file format) application or does it need the flexibility of being supplied a file specification?
    • Are you open to purchasing a third party library?

  6. #6

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [Access] Import Specifications

    Yea I know the textfile driver requires a txt file extension as well as the .TransferText method requires it too.

    Yes, the app is in .NET currently and automating Access to do the .TransferText but this is going away.
    Several files and some different formats.

    Today looks like I may have figured out how to remove the entire requirement on this so I can go directly from the in memory records out tot the database witht eh records transformed before exported.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: [Access] Import Specifications

    Do not mis what the author of select statement say:

    You can also use an UPDATE or INSERT statement to alter existing columns or insert and append new columns to an existing specification. You can create entirely new specifications using this methodology.
    Btw you can transfer excel sheet too, althought ignored whether use spreadsheet is one option for you.
    As txt you can use csv too. The problem when importing txt file is that you should organize data properly before do nothing.
    Anyway you could clean file before import via VB.Net using, lets say regular expressions (Regex) for example.

    Ignore too, the amount of data you are dealing to.

  8. #8

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [Access] Import Specifications

    Yea I cant use any of the built in stuff as its just way too slow. I am rewritting the logic so it doesnt write a textfile out anymore and modifies the datatable for direct export to access
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

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



Click Here to Expand Forum to Full Width