Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1372
Roger French	1315
mark.cook	1244

Creating excel xlsx file instead of csv

12 Replies

5 Subscribed to this topic

52 Subscribed to this forum

Sort:

Author

Messages

HDAustin13

Advanced Member

Posts: 31

10/9/2017 2:46 PM

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

10/9/2017 3:03 PM

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.

Woozy

Veteran Member

Posts: 709

10/9/2017 3:07 PM

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?

Woozy

Veteran Member

Posts: 709

10/9/2017 3:20 PM

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

Woozy

Veteran Member

Posts: 709

10/9/2017 3:26 PM

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

David Williams

Veteran Member

Posts: 1127

10/9/2017 3:28 PM

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.

Terry P

Veteran Member

Posts: 234

10/9/2017 3:42 PM

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

10/9/2017 3:54 PM

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

10/9/2017 6: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

Veteran Member

Posts: 52

10/10/2017 11: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:


 
 

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
        
        
            
        
        
            
        
    

						
					
					
						
							
								
									
										10/10/2017 11: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
    
    
        
            
            
            
        
    


    
    
    Veteran Member
    
    
    
    
        
            Posts: 136
        
        
            
        
        
            
        
    

						
					
					
						
							
								
									
										10/10/2017 11: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
    
    
        
            
            
            
        
    


    
    
    Veteran Member
    
    
    
    
        
            Posts: 52
        
        
            
        
        
            
        
    

						
					
					
						
							
								
									
										10/10/2017 11: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.



    
        Please login to post a reply.