Home > IT Answers > AS/400 > CPYTOIMPF and EXCEL defaults
Sbarnes
5 pts.
0
Q:
CPYTOIMPF and EXCEL defaults
CPYTOIMPF, Microsoft Excel, CSV format, IFS
I have an issue when copying an AS400 file to the IFS. One field that is defined as 15 charachter and contains numeric data does not show up in .csv format correctly without reformatting the column to special. I also tried defining the field as numeric, and had the same result.

Example of data displayed: 8.1228E+13, where the value is displayed as 81228000000001.

Is there an option on the CPYTOIMPF command the resolve this? I also tried the CPYTOSTMF without any luck.

Thank you!
ASKED: Jan 6 2009  8:39 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Teandy
2905 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
This is a problem with Excel not CPYTOIMPF. If you look at the raw csv data with note pad or word pad, you will see the data formatted correctly.
Last Answered: Jan 15 2009  10:17 PM GMT by Teandy   2905 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Philpl1jb   24105 pts.  |   Jan 6 2009  9:49PM GMT

This isn’t exactly an AS/400 problem.
You’ve sent the correct value to excel but it’s decided to show it in this format.

In Excel click on the field and you will see the number in the formula pane.

Format the cell (or all the cells in the column) to number and set the column width wide enough so you will see the numbers.

Using HSSF an rpg program could create the spreadsheet and set the column width and I believe set the format but that’s a chunk of work and a chunk of processor time.

Phil

 

Cjdugas   45 pts.  |   Jan 16 2009  5:25PM GMT

You will need to create a cwbtfr.ini file. Refer to:

 <a href="http://www-01.ibm.com/support/docview.wss?uid=nas1352f81ff9d0d4c86862565c2007cae44" title="http://www-01.ibm.com/support/docview.wss?uid=nas1352f81ff9d0d4c86862565c2007cae44" target="_blank">http://www-01.ibm.com/support/docview.ws…</a>

Excel will need to be restarted before it takes effect.

 <a href="http://itknowledgeexchange.techtarget.com/itanswers/importing-an-excel-tab/" title="http://itknowledgeexchange.techtarget.com/itanswers/importing-an-excel-tab/" target="_blank">http://itknowledgeexchange.techtarget.co…</a>

See if that works.

Chris

 

Philpl1jb   24105 pts.  |   Jan 16 2009  6:18PM GMT

Cjdugas

This documentation and everything I can google on this cwbtfr.ini file refer to it’s use with Client Access.
Does it effect Excel outside that process .. ie: CPYTOIMPF.???

Phil

 

Cjdugas   45 pts.  |   Jan 20 2009  2:00PM GMT

After reviewing the original posting, I believe you are correct. The .ini file should have no effect on the opening of a .CSV in Excel

What the .ini did do for me was to prevent the automatic conversion of long numbers into scientific notation when using data transfers and ODBC queries. I was experiencing a number conversion before the value would hit the cell. No amount of cell manipulation would correct it. Installing the .ini and changing the value worked like a charm.

This .ini has not adversely affected any of my day-to-day 400-to-excel transfers. I recommend it.

 

Philpl1jb   24105 pts.  |   Jan 20 2009  2:14PM GMT

Cjdugas

Thanks, this is really good info.

Phil

 
0