• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1777
  • Last Modified:

data transfer from excel to AS400 text to Char format

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.
0
amarie
Asked:
amarie
  • 2
1 Solution
 
daveslaterCommented:
Hi
if you you are using a DDT for the data transfer check for this line in the  [Properties] section

AllowNumericsInChar=1

Dave
0
 
theo kouwenhovenCommented:
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.

Regards,
Murph
0
 
amarieAuthor Commented:
Wow.  Thank you soo much.  I thought for sure I did that but a new day and viola!  
0
 
amarieAuthor Commented:
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now