Importing Multiple Columns of XY Data from Excel File

[DanielGetz]

I have an excel file with about 200 rows. Each row has 6 columns. The first 3 columns in order are 'Distributor ID', 'Distributor Lat', and 'Distributor Long'. The next 3 columns are the same except that they're for the 'Customer ID', 'Customer Lat' and 'Customer Long'. Using the XY Table to point, I am able to access the Distributor Lat and Long, but the tool can't seem to find the Customer Lat and Long fields. Is there a tool that does this? Am I using the tool wrong? Do I just have to restructure my input table? Picture Included for reference.

[KellyKoenig]

The XY to Point tool is looking for columns with the field type of "double." It seems like your customer lat/longs aren't being read in by ArcGIS Pro as "double." Sometimes that happens if you have a value like "NULL" or "N/A" in your list of coordinates - to accomodate for the text value, ArcGIS will convert the entire column to text.

If you confirm that your customer lat/long fields don't have any text in them but you're still unable to see those fields in the XY to Point tool, you can save your excel sheet as a .csv file and create a schema file to go with it. In Notepad or whatever text editor you use, save a blank document with the filename "schema.ini" in the same location as your .csv file. Then in your text editor, you will need to specify a) the name of the .csv file in brackets, b) the format of the .csv file, and c) the field names and what type they should be. Like this: 

[YOUR CSV NAME HERE.csv]
Format=CSVDelimited

Col5=Customer_Latitude Double
Col6=Customer_Long Double

Of course you'll want to replace "YOUR CSV NAME HERE" with the name of your CSV file, and double check that the column names I used match your column names.

Back in Pro, use the .csv file as your input for the XY to Point tool. Your customer lat/long fields should show up now.

As for your question of "Am I using the right tool?" - if you're trying to make a layer for customer points and a layer for distributor points, then I would split your spreadsheet into two separate files and run the XY to Point tool on each file. You can add a unique ID field before you split the data so you can join them back together if needed.

If the distributor and customer that share a row are related and you'd like to see how they connect, you can use the XY to Line tool. That tool will ask you for four inputs instead of two - a lat and long for the start and ending of the line (in your case, the distributor lat/long and the customer lat/long).

I hope this helps! Let me know if I misunderstood your question.

[DanielGetz]

Wow thank you for such an in depth and well thought out response to my question Kelly Koenig! To give you more context, the punchline here is that I am planning on using the network analysis tools in pro to measure drive distance between a customer and a distributor. The distance between the two Lat/Long coordinates on the roads is used in other calculations outside of the GIS application. I have a hunch that splitting the files into two separate csv or excel files may be the way to go. 

[AnnetteHovorka]

I think you are right about splitting the table, however you may still need to convert the columns to read as double in order to get it to read.  The good news is splitting the information will allow you to display each set differently.  Could make some other analytic easier as well.

[KellyKoenig · Accepted solution]

Glad I could help! Sounds like splitting them into two files will work perfectly for the OD Cost Matrix tool. You can import one feature class as "origins" and the other as "destinations." If you map each layer's ID field to "Name" when you're importing them to the OD Cost Matrix, the lines that get created when you solve the layer will have a field that looks like "[Origin ID] - [Destination ID]." I use this tool a lot in Pro and then I export the solved lines to excel for further analysis, which sounds like what you're doing as well.

The OD Cost Matrix has an option that will output straight lines between each origin and destination, but the "cost" you set when configuring the matrix (ex. miles, minutes, etc.) will be calculated based on the driving distance along the network and not the straight line. That can be a bit confusing if its your first time using the tool - you can read more about it here.

I used to use the "Closest Facility" tool to find the distance between two sets of points, but so long as you don't need to create turn-by-turn directions, the OD Cost Matrix is a lot faster.

Best of luck!