ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Excel question - treat "MM/YY" as a string, not a date to be interpreted

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel question - treat "MM/YY" as a string, not a date to be interpreted

    I have a program that populates a PF, and then this file is downloaded as a CSV to a Windows PC using Client Access. The CSV file is then opened in Excel and saved as an Excel 97 file. This is because the credit card printing software we use needs it in Excel in order to import it properly. Credit cards are then printed from that data.

    One problem I'm having is with a field that contains the credit card expiration date which is just a string in the format MM/YY. So for example, the expiration date might be 1/18 and this is what we want printed on the card. But when the file is opened in Excel, it interprets the string "1/18" as a date and assumes that it's MM/DD and then it adds on the current year, so it thinks that I mean 1/18/2016. So before importing into the credit card printing software, I have to change that field to ="1/18" to force Excel to treat it as a string and not a date.

    There must be a better way... any ideas?

  • #2
    I have run into similar issues in the past, can't remember how I handled them but it appears if you can add a space before the dates in the csv excel will see that as a text string and leave it alone.

    Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date? I'm trying to write a .csv file from my application and one of ...

    Comment


    • #3
      Looks like this issue has been discussed at length, as in the link you provided above, and I'm not the first to be annoyed by this Excel behavior!

      Hmmm... the field in the PF is 5A, just enough for "12/31" for example, so that leaves no space for a leading space, or quotes, or all the other suggestions on that thread. But we can change the PF and give it a try. I wish Excel had a global preference that could be set to not try to be smarter than the data... don't try to interpret it. That PC only controls the credit card and envelope printers, so that would be no problem to set.

      Thanks.

      Comment


      • #4
        There also many ways to generate a csv. If you are doing a straight copy command then yes the file will have to change but otherwise you could write directly to the IFS and add in your space.

        Could also generate an excel directly from RPG and then you can do whatever you want.

        Comment


        • #5
          Microsofts handling of CSV files has always been flakey and annoying. When I've had difficulty in the past, I've saved the data as a plain text file with fixed length fields and imported into excel that way. It gives you full control on how excel will interpret each column that's imported.

          Comment


          • #6
            Follow-up: Some other changes were made to the file in question, so I took the opportunity to also change the length of this field from 5A to 6A. Then I changed the program to add a space before the month and year, " 12/31" for example. With that leading space, Excel now opens the CSV and leaves the string as 12/31 instead of converting it to a date. Just FYI.

            Comment

            Working...
            X