Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

data transfer from excel to AS400 text to Char format

Posted on 2009-03-30
Last Modified: 2013-12-06
Hello all.  I am trying to upload data (append) from excel to my AS400 db file (running V5R4) and I am getting an error on a cell that is formated (in excel 2007) as text but contains 0039.  No leading ' .  It is a part number but on the AS400 that field is charactor field not numeric.  This db file has 33 columns (fileds) and I downloaded the file first into excel so I could have a sample record to use.  I deleted all the records except my sample (Row2).  I then added in about 5000 part numbers (starting in row 3, column 2), and their price in column D.  All the other fields I copied to the other records from my example so all fields contain required data.  When I try to upload (append) it comes back with "CWBTF0005 Data in this filed (row 3 column 2) is incorrect or does not match the PC data type. Nothing at al from the excel sheet is actually uploading.  The column with the part number (column 2) is all formatted as text in excel and there is no ' in front of any of the numbers.  Help?  I notice when downloading into excel we seem to have options to control the format coming down but not going up to match?  Is there another setting? Any tips or hints would be greatly appreciated.  I was able to do this successfully with about 20 other files in January that ironically contain the same part number (0039) but for life of mean I can't think what I missed.
Question by:amarie
  • 2
LVL 14

Accepted Solution

daveslater earned 500 total points
ID: 24026429
if you you are using a DDT for the data transfer check for this line in the  [Properties] section


LVL 16

Expert Comment

by:theo kouwenhoven
ID: 24038836
Hi Dave,

Great I didn't know that :)

If you dont use a DDT, mark the column as type TEXT and update the wrong value F2 + enter, where required.


Author Closing Comment

ID: 31564510
Wow.  Thank you soo much.  I thought for sure I did that but a new day and viola!  

Author Comment

ID: 24040331
murphey2, sorry, new to responding here but I am confused by your suggestion.  If you mean in excel mark the column as text, I did that and it had no effect, same error.  That was my first try.  What do you mean by 'update the wrong value F2 + enter'?  I did try taking out that particular record/row/part number that started with 00 but it just errored on the next part '403'.  I have about 8884 rows/records in my list and a number of them start with 00 so I was thinking my next step would be to remove all of these parts and manually add them in on the 400 side but when it came back and errored on the 403 I gave that up.  Dave's answer worked. As I noted I thought I had already tried that but alas, this morning the sun is shining and all is well.  Thanks for the reposonse to my problem I look forward to your clairfication.  I would really like to get better at this function and learn how to use sql in excel.  It sure beats dfu.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question