Copying Data to/from a Spreadsheet

Copying data from a form to a spreadsheet

When copying data from a form, keep these facts in mind:

• Carriage returns and line feeds in a multi-line cell are removed when you copy a row to the clipboard. So, if you have multi-line cells, you must copy those cells separately or reconstruct them in the spreadsheet.
• If you plan to copy updated spreadsheet rows back into this application, see the next section.
• Numeric values represented by check boxes in grids are 0 (zero) when cleared and 1 (one) when selected. These values are transferred to and displayed in spreadsheets as 0 and 1, respectively.
• This procedure can copy only the records that have actually been retrieved. If you want to copy all the records from a collection, and the cap prevents some records from being displayed, you must change the cap so that all records are retrieved.

  See Retrieving Collectionsand About caps.

• Row labels are pasted in the spreadsheet as the first column. The labels are for reference only and should not be copied and pasted back into a grid. In the spreadsheet, you can delete the row-label column, unless you want to keep it for reference purposes.
• By default, the header row is not included as part of this procedure. If you want to copy the header row as well as the records, perform one of these actions:
  • Select View > User Preferences and then select Include Grid Column Headers Copying to Clipboard.
  • Use the To Excel menu option.

Pasting data from a spreadsheet into a form grid

When pasting data from a spreadsheet into a form grid, keep these facts in mind:

• The spreadsheet and the grid must follow the same data scheme. Some grids contain hidden columns. Columns in the spreadsheet must exactly match the visible columns in the grid. The first column in the spreadsheet must correspond to the first column in the grid; the second column in the spreadsheet must correspond to the second column in the grid; and so forth.

  Normally, if you originally copied from a grid into the spreadsheet, the data schemes are identical. However, in some cases, this is not true because the order of the grid may not match the order of the collection that was exported; in those cases, you must reorder the columns in the spreadsheet to match the grid order.

• Be careful, too, when ordering columns in the grid view. Because the paste order matches that of the spreadsheet, if you paste a value into a column that affects the value of a later column, then when the later column value is pasted, it might overwrite the desired (calculated) value with the value from the spreadsheet.
• Be careful when you select the data in the spreadsheet to copy. Do not select complete rows, because the null cells at the end of the rows are also selected and copied. This causes errors when you try to paste the rows into the grid. Select and copy only the cells that contain data.
• Numeric values represented by check boxes in grids are 0 (zero) when cleared and 1 (one) when selected. These values are transferred to and displayed in spreadsheets as 0 and 1, respectively. So, if you plan to paste content from a spreadsheet into a grid and a check box value is included, you must enter the correct values as 0s and 1s in your spreadsheet.
• You can insert rows from a spreadsheet as new rows in a grid, or you can overwrite existing rows in the grid with rows from the spreadsheet. The overwrite operation skips columns that are read-only, leaving the current values unmodified.

  This means you might have to take other steps to get the appropriate values into those read-only fields.

• The form might validate each cell when you paste rows into a grid. Invalid data in cells in the spreadsheet or empty cells that correspond to required fields in the grid might generate validation error messages. Be aware that, if you edit a cell in response to a validation message during the paste operation, the paste operation ends with the current record. Therefore, we recommend that you respond No to all prompts for validation during the paste operation, and then go back after the operation is complete, to edit these fields.
• The maximum number of rows you can paste into a form at one time depends on the memory resources of your computer. Pasted rows are held in memory until you save them. You can avoid out-of-memory conditions and related errors by dividing a large number of records into smaller batches and then pasting and saving each batch separately.
• To ignore the values in some cells of the spreadsheet and replace them with the default value in the application, type [null] in those cells of the spreadsheet before copying the rows into the grid. Do not leave the cells blank. Be sure to include the brackets and use lowercase.
• We do not recommend that you paste data back into form grids from spreadsheets that were created using the Actions > To Excel menu option. The To Excel option is intended for exporting data to other applications, and the output might include hidden columns. Use another copy option instead, if you plan to paste updated data back into the application.

Copy/Paste options

To copy from a grid to a spreadsheet, see these topics:

• Copying Entire Collections to a Spreadsheet
• Copying a Single Record to a Spreadsheet
• Copying a subset of records to a spreadsheet
• Copying a Single Column to a Spreadsheet
• Copying a subset of columns to a spreadsheet
• Copying Single Cells to a Spreadsheet
• Copying Blocks of Cells to a Spreadsheet

To copy from a spreadsheet into a form grid, see these topics:

• Pasting Entire Collections into a Grid from a Spreadsheet
• Pasting Selected Records (Rows) from a Spreadsheet
• Pasting the Contents of a Single Cell from a Spreadsheet