Data Mismatch Error CWBTF0005 When Uploading an Excel File

Data Transfer upload fails with CWBTF0005 - column data mismatch at (row x, column y). Data in each column must be of all the same type. Correct the problem and re-scan the data. This message may be received when using the Create AS/400 Database File wizard (available beginning in Client Access Express V4R4M0) or the Transfer Data to AS/400... Excel add-in (available beginning in Client Access Express V5R1M0) to create an IBM DB2 file from a Microsoft Excel spreadsheet. IBM i Access V6R1 may fail when clicking on Finish with CWBTF0105 - Data is stored in different formats on the system and the client. An error was detected while converting data from the system format to the client format.

Note: This document does not cover all scenarios that can produce CWBTF0005 and CWBTF0105 when working with Excel spreadsheets. It is possible that a FDF file with field names that do not match the PC file could also cause message CWBTF0005.

Microsoft Excel automatically stores numbers as numeric data (by default 'Format cells' will show the cell as General) unless the Text format has been applied to the empty cells before the numbers are entered.

Applying the Text format to cells that already contain numbers does not change the numbers into character data. The data transfer wizard scan function fails with error message CWBTF0005 when the first row of data in a particular column is numeric (defined as General under Format cells) and a record is encountered in the same column with character data. An example of this scenario is the data in the second column in the following example:
 

ALL SYSTEMS CORP	223440	4098	2898	$ 3,663.20
AIR HYDRO CO	11864A	20	18	$ 500.62
BOMOT CONTROLS CO	108820	507	477	$ 31,930.48

In the following example, the scan completes successfully. Data in the first record of the second column contains character data. The data transfer scan function will determine the column is a character field.
 

ALL SYSTEMS CORP	22344B	4098	2898	$ 3,663.20
AIR HYDRO CO	118642	20	18	$ 500.62
BOMOT CONTROLS CO	108820	507	477	$ 31,930.48

However, when an attempt is made to transfer the data to a DB2 file created by the wizard, the upload fails with error message CWBTF0005 - Data in this field (row 2, column 2) is incorrect or does not match the PC data type. Data transfer is detecting the data in row 2, column 2 is numeric data rather than character data. Excel may show the data in this cell as being General or Text.

A new option was added to iSeries Access V5R3 and later Data Transfer to allow numeric data in character columns to be converted to character data. The option is located on the Conversions tab under the File -> Properties drop down menu in the Data Transfer session or by clicking on the Properties button when using the Excel data transfer add-in. Selecting this option helps eliminate many CWBTF0005 and CWBTF0105 error messages when uploading Excel .xls spreadsheets.

Or, one of the following options can be used:

The Microsoft Excel README recommends doing one of the following to store numbers as character data:

o	To have Excel interpret numbers as text, first apply the Text format to empty cells, and then type the numeric data. The Text format can be applied by highlighting the desired column and using Format Cells to change the format from General to Text.
o	If numeric data has already been typed in the column, apply the Text format to the cells, click each cell, press F2, and press the Enter key to type the data again. Note: In MS Excel, a copy and paste of numeric data into a field formatted for text retains the numeric data type and results in CWBTF0005 when transferring using iSeries Access unless it is refreshed by pressing the PF2 key and then by pressing the Enter key.
o	Select a cell that contains numeric data, and add a single quote (') in front of the number (using the entry line at the top of the spreadsheet). This allows the numeric data in the cell to be specified as character data. The single quote is not shown as part of the data in the cell. As an alternative, concatenating a column of single quotes (') with a column of mixed numeric and character data will also work. Instead of adding the single quote to the beginning of each cell, a concatenate formula can be used to join a column that contains only a single quote in each row (column A, for example) with a column that contains a mixture of numeric data and character data (column B). The syntax would look something like =CONCATENATE(A1,B1). Then create a column (column C), put the CONCATENATE formula in the top cell, and do a fill to repeat the concatenate in every cell. A fill could also be used when creating the column of quotes.
o	Do the following: -- Save the file as Tab Delimited text. -- Exit Excel. -- Open Excel again. -- Open the .txt Tab Delimited file previously created. -- Select Delimited in the wizard that comes up. -- Select Tab on the next wizard screen. -- Set the data type appropriately for each column. This will open the file in Excel with the correct data types. The file can then be saved as a .xls file.