[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


data transfer from excel to AS400 text to Char format

Posted on 2009-03-30
Medium Priority
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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
Windows 7 does not have the best desktop search built in. This is something Windows 7 users have struggled with. You type something in, and your search results don’t always match what you are looking for, or it doesn’t actually work at all. There ar…
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.

649 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