Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert multiple excel tabs from the same excel to individual file with no .xlsx extension

kmoon
8 - Asteroid

I am writing data from a database to a output file in .xlsx format. The data is run each day and gets added to individual tab with date name (<Filename>.<Today's Date>). For this I have used formula tool and inserted DateTimeFormat(DateTimeToday(),"%Y%m%d"). This is saved under the .xlsx file. So if everyday file is run, the tab will show multiple sheets with different dates.

However, I want to convert this individual sheet to a separate file that would have no .xlsx format.

Is there any way to do it?

It is not mandatory to have the output file only in .xlsx format. I need the file name as <Filename>.<Today's Date>.

 

Your response would be appreciated!

 

Thanks.

 

 

18 REPLIES 18
JohnJPS
15 - Aurora

For Excel files, you can try checking "Skip Field Names" in the config panel.

 

Hope that helps!

 - John

 

 

kmoon
8 - Asteroid

Hi @JohnJPS ,

 

With respect to your solution for extension file with .xlsx, I want to upload the day to day file to a server. Right now the file name stands at <File Name>.<Today's Date>.

 

Right now I am putting today's run time file to the server manually (in the attached workflow) by placing the <File Name>.<Today's Date> manually at the file location from Text Input tool. However I want to make it dynamic so that the day I would run the file, the file extension would be automatically there. Please refer to only "Upload file from disk" container only. As I am not using the other one.

 

Could you help me with this? If there's other alternate ways, do let me know.

 

Thanks. 

JohnJPS
15 - Aurora

DateTimeNow() in the formula tool will supply you with the timestamp at that moment (during run-time).  So you could have a formula with [Filename]+"."+DateTimeFormat(DateTimeNow(),"....") , where "...." is whatever format you want that portion of the filename to have (e.g. %y%M%d or some such).

kmoon
8 - Asteroid

Hi @JohnJPS 

 

I have done it the same way locally and it works. But the point here is I want to upload the file to a different server with the server name/authentication. How would I upload it dynamically to the server? That's why I attached the previous workflow for your reference.

Is there something I could use in the formula tool itself? So that it could directly upload the file to server.

 

Would appreciate your feedback.

 

Thanks. 

JohnJPS
15 - Aurora

Hi @kmoon,

 

I think that may depend more on the target than on Alteryx.  It looks like you're trying to POST the file to the server, but that would assume the server is willing to accept the raw, and furthermore will know what to do with it (e.g. where to put it on the server).

 

An alternative approach that you might consider is mapping a drive on the server such that Alteryx can see it using...

\\ServerName.YourCompany.Com\SharedPath\

... and then calculate the output name with that as the location followed by your constructed filename...

\\ServerName.YourCompany.Com\SharedPath\FileName

... and then use the regular Output tool to plop it there, rather than the Download Tool.

 

If you don't have direct access to the server, I'm assuming someone in your IT organization may be able to assist with the mapping the folder and setting up permissions, etc...?

 

Hope that helps!

John

 

kmoon
8 - Asteroid

Hi @JohnJPS ,

 

Can I type in the following in the formula tool? 

\\ServerName.YourCompany.Com\SharedPath\FileName

 

And by the constructed filename, did you mean Filename.Datetimeformat(Datetimenow(),'.....')?

 

Thanks!

JohnJPS
15 - Aurora

Sure, the formula expression would just be...

 

"\\ServerName.YourCompany.com\SharedPath\FileName." + DateTimeFormat(DateTimeNow(),"....")

 

... where "...," is the format you're after, and you literally just hard-code in the other stuff as a string.

 

Aside... if desired, those could also be variables:

 

"\\" + [ServerName] + "." + [YourCompany] + ".com\" + [SharedPath] + "\" + DateTimeFormat(DateTimeNow(),"....")

 

 ... assuming you had all those defined in the data stream.

kmoon
8 - Asteroid

HI @JohnJPS ,

 

With the previous email thread, you mentioned of mapping a drive to the server as shown below in the quotation mark. Is it possible to use in the formula tool? If I use in the formula tool, should I run the workflow in that particular server?

"

An alternative approach that you might consider is mapping a drive on the server such that Alteryx can see it using...

\\ServerName.YourCompany.Com\SharedPath\

" 

 

 

Thanks.

JohnJPS
15 - Aurora
(Responding via phone here)

If the path is shared, it means you should be able to access it from anywhere, including Alteryx. Someone from your IT group would know how to set up the share and make sure you have permissions to it. You should not need to be on the server itself if using this approach.
Labels