SQLite database output which works well for running Batch Macros

[Aakash55]

Hello

I have got thousand plus CSV Files in a single folder. All the files have got exact same schema, same columns and data structure. There is one file for each date, containing all the data for that date. And everyday a new CSV file is being copied to that folder which contains the data for the new date.

The CSV files are named in this format YYYYMMDD.CSV and the FileName is also saved as a column in the CSV files.

I have designed a workflow in Alteryx which does all the data cleaning and modification steps according to my requirements. Then I have saved it in the form of a BATCH MACRO.

Whenever I have to add a new column to these CSV files or have to do any other type of modification, then I simply run the Batch Macro, and it applies all those new changes to every single CSV Files without any problems of any kind.

Now my current problem is that, I have decided to move from the CSV File Format to the Sqlite file format because of the advantages of Sqlite file format. My CSV files are really big in size and they contains many million rows of data in each file, therefor I need to save them in Sqlite format. I cannot use Alteryx .yxdb because that does not support any type of APPEND JOB etc.

Can someone please suggest me the approach which I should use in my case, like what type of settings should I do in the Output Tool, so that I am able to run my Batch Macros on them, "which are already working fine for my csv files".

Which of these options will work best for my current requirement of Batch Macros -

1. Separate Sqlite Database for each CSV File Output.

2. Same Sqlite Database but Separate Table for each CSV File Output.

3. Same Sqlite Database and Same Table for each CSV File Output. ------ this will act like an Append Job.

I am totally new to using the Sqlite file format, so any more tips or tricks for this file format are most welcome. Please share any links that could help me to use this file format more efficiently with the Alteryx Workflows.

I have attached the sample CSV data files containing very small number of rows. Actual files have millions of rows in each file. If any more information is needed from my side, then please let me know.

Thanks and Best Regards

[atcodedog05]

Hi @Aakash55 

If you refer to my example I am using formula tool to create new columns. You can a similar setup. Again there is a possibility I might be missing something crucial.

Hope this helps : )

[Aakash55]

Thanks again.

I think I should spend some more time with my actual data files and try to replicate the scenario of adding a few more columns by using various formula tools etc and then trying append that data into Sqlite files.

This process will give me the practical insights which will help me in posting the replies in a better way. I will create a new thread for this going ahead.

Once again, thanks a lot to both of you @Qiu and @atcodedog05  for the great help provided. I guess there are not many guys here who are using Sqlite format data files, so any guidance in this direction is highly appreciated.

Best Regards