PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 10/10/2017 7:34 AM by  jamesraceson
Creating excel xlsx file instead of csv
 12 Replies
Sort:
You are not authorized to post a reply.
Author Messages
HDAustin13
IT programmer
Austin ISD
Basic Member
(17 points)
Basic Member
Posts:9


Send Message:

--
10/09/2017 10:46 AM
    Hello All,

    I have a flow that using SQL to produce an output to CSV file but the vendor keeps having an issue of opening it. In order to successfully open it, he has to rename the file extension from .csv to .xlsx each time. Does any know if IPA can produce an output excel file to .xlsx instead of .csv file? Can you please advise?

    Thanks, Han

    David Williams
    Private
    Private
    Veteran Member
    (3025 points)
    Veteran Member
    Posts:1019


    Send Message:

    --
    10/09/2017 11:03 AM
    If all the vendor is doing is to rename the file (and not having to open and save as), then just name the file with the different extension. I have a feeling they are opening and saving as Excel so it reformats correctly.
    David Williams
    Woozy
    Private
    Private
    Veteran Member
    (3472 points)
    Veteran Member
    Posts:702


    Send Message:

    --
    10/09/2017 11:07 AM
    Hi Han,


    The short answer is "no" . Writing a file to xlsx is far more complicated than just changing the extension and it is not really possible in IPA/PFI without extensive custom code.

    It's confusing to me that they are having an issue opening the file, because a csv file is just a text file with a comma (or some other) delimiter. If it is really a csv file, then you should be able to open it with any text editor. Can you open one of the files with notepad? If not, then there is something wrong with your file.

    When you say you're "using sql to produce an output to csv", do you mean that you are doing a SQL query in your flow, and then writing the data to a file using the FileAccess node in the flow?
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3472 points)
    Veteran Member
    Posts:702


    Send Message:

    --
    10/09/2017 11:20 AM
    Hi David,

    If the file is being written as a "true" csv file, even if it is named with an .xlsx extension they won't be able to open it in Excel, will they? Excel will be expecting a file with an xlsx extension to be in the xlsx format, which as csv isn't.

    Maybe I'm missing your point?
    Kelly
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3472 points)
    Veteran Member
    Posts:702


    Send Message:

    --
    10/09/2017 11:26 AM
    Ahhh - I think I misunderstood. The vendor really wants an Excel file rather than a csv file? I'm tracking now.

    Unfortunately, this is not an option with PFI/IPA. Technically it could be possible using a custom node written by developer who really understands the xlsx format. However, this is really not anything most companies would do.

    The csv format is pretty standard for transferring files. Any vendor worth their salt should be able to take a csv file and do whatever they need to do with it. Unfortunately, there are some vendors that will only accept Excel, and if that's the case here then you are (unfortunately) out of luck using PFI/IPA.

    Kelly
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    David Williams
    Private
    Private
    Veteran Member
    (3025 points)
    Veteran Member
    Posts:1019


    Send Message:

    --
    10/09/2017 11:28 AM
    No, just naming a CSV file as xlxs won’t resolve this. Han seemed to be saying that’s all the vendor was doing so I wondered.
    David Williams
    Terry P
    Private
    Private
    Veteran Member
    (609 points)
    Veteran Member
    Posts:233


    Send Message:

    --
    10/09/2017 11:42 AM
    Have you tried using a script like CHRON or PowerShell to open and run Excel? You could open the csv or txt file, then save. I haven't done it myself, but a quick Google search gives examples of this.
    HDAustin13
    IT programmer
    Austin ISD
    Basic Member
    (17 points)
    Basic Member
    Posts:9


    Send Message:

    --
    10/09/2017 11:54 AM

    Kelly, yes, I am doing s SQL query and writing the data to a file using the FileAccess node in the IPA flow.            
                                                                                                                                                                                   Thanks for your answers, Kelly, Terry, and David!
                                                                                                                                                                                        Regards, Han
    steve finger
    ICS - Senior Consultant
    Infor
    Advanced Member
    (88 points)
    Advanced Member
    Posts:30


    Send Message:

    --
    10/09/2017 2:24 PM
    i have "csv" tasked in my registry to open with excel. if i double click on a file named with a csv extension, it opens in excel
    jamesraceson
    Private
    Center for Disability Services
    Veteran Member
    (130 points)
    Veteran Member
    Posts:44


    Send Message:

    --
    10/10/2017 7:03 AM
    HDAustin13,

    We have a vendor who has the same exact requirements. We tried just renaming the file extension, but the vendor kept coming back saying it has to be in "Excel format". We write it out to just a .xls file versus .xlsx, but it will still work as an "Excel file". While the coding is not too bad, you can accomplish this using some Assign and File Access nodes. The basic idea is you have a blank file on the server with a .xls extension. You write into the header with xml resembling the Excel format, something similar to this:
    <?xml version="1.0" encoding="UTF-8"?><?mso-application progid="Excel.Sheet"?>
     <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:x="urn:schemas-microsoft-com:office:excel"
     xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
     xmlns:html="http://www.w3.org/TR/REC-html40">
     <Worksheet ss:Name="Your Worksheet Name Here">
    <Table>
    <Row>
    <Cell><Data ss:Type="String">SSN</Data></Cell>
    <Cell><Data ss:Type="String">FirstName</Data></Cell>
    <Cell><Data ss:Type="String">LastName</Data></Cell>
    <Cell><Data ss:Type="String">Date</Data></Cell>
    <Cell><Data ss:Type="String">HoursWorked</Data></Cell>
    </Row> 


    Then from here, you would loop through your data and append the xml for rows and cells with something like this
     <Cell><Data ss:Type=\"String\">" + strFirstName + "<\/Data><\/Cell>
    . You would need to put the
     <Row>
    in the beginning and end of each row, but you get the idea. The "strFirstName" is just a variable within the flow. Then, just append the results to your xls file on the server, and end with something like this
    </Table>
    </Worksheet>
    </Workbook> 
    to finish the xml properly. Yes, it does take some coding but like everyone else said, there isn't something built into PF for this. You could also find some examples of this type of xml/Excel formatting with some quick searches on the old interwebs. Hope this helps out as we had the same struggle with our vendors trying to make this work (why they couldn't just use csv like every other civilized vendor is a mystery to me)...
    jamesraceson
    Private
    Center for Disability Services
    Veteran Member
    (130 points)
    Veteran Member
    Posts:44


    Send Message:

    --
    10/10/2017 7:05 AM
    I just noticed through the cut/paste there are some missing quotes in the "Cell" example at the end of the string, but hopefully you get the idea.
    Dave Curtis
    HRIS Lead
    University of Maryland Medical System
    Veteran Member
    (292 points)
    Veteran Member
    Posts:102


    Send Message:

    --
    10/10/2017 7:25 AM
    We have two vendors that want Excel files as well (Enter annoyed - rolling eyes - look here). I started playing around with the XML like jamesraceson has mentioned (which is a great option really). However - at the time - I had limited time to get the setup done to transfer the file (I did not want to do it manually every day). So I "cheated"...
    I use LBI to create the Excel file, all formated etc. for the vedors needs. I then have a schedule in LBI that runs the file and places it on the LBI server. I then use an FTP node in IPA to pull the file from the LBI server and place it on the vendor server. This leverages the "power" of LBI/Crystal reports and IPA together.
    jamesraceson
    Private
    Center for Disability Services
    Veteran Member
    (130 points)
    Veteran Member
    Posts:44


    Send Message:

    --
    10/10/2017 7:34 AM
    Dave,

    Very clever, sir. I think I might try that with another process that I could be working on. Thank you for the idea; appreciated.
    You are not authorized to post a reply.