creating csv based on certain key of db table

Question

hello, I have the following code:

which will generate the following table :

into a csv file:

I want to split that csv into different CSVs using COMPANY_ID column as identifier.
for example,
first file will be named COMPANY_ID10.CSV with content -> (notice the companyID columns which is 10)

and so on as per companyID. how should I approach it? thanks

Answer 1

@gal mor , Welcome to Microsoft Q&A, I recommend that you could convert your current csv to many datatables group by Company_ID, then you could convert these datatables to csv files.

Here is a code example you could refer to.

static void Main(string[] args)  
        {  
            var dt = ConvertCSVtoDataTable("test.csv");  
            var result = from rows in dt.AsEnumerable()  
                         group rows by rows["Company_ID"]  
                         into grp  
                         select grp;  
            foreach (var item in result)  
            {  
                DataTable newdt = item.CopyToDataTable();  
                StringBuilder sb = new StringBuilder();  
  
                IEnumerable<string> columnNames = newdt.Columns.Cast<DataColumn>().  
                                                  Select(column => column.ColumnName);  
                sb.AppendLine(string.Join(",", columnNames));  
  
                foreach (DataRow row in newdt.Rows)  
                {  
                    IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());  
                    sb.AppendLine(string.Join(",", fields));  
                }  
  
                File.WriteAllText("CompanyID" + item.Key + ".csv", sb.ToString());  
  
            }  
             
        }  
        public static DataTable ConvertCSVtoDataTable(string strFilePath)  
        {  
            DataTable dt = new DataTable();  
            using (StreamReader sr = new StreamReader(strFilePath))  
            {  
                string[] headers = sr.ReadLine().Split(',');  
                foreach (string header in headers)  
                {  
                    dt.Columns.Add(header);  
                }  
                while (!sr.EndOfStream)  
                {  
                    string[] rows = sr.ReadLine().Split(',');  
                    DataRow dr = dt.NewRow();  
                    for (int i = 0; i < headers.Length; i++)  
                    {  
                        dr[i] = rows[i];  
                    }  
                    dt.Rows.Add(dr);  
                }  
  
            }  
  
  
            return dt;  
        }  

Hope my solution could help you.

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @gal mor

How do you call that function "ExportExcel"?

First suggestion, as that function needs to receive three parameters... relevant => DataTable and Filename.

During creating that DataTable, implement a filter like "get all company_ids, create a DataTable for each company_id," and export each DataTable. Also, build the Filename based on company_id.

Answer 3

Sorry I forgot to reply. I was using the logic of the code, and it was helpful. thanks a lot.
If you're interested I ended up doing:

 void CreateCsvForEachCompany(Dictionary<int, List<RecordObject>> dict)  
            {  
                //for every company in dictionary we create a csv  
                foreach (KeyValuePair<int, List<RecordObject>> entry in dict)  
                {  
                    int key = entry.Key;  
                    List<RecordObject> value = entry.Value;  
                    string filePath = "D:/CompaniesRecordsByObj/";  
                    string fileName = key.ToString() + ".CSV";  
  
                    int length = entry.Value.Count;  
                    StreamWriter writer = new StreamWriter(filePath + fileName, true, Encoding.Default);  
                    StringBuilder csv = new StringBuilder();  
                    GetObjectPropertiesForSecondRow(csv);  
  
                    foreach (RecordObject record in value)  
                    {  
                        writer.WriteLine(record.OrderCompanyId + "," + record.OrderBtlId + "," + record.Zehut + "," + record.CreateDate + "," + record.TimeStamp + "," + record.Interface);  
                    }  
                    writer.Close();  
                }  
            }