topic Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten in Alteryx Designer Desktop Discussions

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

kmoon — Fri, 10 May 2019 21:23:16 GMT

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.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Sat, 11 May 2019 15:36:01 GMT

Hi @kmoon,

For most output file types, you can specify the filename as one of the fields, towards the bottom of the Config Panel of the Output Tool.

So, for instance, use the formula tool to calculate the desired filename, say, as [calculatedFilename], and then do...

You can do this for .xlsx, .csv, .flat, etc...

Hope that helps!

John

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Mon, 13 May 2019 13:20:22 GMT

Hi @JohnJPS,

I did it the similar way you mentioned.

For excel, if I save in .xlsx format, I would get everyday data in separate tab like <FileName><Todays Date>.

However, I would like to see each tab converting to a separate file so that they are visible from the folder itself and not by going to .xlsx and retrieving them.

Any thoughts?

Thanks.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Mon, 13 May 2019 14:21:57 GMT

To illustrate, I've attached an example that generates some random data and outputs it to both a single file with 31 tabs which is I believe what you're already doing, and also 31 individual files, which is I believe what you're looking to do. The two approaches are so similar that I'm not entirely sure what might be missing in what you've already tried, so hopefully this example will contain clues to get you pointed in the right direction for your actual data.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Mon, 13 May 2019 16:25:08 GMT

Hi @JohnJPS,

I appreciate your expedite feedback. The output file is shown as <RandDate.xlsx>. I want to get rid of the .xlsx extension and want to show the file name as <RandDate> only.

Any thoughts?

Thanks.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Mon, 13 May 2019 18:30:16 GMT

In the Formula tool, for the filename, I used:

"C:\dev\Alteryx\tmp\out\" + DateTimeFormat([randDate],"%Y%m%d") + ".xlsx|||" + DateTimeFormat([randDate],"%Y%m%d")

... to eliminate the .xlsx, just take it out (e.g. from the 3rd line):

"C:\dev\Alteryx\tmp\out\" + DateTimeFormat([randDate],"%Y%m%d") + "|||" + DateTimeFormat([randDate],"%Y%m%d")

... please note that the sheet name will still be named as a date... if you just want "Sheet1" there, make the corresponding modification:

"C:\dev\Alteryx\tmp\out\" + DateTimeFormat([randDate],"%Y%m%d") + "|||Sheet1"

... hopefully this gives you the insight to name your output file pretty much anything you want.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Mon, 13 May 2019 20:12:14 GMT

This worked for me. Removing the .xlsx did the trick. Now I don't see the .xlsx extension.

Appreciate it.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Mon, 20 May 2019 19:53:18 GMT

Hi @JohnJPS ,

With respect to what you mentioned before, could you also let me know how do I get rid of the header in final output?

Would appreciate your response.

Thanks.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Mon, 20 May 2019 19:58:25 GMT

Hi @kmoon,

Sure, for most output types, look in the Output tool's config panel for "First Row Contains Field Names"

Just un-check that to get rid of the headers in the output file.

Hope that helps!

- John

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Mon, 20 May 2019 20:48:25 GMT

Hi @JohnJPS

It works for .csv output file but not .xlsx file. The option is available only for .csv.

Curious if Alteryx could apply this for all the output file.

However, it solves my purpose.

Thanks.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Mon, 20 May 2019 20:55:29 GMT

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

Hope that helps!

- John

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Wed, 29 May 2019 14:49:28 GMT

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.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Wed, 29 May 2019 15:58:50 GMT

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).

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Thu, 30 May 2019 13:52:11 GMT

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.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Thu, 30 May 2019 14:46:43 GMT

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

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Fri, 31 May 2019 14:55:28 GMT

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!

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Fri, 31 May 2019 15:42:08 GMT

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.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

kmoon — Wed, 12 Jun 2019 16:20:02 GMT

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.

Re: Convert multiple excel tabs from the same excel to individual file with no .xlsx exten

JohnJPS — Wed, 12 Jun 2019 18:24:12 GMT

(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.