AnsweredAssumed Answered

Converting an Excel Spreadsheet/Access Table to a File Geodatabase.

Question asked by 73Dale on Oct 31, 2014

Thanks for looking at my issue.  I want to convert an Excel Spreadsheet into a File Geodatabase.  This has to be done in VB.Net  code, yes the database conversion tool code is available, but it is in Python.  I thought I could find a simple routine/feature that would take care of it, I have went over ESRI's site and I have not found anything so far, that does not mean it is out there.

 

What I have done is convert the Excel file to an Access Table, then take that table and try to convert it into a File Geodatabase using the IFeatureDataConverter.  I can create the File Geodaabase and then get a dataset with the field names, but no data, so I am missing something, or not interpreting something correctly.

 

The base for my routine is the ConvertTable routine from this site, from what I have seen there are several samples out there built off the following code.

 

Sub ConvertTable()

 

'++ Convert an OLE DB table to a ArcSDE Geodatabase table

'++ Data source is an existing ODBC DSN connection to an MS Excel file

'++ OLE DB provider is MS OLE DB provider for ODBC Drivers

 

Dim InTable As String, OutTable As String

Dim ConStr As String, InProg_id As String, OutProg_id As String

 

ConStr = "Provider=MSDASQL.1;Data Source=Excel_wks"

 

'++ Set the In and Out WorkspaceFactory Prog_ids

InProg_id = "esriCore.OLEDBWorkspaceFactory.1"

OutProg_id = "esriCore.SDEWorkspaceFactory.1"

InTable = "codemog"

OutTable = "vtest.vtest.CODEMOG"

 

'++ Create the Source Workspace

Dim pWorkspaceIn As IWorkspaceName

Set pWorkspaceIn = New WorkspaceName

 

'++ Set the Source Workspace Properties

Dim pInPropset As IPropertySet

Set pInPropset = New PropertySet

pInPropset.SetProperty "CONNECTSTRING", ConStr

pWorkspaceIn.ConnectionProperties = pInPropset

pWorkspaceIn.WorkspaceFactoryProgID = InProg_id

 

'++ Assign input table name

Dim pInTableName As ITableName

Set pInTableName = New TableName

Dim pInDatasetName As IDatasetName

Set pInDatasetName = pInTableName

pInDatasetName.Name = InTable

Set pInDatasetName.WorkspaceName = pWorkspaceIn

 

'++ Create the Destination GDB workspace.

Dim pOutPropSet As IPropertySet

Set pOutPropSet = New PropertySet

Dim pWorkspaceOut As IWorkspaceName

Set pWorkspaceOut = New WorkspaceName

pWorkspaceOut.WorkspaceFactoryProgID = OutProg_id

 

With pOutPropSet

   .SetProperty "Server", "fabio"

   .SetProperty "Instance", "5152"

   .SetProperty "Database", "vtest"

   .SetProperty "user", "vtest"

   .SetProperty "password", "go"

   .SetProperty "version", "VTEST.EV1"

End With

 

pWorkspaceOut.ConnectionProperties = pOutPropSet

 

'++ Create the destination table object

Dim pOutDatasetName As IDatasetName

Dim pOutTableName As ITableName

Set pOutTableName = New TableName

Set pOutDatasetName = pOutTableName

Set pOutDatasetName.WorkspaceName = pWorkspaceOut

pOutDatasetName.Name = OutTable

 

'++ Open input table to get field definitions.

Dim pname As IName

Dim pTableIn As ITable

Set pname = pInTableName

Set pTableIn = pname.Open

 

'++ Validate the input table field names

Dim pInTableFields As IFields

Dim pOutTableFields As IFields

Dim pFieldCheck As IFieldChecker

Dim outEnumFieldError As esriCore.IEnumFieldError

 

Set pFieldCheck = New FieldChecker

Set pInTableFields = pTableIn.Fields

pFieldCheck.Validate pInTableFields, outEnumFieldError, pOutTableFields

 

'++ if there are no field errors reported, proceed to convert

If outEnumFieldError Is Nothing Then

   Dim pOleDBToGDB As IFeatureDataConverter

   Set pOleDBToGDB = New FeatureDataConverter

   pOleDBToGDB.ConvertTable pInDatasetName, Nothing, pOutDatasetName, pOutTableFields, "", 1000, 0

End If

 

End Sub

 

'++ To convert an OLE DB table to a Personal Geodatabase table

'++ Change the OutProg_id to "esriCore.AccessWorkspaceFactory.1"

'++ and set the destination workspace property "DATABASE" as follows

'++ pOutPropSet.SetProperty "DATABASE", "d:\data\us_states.mdb"

'++

'++ You may also wish to change the destination table name

 

I have changed the code to where the input is an .mdb and the output is a FileGDBWorkspaceFactory, when it goes through the pOleDBToGDB.ConvertTable portion I get a dataset with the column names from the .mdb, but no data.

 

This could be an issue, there are 123,000 rows in the .mdb table, not sure if that is an issue or not.

 

I would post my code but it is Friday night at 9:30 pm and I am still running it through my mind.

 

So basically is there a routine out there that just does a simple input file-output file-convert.

 

If not, what am I missing by using this routine (with some modifications) to convert an .mdb to a File Geodatabase.  I am just putting the Geodatabase in a folder on my C: drive.

 

Thanks for taking a look, Dale,

Outcomes