How come my data fields shift to the left?

[ScottJefferys]

I’m having a weird issue and thought maybe someone else has experienced it before and have a solution. Here are my steps and the issue I’m having.

I’m extracting data out of our Computer Aided Dispatch SQL server. All fields and data are good to go.

I add X/Y event data from the previous extract to the map in ArcGIS Pro. All fields and data are correct. Table looks good and points are visible on map.

I publish the X/Y event data to a web layer on AGOL in My Content.

I add that feature layer to my map in AGOL

I reviewed the fields and data to discover that all of the data in the fields has shifted to the left by one field.

Small example:

Fields:                   Agency                 Event                    Case                                      Field 4

Data                       MCSO                   MC17123456      IR17000001

Data on AGOL    MC17123456      IR17000001         Data from field 4

The Agency data MCSO is missing.

[DanPatterson_Retired · Accepted solution]

did you save/convert the event layer to a featureclass first? There may be something in that field that isn't liked from the csv.

[ScottJefferys]

Hi Dan,

No sir I did not. I will give that a try next and let you know (hahah if I can get my PRO to load up, just sitting and spining right now). Thanks for the reply.

[ScottJefferys]

LOL, the hits keep on coming. So, I ran my SQL data extract and exported to EXCEL.

Used the Excel file to add XY events in ArcGIS Pro. No problems except EXCEL jacked up all of my date and time fields.

The good thing is all the attributes were properly aligned with appropriate fields.

Exported XY event layer to shapefile using Pro. Had good results with all data and fields still aligned properly.

Shared and published my shapefile to web layer in My Content on AGOL. This is where the problem still occurred where all of my attribute data has shifted to the left as mentioned in my initial post.

Apologize for not detailing some of the tools I'm using at first, but here they are:

Original Data Source: MS Sql Server 2012

GIS: ArcGIS Pro 1.4.1 with the most current updates. I have ArcMap 10.3.1 on standby in case this might be an issue with Pro when converting to Web Layer

MS Office Excel 2013 (64 bit)

Off to try something else now.

[JoeBorgione]

I've never worked with ArcGIS Pro, but does it provide an ODBC connection right to the SQL db?  That way you would avoid the Excel mojo.  It's not surprising to me it jacked your times and dates.  Excel works great as spreadsheet, but not so great as a database.

[ScottJefferys]

Hi Joe,

Yes, Pro does offer ODBC. I have connected to a couple of our SQL db's to see if I can bypass the Access extraction to Excel export. I also found out about the QUERY LAYER function. I think that will be my ticket to create a workflow within PRO so I can have all processes run within one tool.

My biggest issue now is working with dates and times. I just read up on how shapefiles, geodatabases, and SQL format and use date/time fields. A bit different in each one it appears. When I first pull the data all the dates and times are good to go.

Once I get it all into a shapefile it seems that some date fields get converted properly and others do not. Some of the date and time fields appear to get converted to date serial or time serial integers. I'm sure SQL has some conversion functions that I can use to fix those pesky date/time formats.

Thanks to you all for the support. I'm sure this will not be my last post before the project deadline of March 31st.

Take care all.

[ScottJefferys]

Hi Dan,

I converted the event layer to feature class and was still having the same issue. Actually the project i was using eventually stopped creating a web layer to publish on our AGOL.

I finally threw out that project and started all over.

Went through the entire process again and converted event layer to features as you suggested.

Then published point features to web layer.

SUCCESS!.

All data retained their proper location in correct fields. Nothing was shifted to the left.

One of the issues I think that kept it from working after I followed Dan's directions the first time was the fact my project might have somehow got corrupted because it basically stopped working properly for me.

KUDOS to Dan. Thank you for your assistance sir.

[ChrisDonohue__GISP]

Let me add someone who works in your field in case he has some ideas on why this is occurring:

jborgion 

Chris Donohue, GISP

[ScottJefferys]

Just an FYI gentlemen. I just finished going through my initial SQL data extract and made sure that field names were properly done with no spaces and I double checked all the data types to make sure everything was good to go there. I'm getting ready to run through the process again.Stay tuned and thanks for the assist!

[JoeBorgione]

Since Chris put me on the spot-  seems like that AGOL is using the first field as the oid.  ESRI isn't charging you extra for that are they?  All seriousness aside, I'd do as Dan suggests and create a feature class first, and put it up on AGOL.