Input and output of Excel data

The following describes notes on input and output of Excel data.

Note

  • Do not use Excel data of a large size. A large amount of memory will be used and an error might occur. Data such as the following is regarded as data of a large size.

    • Data that contains many columns

    • Data that contains many rows

    • Data contained in a single cell is large

= Remarks =

Do not specify the same file as both an input file and output file. Make sure that different files are specified as input and output files.

(1) Data formats that can be input and output

The following data formats can be used to input data from and output data into cells in Excel.

  • Dates

  • Character strings

  • Numeric values

Figure 4.91 Data formats that can be input from and output to Excel files

(2) Read and write date format cells

When reading data in date format, the data is read in YYYY/MM/DD HH:MI:SS format regardless of the display format in Excel. For example, if a value of a date format cell that is displayed as "2013/1/22 10:47" in Excel is read, the value will be displayed as 2013/1/22 10:47 in an output destination CSV file.

To write data in date format, values in the following formats in the input source data can be output as the date type data.

  • YYYY/MM/DD

  • HH:MI:SS

  • YYYY/MM/DD HH:MI:SS

(3) Operation of Excel data from a different version

Part of the specifications of Excel differs depending on the version. For example, functionality of functions differs depending on extensions used. Use Excel data by considering the specification differences among versions.

The types of Excel files that DataMagic can handle are as follows:

  • For the input file, matching file, and virtual table, Excel 97-2003 (.xls) and Excel 2007 or later (.xlsx or .xlsm) can be used.

  • For the management Excel file and template file, use Excel 2007 or later (.xlsx or .xlsm).

  • For the output file, data is output as Excel 2007 or later (.xlsx or .xlsm).

(4) Input and output of data using multiple sheets

The following describes notes on inputting data from and outputting data to multiple sheets.

a) Sheet format

To input data from and output data to multiple sheets, the format of the target sheets must be the same. Specify settings of Excel's data format so that the same data format is used for all the sheets in a book file.

b) To output data to multiple sheets

To output data to multiple sheets, specify a field name for a sheet name. To apply this option, select Use field name for the target sheet on the Excel - Detailed Information screen.

At the top of the mapping screen for data processing information, the Sheet field is displayed. You do not need to set this field for Excel information.