Creating excel xlsx file instead of csv

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.

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?

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

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

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.

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.

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

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

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: [code] SSN FirstName LastName Date HoursWorked [/code]

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

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.

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.

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.