|Opening an Excel (or text) file for editing |
This article is appropriate for recent and older versions of MapInfo Pro going as far back as version 8.0.
| ||Here is a quick tip to easily make a copy of an Excel table as you open it. This is handy if you are working with Excel data but you require the ability to edit the data. This will also work with CSV, delimited ASCII text files and Lotus 1-2-3 format data as well. |
Our Technical Support department is aware that some users open Excel data in one step and then do a separate Save Copy As operation to create a copy of the data for editing. The purpose of this article is to show how to do these things in one step.
New to opening Excel files?
If you are new to opening Excel spreadsheet files in MapInfo Pro, a useful tip is to make sure you are familiar with the data before you open it. As compared to opening a database format such as Microsoft Access, DBF, SQL lite or GeoPackage data files, an Excel file can have a varied structure. For example, you will need to know if the spreadsheet has column headings in it. (Hopefully it does!). You will also need to know what row the column headings appear in. It is easiest if they are in the first row, by the way.
Opening an Excel for editing.
Here is how to open a spreadsheet file and then create a copy all in the same operation.
Next, in the Files of type drop-down list, select Microsoft Excel (*.xls), Delimited ASCII (*.txt), Lotus 1-2-3 (*.wk1, *.wks, *.wk3, *.wk4), or Comma Delimited CSV (*.csv) to display the list of available files of the desired type.
- In the 32 bit versions of MapInfo Pro: From the File menu, click Open
- In the 64 bit versions of MapInfo Pro (the 64 bit versions have a ribbon interface): On the HOME tab, click the Open Table button (version 16.0 or later). Or click the Open Gallery button and choose Table.
The Excel Information dialog box appears. Fill this out to specify the range of data to be used in the MapInfo table.
- Select a file from the list and select the Create copy in MapInfo format for read/write to open it in native (.tab) format.
- Click Open to open the file.
Once you click OK, the next dialog allows you to specify the data types for the different columns in your spreadsheet. MapInfo Pro makes some default assumptions based on the data but you may find reasons to change these from time to time.
Once you click OK, the chosen spreadsheet file will be copied into a MapInfo table and opened. The MapInfo table will have the same name as the spreadsheet file.
What if the file is in a read only location?
If the spreadsheet file is in a location where you do not have the appropriate permissions to create and edit files, you will be prompted for a location to place the copy of the data. Navigate to a folder for which you have write permissions and click OK.
Note the title of the following dialog box:
Want something a bit more Advanced?
This article shows how to create a copy of an Excel file. Tony Maber, a MapInfo Pro expert from down under shares a more advanced technique on how to take advantage of ODBC data access to link to the Excel file. This allows for refreshing changes in the Excel data in a more efficient manner.
Article by Tom Probert, Editor of "The MapInfo Pro" journal
When not writing articles for "The MapInfo Pro" journal, Tom enjoys talking to MapInfo Pro users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.