Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Create an Alteryx file by adding a daily csv

craigja
8 - Asteroid

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?

7 REPLIES 7
BenMoss
ACE Emeritus
ACE Emeritus
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
8 - Asteroid

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

AlteryxDB.jpg

mbarone
16 - Nebula
16 - Nebula

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
8 - Asteroid

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

mbarone
16 - Nebula
16 - Nebula

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.  

craigja
8 - Asteroid

Yeah, thanks - any idea how I create an index on the data?

mbarone
16 - Nebula
16 - Nebula

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.

Labels