This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.