Note: A option was added to iSeries Access V5R3 and newer versions to 'Allow numeric data in character columns to be converted to character data' when doing a Data Transfer upload. This option is available in the Data Transfer Properties. This will allow most transfer uploads to complete when message CWBTF0005 has been encountered.

The Data Transfer to IBM i upload function may encounter problems when the source file is an Excel spreadsheet with columns that have a mix of character and numeric values. A typical scenario has the equivalent column on the IBM System i system defined as a character type. Data Transfer will report an error (usually CWBTF0005) indicating that there is a type mismatch between the PC data and the IBM i system file. This happens because Excel has the data saved as numeric data rather than character data. Using the cell formatting function of Excel to format the cell as text has little or no effect.

One way to correct the problem is to add an apostrophe before the numeric data in the cell. For example, if the cell contains the value 12345, change the value to '12345 which saves the numeric data as text. This can be a tedious process if there are more than just a few cells to be modified. As a service to our users, we are providing the instructions and code to create a macro in Excel that allows the process to be automated. Once the macro has been created, the user can select a range of cells or multiple ranges, and then run the macro by selecting the Tools menu, clicking Macro, then clicking Macros to open the Macro dialog box. After creating the macro using the instructions below, the macro named ConvertSelectedNumericsToStrings can be selected and run. When the macro completes, it will inform the user how many values (cells) were modified.

The following macro was created using Excel 2002. The process may need to be modified slightly based on the version of Excel being used.

Step 1: Click on the Tools dropdown menu.

Step 2: Select Macro.

Step 3: Select Macros. This opens the Macro dialog box.

Step 4: In the text box titled 'Macro name:', type in the macro name ConvertSelectedNumericsToStrings, and then press the Create button. When the Create button is pressed, a new window will open which allows the user to write Visual Basic macros. The window will have an editor with the empty subroutine selected and ready for editing. Type or copy the macro below into the Visual Basic editor. Be sure not to duplicate the lines "Sub ConvertSelectedNumericsToStrings()" and "End Sub".

Sub ConvertSelectedNumericsToStrings()
   Dim count As Integer

   count = 0
   For Each c In Application.Selection
      If Application.WorksheetFunction.IsNumber(c.Value) Then
         c.Value = "'" & c.Value
         count = count + 1
      End If
   Next c
 
   If count > 0 Then
      MsgBox count & " values where converted from numeric to character.", _
vbOKOnly, "ConvertSelectedNumericsToStrings Macro Complete"
   Else
      MsgBox "There were no numbers to replace.", vbOKOnly, _
"ConvertSelectedNumericsToStrings Macro Complete"
   End If

End Sub


Step 5: The previous instructions can now be used to run the macro.

If you would like to save this macro outside of the spreadsheet so that it can be used in other spreadsheets more easily, click on the File menu in the Visual Basic editor, select Export File, and use the default name or give it a more meaningful name. To quickly import the macro into a different spreadsheet, use the Tools menu, Macro and Visual Basic Editor to open the editor, and select Import File from the File menu to add the Visual Basic module you saved earlier. All macros saved in that module are now available to you.

To use this macro, select the range of cells in the spreadsheet that are to be converted. Then, click on the Tools menu, and select Macro and Macros. Double-click on the ConvertSelectedNumericsToStrings macro, and it will perform the conversions for you.