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
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
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
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.

749 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