970-360-4065         Store Login

Conversion & Importation of Excel, CSV DWG, or DXF data

Summary

ESRI’s Shapefile format is a common method of transferring spatial data and attributes. One caveat on Shapefiles is that there is no information exported that directly indicates the style or color of the Shapefile’s graphical elements in GIS software. The style is generally decided and implemented internally to the GIS software and each individual shapefile/layer. ESRI’s Shapefile format by design has no method that transfers symbology information to a third-party application. Layer files are available, but these are designed more as internal to ESRI style transfers and may not translate outside of that ecosystem.

CSV Import into PointMan

What we are essentially doing is creating a Shapefile from a CSV file. This is a very common practice for GIS functionality and I will outline the steps using QGIS, but the same process can be done in ArcMap or any other GIS system in a similar manner.

Clean and check the CSV or Excel file, the example CSV file we received is as shown:

You will need to clean up the 2 header lines to a single line so that the GIS parser can understand and properly name the columns. This is what I made it look like:

Also, be sure to remove any extraneous data – comments, text, etc. that are not part of the data, as they may confuse the QGIS parser and give strange coordinates, attributes, or NULL values.

There is a system imposed maximum of 10 chars allowed for the individual header names, this is by design by ESRI, and there is no way to work around this. If you have more than 10 characters, it will be uniquely truncated.

Once this is cleaned up, it can then be directly imported into a GIS system, converted to a Shapefile format, and then it will be readily available for many 3rd party applications, including PointMan.

For QGIS, here is a link to the tutorial that walks through the steps:

 https://www.qgistutorials.com/en/docs/3/importing_spreadsheets_csv.html

The projection file (local site calibration) that is used for this specific dataset is also available. (PRJ file) In this case, the example CSV data is using a local coordinate system (this is the Grand Junction CDOT system), and we have developed the attached projection to be used for it. This is used to define the CRS (coordinate reference system) that the tutorial mentions. If you are using standard State Plane or Latitude Longitude coordinates in the CSV, then you must indicate that in the GIS import instead of using a specific .prj file. This PRJj file is for these specific coordinates and should not be used if you have coordinates in a defined State Plane or other geographic or projected systems.

DXF/DWG Import to PointMan

The process involves creating a Shapefile from an Autodesk CAD file. This is a very common format for CAD functionality. I will outline the steps using QGIS, but the same process can be done similarly in ArcMap or any other GIS system.

The link below outlines the steps in QGIS for importing a DWG or DXF file. Once that is completed then the steps to export that data layer to a shapefile is the same for CSV, Excel, or CAD files.

https://docs.qgis.org/3.10/en/docs/user_manual/managing_data_source/opening_data.html?highlight=dxf#importing-a-dxf-or-dwg-file

Once this is finished, you can then export this imported CSV, Excel data, or CAD data as a Shapefile (QGIS Save function, or Export). You will typically export this data as a standard projection or CRS instead of a locally defined one for easier use in 3rd party programs. The standard export CRS for this example area is the Colorado State Plane EPSG 2232. Re-Project and export the shapefile using your required projection. 

You will have between three and ten individual files that define the dataset for this Shapefile. Shapefiles comprise several files that all have the same prefix and between 3-10 suffixes. i.e., 220215_First.shp, 220215_First.dbf, 220215_First.prj etc.  Select all those component files and compress then to a ZIP file, this Zip file can then be imported to PointMan.

There is a workaround that requires an initial setup of the styling for the layer that can then be applied to any subsequent layers of the same type and attribute. This works for PointMan collected data as each point, line or polygon collected layer will have the same structure. This styling information is saved as an SLD file which is an OGC (open standards) styling format that PointMan supports.

 Attributes, Precision, and Pedigree Information in PointMan

PointMan can show the attributes that were imported with the CSV or Excel file. Any columns that exist in the CSV file will be exported as Shapefile attributes and then will be available to view in the PointMan Feature Dialog (Left). Notice these attributes will not be indicated as Precision and Pedigree information in the dialog on the bottom right. The reason for this is that Precision and Pedigree information is derived from the spatial data stream received in real-time from the positioning GNSS system. This is considered a special data stream as PointMan understands this stream to contain the spatial information, accuracy, and precision information. Even if the CSV attributes contain similar data, it is not recognized as the same information as it was not received in a recognized GNSS data stream. In any case, all the information is available, it is just indicated as point attributes.

 

Use Case

The example data shown comes from an automated pipeline inspection system developed by Condux that navigates itself along the inside of a pipeline or corridor down to several inches in diameter. The spatial coordinates are derived from an Inertial Navigation System as it traverses the inside of the pipe. This is used to show that PointMan can bring in a variety of spatial datasets, including the full set of attributes if some idea of the structure of the spatial coordinates is known and the datum and projection are available.