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