Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Check out our powerful new search update! You can read more here. Please let us know if you have any feedback by creating a topic here.

Automated loading of text files

Meteoroid

Hi, I am sure this is a very routine task, but for some reason I have been having endless challenges making it actually happen within Alteryx.

 

Essentially my question is - what is the best approach to reading and loading text files in the following scenario?

 

Background:
A working directory will have 0-3 new CSV files every day. These files are all structurally identical. The filename will always have the form "subjectarea_yyyymmdd.csv"

 

Tasks:
1. Before processing Alteryx should check the control table to ensure a file has not been previously loaded.
2. Open and load the file into a SQL Server table, after which an INSERT record should be written to a control table, with the filename, processing date and number of records.
3 - A successfully processed file should be relocated from the working directory to an archive directory.
4 - If there are still unprocessed files in the working directory, repeat.

 

I have built a workflow for step 2 (but I need to figure out how to do it for a flexible filename). Step 3 is a simple batch file via a run_command.

 

I am not getting very far with task 1. It needs to be a conditional check, with a pass/fail branch (execute, don't execute). Likewise, I have no idea how to approach Task 4

 

I am not expecting people to do the work for me, but some general guidance about approach would be greatly appreciated.

Highlighted
Alteryx Certified Partner

@Manning_Oz,

This is what I would probably do. Use the Directory tool to get a file listing of the csv files in that specific directory.

Open up the control file that lists out files that have already been read in, use the summarize tool to group by on the filenames to get a file list.  Join the directory list of files to this summarized list of files from the control file based on the filename and whatever does not join, is a list of files that need to be opened.

This file list is then read into a batch macro that does #2.

I hope this helps and makes sense.  If you need more info on how to do the batch macro, let me know.

Labels