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
Comment Utility
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
Comment Utility
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

Comment Utility
Wow.  Thank you soo much.  I thought for sure I did that but a new day and viola!  

Author Comment

Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello I read in a discussion about a person who configured a very simple mirror RAID with two hard drives; the system and data were on the same partition. He asked how to repair the system as it was not booting up anymore. In his case running …
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now