I have a csv file created every day, I want to take the daily file and use it to build up an Alteryx database file. I did think all I needed to do was to union the daily file together with the previous days database file but it wont work. I assume this is because Alteryx opens the database file to read from it, so cant then write back to it at the same time. Is there a way to accomplish what I want to do with Alteryx?
Solved! Go to Solution.
I was going to save to a YXDB file - I also had a thought about my current method..... Its just going to slow down over time, each daily csv file I want to add can have up to 100,000 rows, my method would require reading in the existing YXDB file every day to create the new one, unless I'm doing something wrong?
Heres my basic workflow
With Alteryx formatted databases (YXDB or CYDB), you cannot just append to them. They are not true relational databases format due to their highly compressed nature. If you want the data to be kept in YXDB or CYDB format, then you have only one option - exactly what your workflow does, but you must add a Block Until Done tool before the output (as Ben mentioned). YXDBs are extremely efficient and small for the amount of data they hold. And are extremely fast to read into Alteryx. But your price for that is that they don't handle traditional relational database functions like Append.
If you are dead set on appending just the new records, then, again, as Ben said, you have to choose another file type for your storage. One quick and easy option is SQLite. Doesn't matter if you have SQLite on your machine or not. You don't need to. Just output to SQLIte and it will create the database and table for you. Your machine won't recognize it (if you try to open it it won't know what to do with it). But that's okay because Alteryx will recognize it and can read/write/append to it no problem.
All that being said - I've tried SQLIte, SQL Server, Oracle, Access, etc., and none come close to the compactness or speed of reading/writing/storing a YXDB or CYDB, even with the hassle of having to overwrite the entire table each time.
Hope all this info helps!!
SQL Lite seems to be the way to go - just started creating it and it seems to be working as expected, thanks!
Welcome. If you expect yourself to be querying the database often, you might want to run some parallel testing. The time you save with the daily load in SQLite may not outweigh the extra time it will take to query the data. If you are going to be querying this data often, you might even want to consider going to CYDB.
Yeah, thanks - any idea how I create an index on the data?
I don't know how (or if it's possible) to create indexes in SQLite by just using Alteryx. You'd probably have to install SQLite on your machine, but I don't know for sure.