Combining multiple txt. files into a single xls.

[silentg7]

Hi,

I have multiple txt. files with the same format (see attached a sample). These files are generated monthly and contain information for sales transactions performed during the month. Each line in the txt. file is a seperate transaction. Depending on the nature of the transaction, the amount is included in a different column in the txt file.

Until now, in order to access and filter the information in the txt. files I opened each txt. file in excel and copied the data into one xls. table (e.g. i combined the sales data for one year in 12 txt. files into one table).

My goal is to create a workflow which combines those txt. files into a single xls. file. I tried using the dynamic tool, however without access.

I will be very grateful if someone can assist me.

Many thanks!

[Sntrada]

Hello @silentg7 ,

I have attached a sample workflow that does this. 

First, import one text file and configure the popup, you can use the attached image as a guide. Be sure to increase the field length in the input tool configuration, I used 1000 in my example. 

After you have imported one file successfully, change the file path to utilize a wildcard. For instance, the path of the first file is C:\Users\sntra\Downloads\Prodagbi.txt assuming all your files have the word Prodagbi in them, you can make the path C:\Users\sntra\Downloads\Proda*.txt, this will bring in and merge any file that has this word in the file name. After all this, you can set up an output tool to export everything as one xls file. 

Hope this helps, let me know if you have any questions. 

*update, attached another file. This yxmd one has parsing. But you'll probably still have to do some housekeeping to change some of the text values to numeric. 

[Himanshu_1984]

Hi Silen,

It will be good if you can share the sample data with headers. You can use the batch macro to read the all text file one by one and do all the operation on it.

Regards,

Himanshu

[grazitti_sapna]

Hi @silentg7 , you can try this.

1)Keep all your .txt files in single folder and zip it.

2)Use the zip file in alteryx with this configuration.

3) Cleaning and Transformation of data and combining all the files into single output.

Another possible solution is by suing directory and dynamic input tool.

1)directory tool config- supply full path name in directory.

2) Dynamic input tool config

While inputting data source same config will follow for csv as mentioned above.

3)Output

Attaching the workflow for reference.

Thanks.

[DavidP · Accepted solution]

Your file seems to be a fixed width file. I would therefore use the fixed width option in the flat ASCII file format in the input data tool as shown below. It will remove leading and trailing spaces.

Once you've done it for one file, you can use the widlcard *.txt option in the filename. 

[vizAlter]

Hi @silentg7 — Hope attached the solution will be helpful, if not let me know. Check the configuration window for each tool.

I am importing all the .txt files as we import .csv files, means 1st, I keep all the data in a single column only, then use a RegEx tool to put pipe sign ( "|" ) for each value to separate those further through TextToColumn tool.

In the End, the Output tool will generate a consolidated Excel file along with Today's date in the file name.

\s\s([[:alnum:]])

If it resolves your query please mark it "Solved" or "Solved" with a Like. This will help other users find the same answer/resolution.  Thank you.

[GraziellePereira]

Can you explain how did you did this cleaning transformation, please?