Importing Points from CSV file to correct location

[seskinner]

Hello,

I have been sent an Excel file that has DMS coordinates that I'd like to import into my C3D file that is set to the GA83-WF coordinate system. I can import them, but they do not line up with an aerial photo that I have inserted to georeferenced coordinates. I can convert the DMS to Digital Degrees as a CSV file, but it goes to the wrong location as far as X,Y coords. Can someone show me what I may be doing wrong? 

Thank you.

[Pointdump]

Hi Scott,
Couple of things that will help. Your CSV file is decimal degrees, not DMS. And the longitudes should be negative:
32.634276,84.498108,0
Dave

[Pointdump · Accepted solution]

Here is how I set up the longitude-modified point file format:

[rl_jackson]

I totally missed the -# part. Just looked right past it. Good catch @Pointdump 

[seskinner]

Dave,

Thank you for the insight and answer. I appreciate you showing me that dialog box. I wasn't using the correct headers in the Point File Format dialogue box. The negative longitude wasn't helping either!

Is there a way to automatically convert them to negative from the original excel file, attached? I am using this formula to convert from DMS to decimal:

=TEXTBEFORE(A2, "°")+TEXTBEFORE(TEXTAFTER(A2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600

Thank you!

[Pointdump]

Scott,
=TEXTBEFORE(A2, "°")+TEXTBEFORE(TEXTAFTER(A2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600

That's clever! I wouldn't have thought of it. I'd use replace all to change °[sp] '[sp] "N etc to commas. Then concatenate divided values. Not nearly as elegant as your solution.
As for changing longs to negative, multiply by -1, copy, paste as value.
Dave

[Pointdump]

Scott,
Another option is to input your "Copy of Blasting on Bench 1 only.xlsx"(in Excel save as CSV) into QGIS and re-save as a shapefile. Drop into Civil 3D. No gymnastics and you get attributes as Object Data.
Dave

[seskinner]

Dave,

Thanks again for your help.

Credit for that formula goes to: Excel: Convert Degrees Minutes Seconds to Decimal Degrees (statology.org)

I searched for a while before I stumbled upon it.