Forcing data to stay left going to excel

1380 pts.
Tags:
AS400 RPGLE
InLot  Contains

123

ABC456

Eval outlot = inlot

transfer file to .csv

outlot

'         123'

'ABC456   '

Ugly.  How do I force it to all stay to the left.  TrimL, MoveL has no effect.  It is in the translation to .csv.

Thanks,

Nick

 

 



Software/Hardware used:
AS400 RPGLE .CSV

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to write the value of the 1st cell 123 with a quote mark ‘ in the first position with the data concatenated after that.
This will make excel think it is a alpha field and left justify it,

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • deepu9321
    Yupp, It looks ugly to have different formats in one column. I do face this problem regularly. But, We do manage it by adding ' as prefix if it needs to be a character. Of course its not the better choice. Pradeep.
    4,235 pointsBadges:
    report
  • NickHutcheson1
    OutLot is 10A. D Quote C '''' CVLCLOT = %trimL(quote + CVLCLOT) CVLCLOT = ''4 ' Results Lot # ' '4 ' '34 '34 '34 '34 '34 '34 '34 '34 '34 '34 '32 '32 '87 '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B '2E-B ' 'LA381 'LA381 It looks correct except for the leading quote. '4 is 4 in the file, '34 is 34 in the file '2E-B is 2E-B in the file. Is this acceptable to everyone? Is this what everyone is using? Nick
    1,380 pointsBadges:
    report
  • CharlieBrowne
    The question is not if it acceptable to us. The question should be "is it acceptable to your end user"
    41,380 pointsBadges:
    report
  • NickHutcheson1
    Ok. Let me try that again... It this acceptable to YOUR end users. Is this how all you do it for YOUR end users? Our end users know nothing different than the scattered ugly way of not doing anything.
    1,380 pointsBadges:
    report
  • CharlieBrowne
    Yes this approach has been acceptable to our end user.
    41,380 pointsBadges:
    report
  • CharlieBrowne
    BTW, When you open up the .csv file in EXCEL, the leading quote does not appear in the cell.Only the value displays in the cell.
    41,380 pointsBadges:
    report
  • NickHutcheson1
    Then something is different between here and there. What I posted is what I got. I saved even went back to the IFS, dragged the .CSV file to the desktop, opened it, saved it as an Excel workbook, closed and re-opened it from the desktop. The leading quote is still there. What do you open the .CSV file with and how?
    1,380 pointsBadges:
    report
  • NickHutcheson1
    I should mention that our excel version is 2003. I would guess this could make a difference. Nick
    1,380 pointsBadges:
    report
  • CharlieBrowne
    When I open with 2007 or 2010, I do not get the aprostrophe in the cell, but instead, I get a small triangle in the upper left corner identify this as a text field. If you go to a cell and enter '123 that is what you should get. Another way to do is is to just use import to bring in the file. Open excel Click option to import a text file Select a .csv that does not have the apostrophe in this field Click options to import as a Delimited file Select comma for the delimiter When it displays teh SFL with all your colums, Click on the one you need to adjust and click on the radio button to format it as text. Click finish and this would work. == This method is good because you do not have to do the code to put in the leading apostrophe, but it takes a few extra steps to get the file into excel. === We do use both methods here.
    41,380 pointsBadges:
    report
  • CharlieBrowne
    I'm not sure you have QUOTE defined properly here is how I define it. I use the name $Apos
     D $Apos           C                   CONST(X'7D')
    41,380 pointsBadges:
    report
  • philpl1jb
    What we have here is a failure to communicate... If you send '34 then that is what will be in the cell. When you look at the list you see 34 left justified But when the cellpointer is on the cell, the formula box shows '34 What's "in" the cell? '34 This would be on all versions of excel. What we added to the column was an nps, non-printing space but I don't remember the hex character. Using any one of these methods is necessary when there is a leading zero that shouldn't be dropped. Phil
    50,860 pointsBadges:
    report
  • TomLiotta
    It is in the translation to .csv. No, it's not in the .CSV. It's in Excel trying to convert a .CSV file into a meaningful Excel file.
    • .CSV file <> Excel file
    If you want Excel to know how it should present values in cells and you don't want stuff like quote marks messing up the values, then you'll have to create an Excel file rather than a .CSV file. Of course, creating an actual Excel file is not trivial; while generating .CSV files can be simple. But .CSV files have no way of knowing what application is going to be reading them, so there is no accommodation for stuff like "If Excel opens this file, then left-justify numeric values found in the third column. If DB2 opens this file, then use the first row as field names. If xxxxxx opens this file,... etc." There are ways of creating actual Excel files. They aren't anything like ways of creating simple .CSVs, but it can be done if that's what you want to do. And if it's not, then you're stuck with what a .CSV can do. Just be aware that [ .CSV file <> Excel file ]. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following