Have you ever received a text file of points with the locations and other data that goes with those points? Say you receive a GPS file of some hydrants that list the location as lat/lon and data about the hydrants. But the file is in a text format instead of a shp or dxf format. You can use the “Multiple” command with the point command to bring the points in but what about all those attributes that go with those hydrants?
“SIMPLE” Open the file in Excel and use the data connect for an ODBC connection. Here is how you can do this.
We start off with a text file either a CSV (comma separated) or tab delimited plain text. Depending on how it’s formatted as to how you open it in Excel, for my example I have a CSV file like so.
Notice the first line has the definition of each column or the label for each and the text is separated by commas. If you do not have the labels or definitions in the file that’s fine you can add them after we open it in Excel. Start by opening up the file in Excel, open Excel and go to file open and browse to the file. Excel should open the file just fine.
If you do not have the first row with a label or the description of the column you can add insert a blank row and enter them now. The next thing we need to do is create a named table range of the data. We do this by selecting all the data we need then from the menu Insert > Name > Define.
A dialog will open allowing you to create a name for the range. Give the range a name, it will default to what value is in cell A1, I find that is not very descriptive and causes a few problems down the line.
A few hints on creating the named table range, the help in AutoCAD Map 3D says you can press Ctrl-A to select the data cells but I found this to be a problem at times. First you are selecting EVERYTHING even empty cells from empty columns and rows. If there is an empty cell in the first row you may not be able to select the X-Y columns when you connect to the data in Map. So select the left top cells then scroll to the bottom of you data and shift-select the bottom right cell you need. If there is more data in the file than what you need then just select the data you need.
Next save the Excel spreadsheet as a Microsoft Excel 97/2003 & 5 Workbook with the xls file extension and close it.
The next step is creating an ODBC Data Source for the Excel file we just saved. Depending on your operating system as to how to get to the ODBC Data Source Administrator but with most of them you can get to it from the Windows Control Panel > Administrator tools.
Create a new Data Source by clicking on the “Add..” button in the dialog.
Select the Microsoft Excel Driver (*.xls), then click on finish. Provide a name for the data source, select the correct version then select the workbook to use by click on the “Select Workbook” button and browse to the Excel file you saved in the first step.
OK out of the dialog boxes and close the Administrator tools and any other dialog boxes that may still be open.
Open up AutoCAD Map3D and use the data connect to open the data connect palette. Select the Add ODBC Connection provider, then click on the file button and select the DSN name you created. Then click the connect button.
The login dialog box will appear, unless you placed restriction on the Excel file just click on “Login” with a blank user’s name and password. Back in the Data Connect palette you should now have the Excel file listed with the named table range you created as the FDO Schema. You can now select the named columns to use for the X & Y (& Z) insertion for the points. You do this by clicking in the blank space under each column. (You can drag the X & Y columns to make them wider if you need to.
After you click under the X column a drop down list box appears to allow you to select the column to use for the X & Y coordinates.
Select the columns that contain the X and Y coordinates in your Excel file. Then click on the “Add to Map” button.
Now you should have some points in your drawing with all the attributes that is in the text file.
Now if you need to use the points in another map or drawing export the ODBC layer to an SDF format file and you can archive the original text and Excel file, you don’t need them any longer.