Re: Getting a file to excel

[Gene Burns <burns.gene@xxxxxxxxx>]

Have you tried using cast?

Using the following two statements with a test file got the following 
results:

INSERT INTO TESTCHAR (CHAR1) SELECT cast(num1 as char) FROM testchar1 
INSERT INTO TESTCHAR (VCHAR1) SELECT cast(num1 as varchar(19)) FROM 
testchar1   
  
                           NUM1          
CHAR1                                    VCHAR1                    
  1,234,567,890,123,456                                                  
                      0                        1234567890123456          
                      
0                                                                          
1234567890123456             

TESTCHAR
NUM1 = 19,0
CHAR1 =  19
VARCHAR1 = Var(19)         

TESTCHAR1 is a view over TESTCHAR     

Gene Burns
                  
Alan Shore wrote:
> Thanks Jeff - unfortunately we have credit cards that use from 15 up to ALL
> 19 digits
> I had thought of Substring, but its an SQL procedure that produces this
> data, and I just cant seem to get my head around how to build a substring
> with a varying length, and how to calculate that varying length
>
> Alan
>
>
>                                                                            
>              jstevens@mizehous                                             
>              er.com                                                        
>              Sent by:                                                   To 
>              rpg400-l-bounces@         RPG programming on the AS400 /      
>              midrange.com              iSeries <rpg400-l@xxxxxxxxxxxx>     
>                                                                         cc 
>                                                                            
>              06/29/2006 09:56                                      Subject 
>              AM                        RE: Getting a file to excel         
>                                                                            
>                                                                            
>              Please respond to                                             
>               RPG programming                                              
>               on the AS400 /                                               
>                   iSeries                                                  
>              <rpg400-l@midrang                                             
>                   e.com>                                                   
>                                                                            
>                                                                            
>
>
>
>
> It's probably 19 digits because of the security code from the back panel.
>
> How about...
>
> CCNbr = %SUBST(CCNbr19:4:16);
>
> ?
> Jeff Stevens
> Mize, Houser & Co. P.A.
> 913 451 1882
> JStevens@xxxxxxxxxxxxxx
>
>
>
>
>
> Alan Shore <AlanShore@xxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 06/29/2006 08:53 AM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
> cc
>
> Subject
> RE: Getting a file to excel
>
>
>
>
>
>
>
> Thanks Mahesh, that is in fact what I am doing. But that displays the
> whole
> 19 digits, including leading zeroes. What I'd like to accomplish is
> display
> the number in total, with NO leading zeroes, for example:-
>
> The number 0001234567890123456, would be displayed as
>                                 1234567890123456
>
> NO leading zeroes.
> Am I nit-picking? Most definitely. But this way, I can stop my users
> complaining (OVER & OVER & OVER...) about the credit card number looking
> weird.
>
> Thanks
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
>
>
>
>              Mahesh Pawar
>              <pawarmahesh@yaho
>              o.com>                                                     To
>
>              Sent by:                  RPG programming on the AS400 /
>              rpg400-l-bounces@         iSeries <rpg400-l@xxxxxxxxxxxx>
>              midrange.com                                               cc
>
>
>                                                                    Subject
>
>              06/29/2006 09:18          RE: Getting a file to excel
>              AM
>
>
>              Please respond to
>               RPG programming
>               on the AS400 /
>                   iSeries
>              <rpg400-l@midrang
>                   e.com>
>
>
>
>
>
>
> Alan ,
>
>   Better you concat Apostrophe(') with card number number.
>
>   you can try to enter number in excel by preceeding Apostrophe( ' ). this
> wil help to store number more than 15 digits.
>
>   Regards
>   Mahesh Pawar
>
> Alan Shore <AlanShore@xxxxxxxx> wrote:
>
> Does the Char( ) work for numbers larger than 15 digits?
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
>
>
>
> keblake@highlight
> s-corp.com
> Sent by: To
> rpg400-l-bounces@ RPG programming on the AS400 /
> midrange.com iSeries
> cc
>
> 06/28/2006 04:55 Subject
> PM RE: Getting a file to excel
>
>
> Please respond to
> RPG programming
> on the AS400 /
> iSeries
> e.com>
>
>
>
>
>
>
> I use SQL to pull most of the data I take to Excel. Any numbers like UPC,
> ISBN, or credit card I Char() first, then import using the I-Series Access
> plug-ins for excel. Works fine. No leading zeroes and it doesn't turn the
> last few digits into zeroes.
>
>
> Kevin Blake
> Highlights For Children
> I.S. Applications Manager
> 614-487-2706
>
>
> Visit us on the Web @ http://www.highlights.com
>
>
>
>
> n.com>
> Sent by: To
> rpg400-l-bounces@
> midrange.com cc
>
> Subject
> 06/28/2006 04:48 RE: Getting a file to excel
> PM
>
>
> Please respond to
> RPG programming
> on the AS400 /
> iSeries
> e.com>
>
>
>
>
>
>
> Yep, it shows up wrong. That's because the default cell type is general
> which means that anything that looks like a number is treated that way
> (it also looks like Excel doesn't handle 18 digit numbers properly). We
> have the same problem with ISBNs (most have a leading zero and are all
> numbers). You have to specify the cell format before you enter data. I
> think there's a way to run the text import wizard that lets you specify
> the format of each column but it's not obvious. Stupid Excel.
>
> Matt
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> Sent: Wednesday, June 28, 2006 4:38 PM
> To: RPG programming on the AS400 / iSeries
> Subject: RE: Getting a file to excel
>
>
> That's what I thought.
> Try typing the following into an excel cell
> 123456789123456789
> and tell me what you get.
> The only way around this is by typing a single quote before you type
> 123456789123456789, making the cell a text format, but when we applied
> this
> idea to our problem, the result showed leading zeroes. Even though this
> was
> NOT incorrect, it looked weird for our users when they know that, the
> particular field was a credit card.
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
>
>
>
>
>
> n.com>
>
> Sent by:
> To
> rpg400-l-bounces@
>
> midrange.com
> cc
>
>
>
> Subject
> 06/28/2006 04:25 RE: Getting a file to excel
>
> PM
>
>
>
>
>
> Please respond to
>
> RPG programming
>
> on the AS400 /
>
> iSeries
>
>
> e.com>
>
>
>
>
>
>
>
>
>
> That only holds true for text files and there is a work around for it
> but I don't recall what it is (searching on WEB-400 might turn it up).
> For true Excel files, you can specify the data type for each cell and
> it's not a problem.
>
> Matt
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> Sent: Wednesday, June 28, 2006 4:02 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: Getting a file to excel
>
>
> Be careful with attempting to put data into an excel spreadsheet.
> One of the problems we have (and if anyone can post an answer to this,
> we
> would be very grateful), is that a credit card number is held in a 19
> digit
> numeric field. Once this field is placed into an excel spreadsheet,
> excel
> will only display the first 15 numbers, 0 filling the rest - NO MATTER
> what
> the rest is.
> I know this is a problem with excel, but, like I said, if anyone has an
> answer....
>
>
> Alan Shore
>
> NBTY, Inc
> (631) 244-2000 ext. 5019
> AShore@xxxxxxxx
>
>
>
>
> steema@diskhaven.
>
> com
>
> Sent by:
> To
> rpg400-l-bounces@ rpg400-l@xxxxxxxxxxxx
>
> midrange.com
> cc
> midrange-l@xxxxxxxxxxxx
>
>
> Subject
> 06/28/2006 09:16 Getting a file to excel
>
> AM
>
>
>
>
>
> Please respond to
>
> RPG programming
>
> on the AS400 /
>
> iSeries
>
>
> e.com>
>
>
>
>
>
>
>
>
>
> HI What are some of the methods in taking a file that was generated by
> an
> RPG program and getting it to the user in an Excel Worksheet file?
>
> It seems to be a straightforward file. Is there a preferred method. Any
> issues w/ v5.3 and the client access ?
>
> Thanks,
> Steve
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>
>
> ---------------------------------
> Yahoo! Music Unlimited - Access over 1 million songs.Try it free.
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
>