Question Create CSV file for each folder

[Mr C]

I'm new to C# and I'm trying to figure out how to create one CSV per folder using data from a SQL database. I have several large SQL tables that are named by year(Ex. Claims_2001, Claims_2002, etc..). I will need to retrieve the data for 5 years from the database, create a folder for each year, and place one CSV file with the data for the year in the folder.

For example, I will need loop through 12 tables of data. Once the data for the first table is retrieved from the database, the first folder(folder_1_2001) would need to be created. The CSV file for Claims_2001 would need to created inside of folder_1_2001. This process would continue until all 12 folders containing 12 CSV files were created.

I'm currently using a datatable and CsvHelper to create CSV files. I'm not sure about how to loop and create each folder with a CSV file for each year.

Also not sure if it would be best to create individual SQL select statements to retrieve data from each table or if it would be best to insert all of the data into one SQL table and use the year column to create individual folders and CSV files.

Any help is appreciated.

using( var dt = new DataTable() )
{
    dt.Load( dataReader );
    foreach( DataColumn column in dt.Columns )
    {
        csv.WriteField( column.ColumnName );
    }
    csv.NextRecord();

    foreach( DataRow row in dt.Rows )
    {
        for( var i = 0; i < dt.Columns.Count; i++ )
        {
            csv.WriteField( row[i] );
        }
        csv.NextRecord();
    }
}

 

[Skydiver]

If these are truly large tables, then it doesn't make sense to load all the data into memory in a DataTable only turn around to just write the data out to a CSV. It would be better to just use the DataReader you have to help you just read in one row at a time, and then write out that row.

Anyway, I think the basic problem is that you trying to solve your problem as one big monolithic problem. Break it down into parts. Solve each part individually.

In my mind, since the tables have a fixed naming convention, then it would be simple enough to loop over them. Pseudo code would look something like:

for suffix = 1 to 12 do
    let folder_name = $"folder_1_{suffix}"
    Directory.Create(folder_name)
    create CSV file in folder_name
    let table_name = $"Claims_{suffix}"
    let query = $"SELECT * FROM {table_name}"
    let command = new Command(query, ...)
    let dataReader = command.ExecuteQuery()
    while (dataReader.Read())
        add row to CSV file

 

[Mr C]

Thanks for the response and I understand the recommendation. Initially, I was trying to extract the data in one big loop in case I was asked to run the process more than once a day. Since I now know that I will only have to produce the files once, I can produce each file one at a time.