Creating excel xlsx file instead of csv

Author
Messages
HDAustin13
Advanced Member
Posts: 31
Advanced Member
    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
    Veteran Member
    Posts: 1127
    Veteran Member
      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
      Veteran Member
      Posts: 709
      Veteran Member
        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
        Veteran Member
        Posts: 709
        Veteran Member
          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
          Veteran Member
          Posts: 709
          Veteran Member
            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
            Veteran Member
            Posts: 1127
            Veteran Member
              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
              Veteran Member
              Posts: 234
              Veteran Member
                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
                Advanced Member
                Posts: 31
                Advanced Member

                  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
                  Veteran Member
                  Posts: 47
                  Veteran Member
                    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
                    Veteran Member
                    Posts: 52
                    Veteran Member
                      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:
                      
                       
                       
                      
                      SSN
                      FirstName
                      LastName
                      Date
                      HoursWorked
                      

                      Then from here, you would loop through your data and append the xml for rows and cells with something like this
                       " + strFirstName + "<\/Data><\/Cell>
                      . You would need to put the
                       
                      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
                      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
                      Veteran Member
                      Posts: 52
                      Veteran Member
                        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
                        Veteran Member
                        Posts: 136
                        Veteran Member
                          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
                          Veteran Member
                          Posts: 52
                          Veteran Member
                            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.
                            ---