Create an Alteryx file by adding a daily csv

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?

Best Practices

Help

Accepted answers

mbarone

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!!

craigja

SQL Lite seems to be the way to go - just started creating it and it seems to be working as expected, thanks!

All comments

BenMoss

Correct.

So two things you can do;

1, use your current method, but ads a block until done to between your union and output. Connect your output go stream 2 and place a browse on stream 1.

2, you could use a file type which allows for appends, such as a database or excel file.

Ben

craigja

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

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335