Numerical to Character format

[Namasiva]

Hi I hve created a program which will deliver the CSV file for the item planning data.

my item number is having 00 as prefix but when i open the csv file, i am geting the out put as below.

the item numbers showing without the two zeros as per the sample below


Item_Number
UOM
Order_Qty
Re_order_point
P/M
Safety_Stock
Mini_Order
Pur_Lead_Time

8802​

BG

40​

0​

p

0​

0​

0​

8803​

BG

40​

0​

p

0​

0​

0​

8804​

BG

40​

0​

p

0​

0​

0​

8808​

BG

0​

0​

p

0​

0​

0​

The item mumber should be display as 008802
008803
008804
008808
Please find below my codes

*********************************************************************************
/* added for desktop version */
{mfdtitle.i "2+ "}
/* end desktop add */

define variable line like pt_prod_line no-undo.
define variable line1 like pt_prod_line no-undo.
define variable part like pt_part no-undo.
define variable part1 like pt_part no-undo.
define variable type like pt_part_type no-undo.
define variable type1 like pt_part_type no-undo.
define variable buyer like pt_buyer no-undo.
define variable buyer1 like pt_buyer no-undo.
define variable vend like pt_vend no-undo.
define variable vend1 like pt_vend no-undo.
define variable pldesc like pl_desc no-undo.

DEFINE VARIABLE outfile AS CHARACTER NO-UNDO.
def stream x.
assign outfile = "" .
/*GUI preprocessor Frame A define */
&SCOPED-DEFINE PP_FRAME_NAME A
FORM

RECT-FRAME AT ROW 1.4 COLUMN 1.25
RECT-FRAME-LABEL AT ROW 1 COLUMN 3 NO-LABEL
SKIP(.1)
line colon 15
line1 label {t001.i} colon 49 skip
part colon 15
part1 label {t001.i} colon 49 skip
type colon 15
type1 label {t001.i} colon 49 skip
buyer colon 15
buyer1 label {t001.i} colon 49 skip
vend colon 15
vend1 label {t001.i} colon 49 skip
SKIP(.4)
WITH FRAME a SIDE-LABELS WIDTH 80 ATTR-SPACE.


/* added for desktop version */
setframelabels(FRAME a:HANDLE).
/* REPORT BLOCK */
{wbrp01.i}
repeat :
if part1 = hi_char then part1 = "".
if line1 = hi_char then line1 = "".
if type1 = hi_char then type1 = "".
if buyer1 = hi_char then buyer1 = "".
if vend1 = hi_char then vend1 = "".
if c-application-mode <> 'web' then
update line line1 part part1 type
type1 buyer buyer1 vend vend1 with frame a.
{wbrp06.i &command = update &fields = "line line1 part part1 type
type1 buyer buyer1 vend vend1" &frm = "a"}
if (c-application-mode <> 'web') or
(c-application-mode = 'web' and
(c-web-request begins 'data')) then do:
bcdparm = "".
{mfquoter.i line }
{mfquoter.i line1 }
{mfquoter.i part }
{mfquoter.i part1 }
{mfquoter.i type }
{mfquoter.i type1 }
{mfquoter.i buyer }
{mfquoter.i buyer1 }
{mfquoter.i vend }
{mfquoter.i vend1 }
if part1 = "" then part1 = hi_char.
if line1 = "" then line1 = hi_char.
if type1 = "" then type1 = hi_char.
if buyer1 = "" then buyer1 = hi_char.
if vend1 = "" then vend1 = hi_char.
end.

{gpselout.i &printType = "printer"
&printWidth = 352
&pagedFlag = " "
&stream = " "
&appendToFile = " "
&streamedOutdisplayToTerminal = " "
&withBatchOption = "yes"
&displayStatementType = 1
&withCancelMessage = "yes"
&pageBottomMargin = 4
&withEmail = "yes"
&withWinprint = "yes"
&defineVariables = "yes"}
{mfrpchk.i}
outfile = "/extdata/desktop/dtlive/Board/Item_Planning_Data.csv".
output stream x to value( outfile).

put stream x
"Item_Number" ","
"UOM" ","
"Order_Qty" ","
"Re_order_point" ","
"P/M" ","
"Safety_Stock" ","
"Mini_Order" ","
"Pur_Lead_Time" ","
skip.

for each pt_mstr where (pt_part >= part and pt_part <= part1)
and (pt_prod_line >= line and pt_prod_line <= line1)
and (pt_part_type >= type and pt_part_type <= type1)
and (pt_buyer >= buyer and pt_buyer <= buyer1)
and (pt_vend >= vend and pt_vend <= vend1)
no-lock use-index pt_prod_part break by pt_prod_line
by pt_part :


put stream x unformatted
string (pt_part) ","
string (pt_um) ","
string (pt_ord_qty) ","
string (pt_rop) ","
string (pt_pm_code) ","
string (pt_sfty_stk) ","
string (pt_ord_min) ","
string (pt_pur_lead)
skip.

end. /* FOR EACH pt_mstr */
{mfrtrail.i}
end. /* REPEAT */
output stream x close.
{wbrp04.i &frame-spec = a}
********************************************************************************************************

Please help me out to resolve this issue

 

[Kishor Chari]

Take out the string bit in put statement. Dont understand why u r converting all to string. Try Export delimiter "," .

 

[Namasiva]

Hi Chari

Thanks for your reply

i have modified as

********************************************
put stream x unformatted
(pt_part) ","
(pt_um) ","
(pt_ord_qty) ","
(pt_rop) ","
(pt_pm_code) ","
(pt_sfty_stk) ","
(pt_ord_min) ","
(pt_pur_lead)
skip.
*****************************************

but still i am getting the same out put. I am here with attaching the out put for your reference pls help me out.

 

[oldemanw]

Ah, this is probably silly Excel trying to interpret the partnumbers as real numbers.
When when importing from File -> Open, you can set the format of the column to "text". Otherwise I'd try to surround the item number with quotes in your output.

HTH,
Willem.

 

[leite1969]

Quando é gerado um arquivo CSV ou texto que será aberto pelo Excel, efetuando a conversão de TEXTO para COLUNAS, o Excel considera como padrão para a coluna o formato GERAL. Neste caso se o campo possui somente número é considerado como NUMÉRICO, onde 000880 passa a ser mostrado 880.

Para não acontecer esta situação, ao abrir o arquivo, será mostrado o ASSISTENA PARA CONVERSÃO DE TEXTO PARA COLUNA, conforme figura em anexo, na etapa 3, selecione a coluna que contem os dados com 0 a esquerda e seleciona a opção TEXTO em FORMATO DOS DADOS DA COLUNA.

Uma maneira de fazer automático, seria inserir CHR(160) no final do campo, exemplo: string (pt_part) chr(160) ",".

----------------------------------------------------------------------------------------
Traduzido pelo Google do Portugues x Ingles:

When you created a CSV or text that will be opened by Excel, making the conversion of text to columns, Excel considers a standard for the format column GENERAL. In this case if the field has only number is considered as number, 000,880 which will be shown 880.

To avoid this situation happen to open the file will be shown the Support to convert text to columns, as shown in the Annex, in step 3, select the column containing the data with 0 the left and select the option in TEXT FORMAT FOR DATA FROM.

One way of doing automatic, would add CHR (160) at the end of the field, example: string (pt_part) chr(160) "."


Att.


Eduardo Leite

 

[Namasiva]

Hi Leite,

Thanks for your help, its solved my purpose.

Thanks to all of you for helping me to resolve this issue.