How to insert records into a shapefile from an excel sheet (c#.net)

SolidSmoke — Wed, 01 Sep 2010 05:08:16 GMT

Cud anyone advise me how to insert records into the attribute table of an existing shapefile from an excel sheet

Re: How to insert records into a shapefile from an excel sheet (c#.net)

BerendVeldkamp — Wed, 01 Sep 2010 13:07:34 GMT

Does the Excel sheet contain coordinates? In that case, you could probably add it as an event layer to ArcMap, and then copy+paste features into an existing shapefile. The number and types of attributes should match, I suppose, but surely there are ways around that.

Of course this can be programmed in C# as well.

Re: How to insert records into a shapefile from an excel sheet (c#.net)

JamesCrandall — Sun, 12 Dec 2021 00:50:25 GMT

Use ADO.NET and setup an OLEDB Connection to the Excel spreadsheet, fill a DataTable, populate the shapefile attributes by going row-by-row thru the DataTable.

Edit: here's a bit of code for setting up the ADO.NET DataTable, connecting to the spreadsheet and filling a ADO.NET DataSet. You can then begin to look at how to populate the shapefiles att's.

'Excel 2007
Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MyExcelSpreadsheet.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=Yes""")

Dim objCmdSelect As OleDbCommand = New OleDbCommand(expr, ExcelConnection)
Dim objAdapter1 As OleDbDataAdapter = New OleDbDataAdapter()
Dim objDT As DataTable
Dim ds As New DataSet()

Using objCmdSelect
   objCmdSelect.Connection = ExcelConnection
   objAdapter1.SelectCommand = objCmdSelect
   objAdapter1.Fill(ds, "dtExcelData")
End Using
        
objDT = ds.Tables(0)
        
Dim ct As Integer
ct = objDT.Rows.Count 'make sure the DataTable was filled with rows

Once you have your ADO.NET DataTable object filled, you can then set the pFeatureClass to your shapefile/layer and use the IFeatureBuffer to add the attributes. Something like this (just a snippet taken from one of my applications so this is untested):

Dim pFeatCur As IFeatureCursor
Dim pFeatBuf As IFeatureBuffer
pFeatBuf = pFeatureClass.CreateFeatureBuffer
pFeatCur = pFeatureClass.Insert(True)

Dim dtCol As DataColumn
Dim dtColname As String
Dim row As DataRow

For i = 0 To objDT.Rows.Count - 1
 row = objDT.Rows(i)

 '***add the rest of the attributes
                    For Each dtCol In objDT.Columns
                        dtColname = dtCol.ColumnName.ToString
                        Dim dtColType As String = dtCol.DataType.ToString

                        If Not IsDBNull(row.Item(dtColname)) Then
                            pFeatBuf.Value(pFeatBuf.Fields.FindField(dtColname)) = row.Item(dtColname)
                        Else
                            If dtCol.DataType.FullName.ToString = "System.Int32" Then
                                pFeatBuf.Value(pFeatBuf.Fields.FindField(dtColname)) = CInt(0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.String" Then
                                pFeatBuf.Value(pFeatBuf.Fields.FindField(dtColname)) = CStr("NA")
                            ElseIf dtCol.DataType.FullName.ToString = "System.Double" Then
                                pFeatBuf.Value(pFeatBuf.Fields.FindField(dtColname)) = CDbl(0.0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.Decimal" Then
                                pFeatBuf.Value(pFeatBuf.Fields.FindField(dtColname)) = CDec(0.0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.DateTime" Then
                                pFeatBuf.Value(pFeatBuf.Fields.FindField(dtColname)) = CDate(Now())
                            End If
                        End If
                    Next

                   pFeatCur.InsertFeature(pFeatBuf)

      Else
       End If
    Next
 pFeatCur.Flush()

Re: How to insert records into a shapefile from an excel sheet (c#.net)

KonradDebski — Sat, 20 Nov 2010 11:44:10 GMT

Maybe this example will help you a bit:
http://www.esri.com/news/arcuser/0104/files/excel.pdf

topic How to insert records into a shapefile from an excel sheet (c#.net) in ArcObjects SDK Questions

How to insert records into a shapefile from an excel sheet (c#.net)

Re: How to insert records into a shapefile from an excel sheet (c#.net)

Re: How to insert records into a shapefile from an excel sheet (c#.net)

Re: How to insert records into a shapefile from an excel sheet (c#.net)