Input Text Data

[anovohatski1]

Hi All,

I tried to search through questions and online, but couldn't find an answer. Hopefully, one of you could help. I am trying to join multiple text files together using an Input tool, but get the following error: "...has a different number of fields that the 1st file in the set and will be skipped."

Input:

Daily .txt files get saved down in the same location, where each file has a rolling 3 days

(Ex: file 1: Name Address 7/31 6/1 6/2; file 2 Name Address 6/1 6/2 6/3, file 3 Name Address 6/2 6/3 6/4, etc)

Goal:

To combine the the files so i have Name Address 7/31 6/1 6/2 6/3 6/4 etc

Issue:

Get an error "...has a different number of fields that the 1st file in the set and will be skipped."

What's the best way to achieve my goal? 

[BrandonB · Accepted solution]

You can pull them in with no delimiter and then parse afterwards using a text to columns tool. Can you give that a try?

[anovohatski1]

worked like a charm, thank you! I can't believe i didn't think of that

[BrandonB]

The other approach would be to build an Alteryx macro that is set to allow differences in schema, but I thought that might be overkill for what you are trying to accomplish. This link covers that concept down towards the bottom: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/t...

[anovohatski1]

Importing without delimiter and then doing text to columns creates an issue with column names. Given that each file contains field names in first row, i now have multiple column names in different rows.

ex: 

Name Address 7/31 8/1 8/2

Name Address 8/1 8/2 8/3

Name Address 8/2 8/3 8/4

needed output

Name Address 7/31 8/1 8/2 8/3 8/4

I didn't see a possible solution in the link that you have shared. Would you happen to know of a tool that could help me? or am overcomplicating this whole process and should just use a macro tool as per your other suggestion?

[BrandonB]

The key will be to build a workflow to get it properly formatted for one file into the structure that you would like. Then you add a control parameter and action tool that update the Input Data tool. Then you go into the interface designer and change the setting so that it doesn't error on schema differences and auto configures by name of column. You save this as a macro and then put the macro into a new workflow with a Directory Tool at the beginning. The Directory tool will point at the location of your text files and pull all file paths. This will feed into your macro and pass all of the file paths to be run through. 

Taking the "Autoconfigure by name" approach should avoid your headers being repeated throughout the data because it is basically unioning the data and using the headers from each file in consideration of what the headers should be at the top of the file. 

[anovohatski1]

Thank you for your suggestions! After a bit of work, I was able to get it to work using the batch macro method.