Upload from Excel to AS/400
Posted by Derek O'Connor on May 02, 2001 11:08 AM
Our department uploads data from excel spreadsheets to AS/400 files. Some of the uploads have numeric data in cells that needs to be uploaded into a text field on the AS/400. The data is in the format 7.6. For this to work correctly the cell in the excel spreadsheet needs to be formatted as text. The problem is that even when we use format, text on the cell it seems not to recognize this as a text field. To get it to work what I had to do was first to type in text characters 'abc' into the cell then enter the numeric data (.095). Then when I upload it gets uploaded correctly. If I not do this it uploads 0 (which is the first digit before the decimal point.
Any ideas!!!!
Posted by Mark W. on May 02, 2001 1:42 PM
Derek, what's the nature of the upload file? Text
or binary? If text, is it fixed record length or
delimited? If delimited, what's the delimiter?
Posted by Kevin James on May 02, 2001 7:23 PM
Derek:
I have come to respect Mark W. as being well versed in his knowledge, so whatever he offers is well worth listening to.
That said, one option would be to export the file from Excel as a CSV file. Since that file type is nothing more than a text file delimited by whatever character you choose, all your fields will be in text format.
Another possibility: I have noted an interesting "feature" of Excel that does exactly what you've notice. To get around this, after have defined the column as text, if you copy the whole column and then Paste it to a blank column, I've seen this work.
Finally, if you are already editing each cell, you might be happy to know that just pressing F2 and Enter "sets" the cell correctly as defined. (I had the opposite problem with importing a text file, defining the column as numeric and yet it was obviously still text because my calculations weren't working.)
Kevin
Posted by Mark W. on May 02, 2001 8:37 PM
Kevin, if Derek comes back and says that their
upload facility does process an Excel binary
file then you've hit the "nail on the head"! Most
people don't realize that applying the Text format
after entry of a numeric value doesn't change
its data type. This is evidenced by using the
ISTEXT() function which can prove that the conver-
sion didn't occur as expected. The remedy as you
suggested is to effectively cause the data to be
re-entered. One way to "trigger" this event
(if all of Derek's values have a decimal point)
is to Replace all instances of the decimal point
(.) with a decimal point (.).
Posted by Derek O'Connor on May 03, 2001 8:20 AM
Kevin & Mark...Thanks for your input. The solution that I ended up using was from the data menu, 'Text to Columns' option. Using this to set the field to Text works. After that the upload worked fine.
.
Derek.
