Using Tables with IPAC Services
Table upload allows you to upload a plain text file containing a list of source names or positions into many of IPAC's services, rather than entering them one by one into a web form. You must, however, upload a table that complies with a set of formatting rules.
This help page describes the acceptable formats and includes sample tables. We advise first-time readers to read the formatting rules and look at the sample tables before submitting their own tables.
Go Directly To A Section:
- Requirements for Supported Table Formats
- Using Sexagesimal or Galactic Coordinates
- Best Practices for Successful Table Upload
- Troubleshooting Tables
Requirements for Supported Table Formats
IPAC accepts four ASCII table formats:
- IPAC ASCII Column-Aligned
- Comma-Separated Values (CSV) or Comma-Delimited
- Tab-Delimited
- A Simple List of Objects (such as astronomical source names)
Each format and its respective requirements are detailed below.
1. IPAC ASCII Column-Aligned Format (Download example.)
Pros: All table formats work equally well with IPAC services, however, this table is very easy to read because it is column-aligned. This document also allows additional column attributes, such as units or data types.
Cons: The data must be properly aligned within each column boundary.
The IPAC ASCII Column-Aligned Format, also known as IPAC Table Format, requires the following structure:
- Rows starting with a vertical line ( | ) are table header lines and define the table column layout and metadata. The first of these (the only one required) must contain the names of each column (e.g., ra, dec, flux).
- The columns in the header row are separated by a vertical line ( | ) to show the boundaries of each column. There is also a vertical line at the beginning and end of the header row.
- There must be at least one row of data in the row directly following the header row, and the data should align with the column names
- The number of cells containing data is equal to or less than the number of columns
- The data columns are lined up because they are column-aligned, which also makes the data more readable.
- Data must fit within the boundaries of each column and not extend beyond the vertical header lines.
Example of a basic IPAC ASCII Column-Aligned Format file:
| object | ra | dec |
M56 289.147941100 30.184500500
ic4710 277.158208330 66.982277780
hoix 49.383333330 69.045833330
tol89 210.339833330 -33.063777780
ngc 4552188.915863750 12.556341390
M82 148.969687500 69.679383330
mrk33 158.132833330 54.401027780
ngc1097 41.579375000 -30.274888890
arp 22179.874583330 -19.297222220
M 65 169.733166670 13.092222220
In the above example, notice the vertical lines ( | ) between object, ra, and dec indicate the column boundaries. Additionally, the data are aligned to clearly identify the columns and do not span across multiple columns.
The IPAC ASCII Column-Aligned Format also has the added flexibility of allowing additional header rows so tables can have additional information such as comments, data types, and keywords. To learn more about this capability and how to use multiple header rows, read the Keyword and Comment Lines and Column Headers section of the DDGEN table documentation.
2. Comma-Separated Values (CSV) or Comma-Delimited Format (Download example.)
This is a format commonly exported from Microsoft Excel as a .csv file. In this format, each data cell is separated by a comma.
Pros: This is a compact format that has little risk of inserting non-printable characters (even tabs).
Cons: Files can appear unwieldy if commas are used within the data cells, which then requires that the entire is cell quoted (e.g., 1.23, "he said, ""No""",3.45 ).
Requirements for CSV tables include:
- The first line must contain the names of each column.
- If data within a cell includes a comma, the data must be enclosed within quotation marks (e.g., "M56,148").
- The columns in the header row are separated by commas ( , ).
- The data provided in the table are separated by commas ( , ).
- The number of cells containing data is equal to or less than the number of columns.
- Blank lines will be treated as blank data records, so omit them if possible.
Example of Comma-Separated Values (CSV) Format (also known as Comma Delimited):
object,ra,dec M56,289.147941100,30.184500500 ic4710,277.158208330,-66.982277780 hoix,149.383333330,69.045833330 tol89,210.339833330,-33.063777780 ngc 4552,188.915863750,12.556341390 M82,148.969687500,69.679383330 mrk33,158.132833330,54.401027780 ngc1097,41.579375000,-30.274888890 arp 22,179.874583330,-19.297222220 M 65,169.733166670,13.092222220
3. Tab-Delimited Format (Download example.)
This is a format commonly exported from Microsoft Excel as a .txt file by selecting Tab-Delimited when saving the file. In this format, each data cell is separated by a tab character.
Pros: From a processing standpoint, this is the most program-friendly format. It is very compact and easy to parse. From a user's perspective, it is a format that can be easily created with Microsoft Excel by saving the file as Text (Tab Delimited).
Cons: Because tab characters are interpreted differently by different systems, users must understand that tabs are used in IPAC's table formatting context as a means of separating data -- NOT for data alignment. Users must take special care to not use tabs within a data cell, which will create too many columns and cause a failed table upload. Also, use one tab separator per column space, even if the data don't align exactly on the screen.
Requirements for tab-delimited tables are virtually identical to those for comma-separated and include:
- The first line must contain the names of each column.
- The columns in the header row are separated by tabs.
- The data provided in the table are separated by tabs.
- The number of cells containing data is equal to or less than the number of columns.
- Blank lines will be treated as blank data records, so omit them if possible.
Example of Tab-Delimited Format:
object ra dec M56 289.147941100 30.184500500 ic4710 277.158208330 -66.982277780 hoix 149.383333330 69.045833330 tol89 210.339833330 -33.063777780 ngc 4552 188.915863750 12.556341390 M82 148.969687500 69.679383330 mrk33 158.132833330 54.401027780 ngc1097 41.579375000 -30.274888890 arp 22 179.874583330 -19.297222220 M 65 169.733166670 13.092222220
In the above example, the columns are lined up using tab characters between each cell. Note the columns may not line up exactly, even if tabs are applied consistently (as in row #5). Resist the temptation to add additional tabs in order to make the data align correctly; this will only create ambiguity with your data, and your table upload will fail.
4. Simple List of Object Names / Coordinates with No Header (Download example.)
Pros: There are no complex formatting rules to remember.
Cons: This format requires name resolution using external name databases if data provided are object names. Each lookup query takes a few seconds per object, so processing large lists may be slow. Coordinates are transformed to RA and Dec in decimal degrees
Requirements for this format are minimal:
- Each line has one item.
- The entire line is treated as an object name or coordinate string.
Example:
M56 ic4710 hoix 3 23 45.6 -12 34 56.78 ngc 4552 M82 3h 22m 15.6s -13d 52m 11.17s ngc1097 331.2 -6.94 ga M 65
Using Sexagesimal or Galactic Coordinates
IPAC table processing includes a fairly careful analysis of the input to determine location on the sky. Coordinates can be entered as decimal degrees or sexagesimal; in Galactic, Equatorial or Ecliptic (with/or without Equinox); as multiple columns or a single column coordinate string; or (if a single string) as an object name to be resolved via astronomical name resolvers.
The first step in the analysis is to identify the best column(s) to use as coordinates. If any of the column pairs in the table below are given, they will be interpreted as shown in the Result column.
Note: This list, which is extensible, is based on data formats used by IPAC's data providers. In all examples, input column names are not case-sensitive.
| Right Ascension | Declination | Result |
| ra | dec | Equatorial * |
| cra | cdec | Equatorial * |
| ra2000 | dec2000 | Equatorial J2000 |
| ra2000 | de2000 | Equatorial J2000 |
| _raj2000 | _dej2000 | Equatorial J2000 |
| raj2000 | dej2000 | Equatorial J2000 |
| raj2000 | decj2000 | Equatorial J2000 |
| ra1950 | dec1950 | Equatorial B1950 |
| ra1950 | de1950 | Equatorial B1950 |
| rab1950 | deb1950 | Equatorial B1950 |
| rab1950 | decb1950 | Equatorial B1950 |
| elon | elat | Ecliptic J2000 |
| elon2000 | elat2000 | Ecliptic J2000 |
| elon1950 | elat1950 | Ecliptic B1950 |
| glon | glat | Galactic |
| l | b | Galactic |
| lon | lat ** | |
| starlon | starlat ** |
* The first two examples default to J2000, unless an Equinox column is included.
* * The last two default to Equatorial J2000 unless extra Equinox ("equinox" or "epoch") and Coordinate-system ("coord sys" or "sys" or "csys") columns are explicitly given.
If no complete set of coordinate columns is found, the table is checked for a variety of possible columns that might contain coordinate strings or object names: "object," "source," "objname," "objstr," "locstr," "location," "star," "galaxy," or "name."
The next step is to parse the coordinates or look up the object by name. Again, the processing allows for a fairly wide variety of inputs. Object name lookup includes checking with NED, SIMBAD, and other, more specialized, lists.
Coordinates can be decimal degrees or sexagesimal in various forms:
| Longitude | Latitude |
| 3h23m45.6s | -37d15m41s |
| 3 23 45.6 | -37 15 41 |
| 3h 23m 45.6s | -37d 15m 41s |
| 3 23' 45.6" | -37 15' 41" |
| 50d 56m 24s | -37d 15m 41s |
| 50.94000 | -37.2614 |
The final step in the processing is to output a column-aligned ASCII table with the same information content as the user's input, plus, if not already included, columns named '"ra" and "dec" containing the right ascension and declination in decimal degrees J2000. Further IPAC processing is based on these values.
Best Practices for Successful Table Upload
Each table format has its own set of rules, so take the time to learn the respective requirements for each when you intend to use them. Here are some general best practices for all IPAC tables:
- Run your table through the Table Reformat and Validation service to identify possible errors in your table, especially if you intend to upload to Gator.
- Columns should not contain "non-printable" ASCII characters. Printable characters are things like "a" and "?." Non-printable characters are sometimes inserted by text editing programs for their own purposes, and include symbols like ESCAPE ( ^[ ), FORMFEED ( ^L ) and DELETE. TAB characters are special in several ways and need to be treated differently in different contexts, as described below.
- Be very careful when using tabs. A tab ( ^I ) is a single character used in most editors to define a display action ("jump to the next tab stop"). In aligned data, such as those in an IPAC ASCII Column-Aligned format, tabs can make the data look aligned (in your text editor) when in fact the characters don't line up and are therefore illegal. In tab-delimited tables, a single tab character is the separator between columns, and in this context any extra tabs will inflate the column count. It is best to remove all tabs from formats other than tab-delimited and to make sure there are no extra (non-column-separator) tabs there.
- Don't mix delimiters , such as using vertical bars ( | ) between column names and tabs between data line values, or using a mixture of tabs and commas ( , ).
- Use an ASCII text editing program, such as Notepad in Windows or TextEdit on Apple computers, which prevents extraneous (and problematic) characters from being inserted. Unix/Linux users should be aware that the vi program generally shows all odd characters as control codes except for tab, which it will often quietly insert to "save space" at the beginning of lines with multiple blanks.
- Save the file name with the .tbl extension, if possible.
- Do not provide comment text (or anything else) at the top of the table unless you are using IPAC ASCII Column-Aligned Format.
- The number of cells containing data must not exceed the number of column names. However, the number of cells can be fewer than the columns of data (the line will be padded with trailing blank cells).
- We accept a broad range of formats and coordinate systems, such as sexagesimal and Galactic. For IPAC internal use, we will convert the data to RA and Dec decimal degrees. For more information, see Sexagesimal and Galactic Coordinates.
Troubleshooting Tables
Error Message: Could not recognize this table format.
Reason: The table is in a binary file format, such as .doc, .rtf or .xls.
In order for a table to be read by our services, it must be in a plain text ASCII file. If you are using Microsoft Word, be aware documents may appear to be in plain text, but can still contain hidden formatting if saved in any file format other than plain text (.txt).
Solution: For best results, create the file in a plain text editor such as Notepad in Windows or TextEdit in Mac. If your data is in a Microsoft Excel spreadsheet, you can save the file as a .csv file.
Error Message: Records in the table do not contain uniform number of columns.
Reason: Too many data columns, too few column names.
If you are using IPAC ASCII Column-Aligned, or tab- or comma-delimited formats, the number of cells containing data must not exceed the number of column names.
In the following example, there are three named columns: A, B, and C. However, there is a row with four cells of data:
A,B,C 5,2, 7,6,9,11
In the above example, the "11" is orphan data because it is not aligned with column A, B, or C.
Solution: Add additional column names in the header row to identify each data cell.
Error Message: We cannot identify columns to use as coordinates or a column to use as object names/locations.
Two common error conditions may result in this error message:
Reason #1: The column names do not sufficiently describe an object name, or the locations on the sky using ra, dec, glon, glat, etc.
Solution: Clean up the column names so they are recognizable by our services. See Sexagesimal and Galactic Coordinates for more information.
Example:
| jcgRA | jcgDec | size | 150.3814 2.3606 60.0 150.2794 2.1560 60.0 149.8873 2.0789 60.0 150.2323 1.9599 60.0 150.5407 2.5196 60.0 149.9343 2.4426 60.0
In the above example, there is extraneous text appended to RA and Dec, rendering the column name unrecognizable.
Reason #2: The table is using IPAC ASCII Column-Aligned format, and the columns are misaligned.
Solution: Make sure the table data are contained within the boundaries set by the vertical lines in the column header rows.
In the following examples, a table with misaligned data is reformatted so it will successfully upload:
Misaligned data columns:
| ra | dec | name| 12 24 36 45 29 27 m1 6 45 22 5 12 54 m2
Reformatted data columns:
| ra_user | dec_user | name | ra | dec | | | | | | | 12 24 3 36 45 29 27 m1 186.0125000 +36.7580556 6 45 2 22 5 12 54 m2 101.2583333 +22.0866667
In the above example, the data within the reformatted table fall within the vertical lines that denote the column boundaries. This removes all ambiguity during table processing, so the table can be interpreted.
Error Message: We were unable to parse this file.
Two common conditions may result in this error message.
Reason #1: Space is used as data delimeter, but also exists within the data, making it uninterpretable.
Solution: If you are using the tab-delimited format, carefully check your data for extra tabs that the system is interpreting as new columns. Be aware that tabs are not visually obvious when viewed on screen or in print; it's best to use a text editor that will allow you to view the file's underlying formatting. (See Best Practices For Successful Table Upload)
Reason: #2 Table contains non-printable ASCII characters.
A non-printable ASCII character in a table, such as ^[ (escape) or ^\ (file separator), can create problems during the upload process. Tabs are one of the most common causes of failed uploads because they are considered characters, even if they don't appear on print-outs, or even on-screen. When used in a table, the tab character is translated into its ASCII counterpart ( ^I ), which is not interpretable during table upload.
Solution: Remove the characters from the file. Cleaning up tabs is done differently, based on your operating system and text editor:
- In Microsoft Word on Windows machines, view all tabs in a document by toggling on formatting marks (click the toolbar button that looks like a paragraph symbol, similar to a pi sign). Remove the arrows embedded within the table, and then save the file as "text only." Here is a screenshot example:
- On Max OSX, Linux and Sun OS, use the the Unix command cat to view a file's non-printable characters:
cat -tev filename
This will bring your file to the screen with all hidden characters, such as tabs ( ^I ) and end of lines ( $ ) indicated. Tabs may seem like spaces and can easily be found using this command, and fixed. Additional blank space at the end of each line causes parser errors; if the end of line ( $ ) characters do not line up, then delete the blank space until they do.
Here are two examples:
Example 1: cat -tev ipac_good_table.tbl
| lon | lat|$ | double| double|$ 237.140482 -28.797019 $ 237.3 -28.0 $
Example 2: cat -tev tab_delim_table.txt
object^Ira^Idec$ M56^I289.147941100^I30.184500500 $ ic4710^I277.158208330^I-66.982277780 $ hoix^I149.383333330^I69.045833330 $
