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.
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.
Solved! Go to Solution.
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.
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.
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.
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.
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.
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!
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.