Results 1 to 5 of 5
  1. #1
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904

    Importing Data With Different Field Headers

    So we are exporting some data from another application. The data export is comprised of multiple tables. The export can be in either Excel or CSV format and will always have headers, of which we really have no control over. The issue is that there are fields in both tables that use the same name.

    In the CSV file, the second instance of that name is prefaced by the table name that it came from.


    So, if I have Table1 and Table2, and there is a field in each one named "Active" that is included in my export, the Header row would look like "Active" and "Table2.Active" for these two field headers, respectively.

    If I choose to export to Excel instead, both fields are given the same header value, i.e. "Active".

    I am trying to import this file into an existing Table in Access. Of course, Access does not like either option (using the same field name twice, or using a period in a field name). So in either instance (Excel or CSV), the field names in the Import File cannot exactly match the Field Name in my pre-existing table, which then does not allow me to import the file, "as-is".

    Short of editing the Excel or Text File after it is exported from this other application, is there any way I can import this file into my existing Access table?
    This is a process that will be run quarterly, so I am trying to make it simple and automate as much as possible for the users.

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You don't have control over the headers but they are always the same with every export?

    Access Import/Export Wizard doesn't like the names so alternative method is opening CSV text file object in VBA and parsing each line of the text file object.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You don't have control over the headers but they are always the same with every export?
    Yes, that is constant.

    Access Import/Export Wizard doesn't like the names so alternative method is opening CSV text file object in VBA and parsing each line of the text file object.
    Yeah, I know there are various ways of doing it via VBA. I could even open the exported files via VBA and change the headers before importing. I was just wondering if there was an easy way to do it without any manual editing or using VBA. Preferably, I would like to keep it fairly simple using no more than Access Macros. I was wondering if there was a way to use CSV and have it ignore the first row (header row), and just import the the fields in the order they come in (which does match the order in the pre-existing Access table).

    For some reason, upper management here does not really like Access, and VBA in Access scares them even more. So I am trying to avoid using VBA in my Access, if possible.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just did a test with the Wizard. It converted the header names that contained a period by just dropping the period. So I used these names in table design and import from CSV.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I can totally work with that.

    Thanks June!

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

Similar Threads

  1. Importing data to a combo box field
    By humtake in forum Import/Export Data
    Replies: 3
    Last Post: 12-02-2016, 08:38 AM
  2. Replies: 13
    Last Post: 05-26-2015, 12:03 PM
  3. Replies: 3
    Last Post: 06-13-2014, 08:02 PM
  4. Replies: 5
    Last Post: 01-09-2012, 05:55 PM
  5. Skipping headers when Importing a text file.
    By bullwinkle55423 in forum Import/Export Data
    Replies: 1
    Last Post: 09-01-2010, 03:25 PM

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