Exporting to Excel, sometimes result can't import into Access?

2186
8
07-26-2016 05:35 AM
cartographercartographer
New Contributor

Using the ArcGIS Desktop 10.4 Table to Excel Arctoolbox script in the Conversion Tools -> Excel I am sometimes able to make an Excel 97-2003 document that imports directly into Microsoft Access without any issues using the

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "Table", strFilename, True

method, but sometimes it fails, and I have to open the document in Excel and save as a document of 97-2003 format with my Excel 2013.

Why does it not always work?  Can null values in certain fields create a problem?

Tags (1)
0 Kudos
8 Replies
DanPatterson_Retired
MVP Emeritus

Table To Excel—Help | ArcGIS for Desktop

  • Table To Excel is able to convert only to Microsoft Excel 5.0/95 Workbook (.xls) format.

Help indicates the export format.

cartographercartographer
New Contributor

Thank you.  Now still trying to figure out how to import into Access 2013 without running into errors on occasion.  Could null values or longer than 8 character field names with underscores make a difference?

0 Kudos
DanPatterson_Retired
MVP Emeritus

nulls must be known and accounted for, keep the field names short and underscores are fine

cartographercartographer
New Contributor

That almost answers my question.  Does that mean Null values can make the import fail in Access?

And how short is short?

0 Kudos
DanPatterson_Retired
MVP Emeritus

Check Access requirements.  Moving to arcmap to excel to access is going to be a good path to introduce problems.  Why not find a format that arcmap and access can agree on without an intermediate excel stop ( ie like text or csv)

cartographercartographer
New Contributor

I see how I can manually export a Feature Attribute Table to CSV  (Going to the Table by right clicking it in the Table of Contents, and using the Settings icon to Export and change the file name to .csv after selecting Text), but I can't seem to find a Toolbox item to export a table to CSV?  Is there a way to do it without including Latitude and Longitude?

I would like to be able to incorporate it in a Modelbuilder routine.

Thank you.

0 Kudos
DanPatterson_Retired
MVP Emeritus

there is not tool, table to table allows export to dbase or geodatabase table.  I think you have exceeded the capacity of modelbuilder.  There are built-in python modules for csv and excel import and export.  The problem with those formats is ensuring data integrity, particularly with excel which allows anything to be entered anywhere 

XanderBakker
Esri Esteemed Contributor

Have you tried exporting the data to a personal geodatabase (this will yield an Access .mdb file).

Also when you export to Excel don't use the option "use field alias as column header" since this may produce invalid field names for the import to Access procedure.