Export to CSV

juddaman posted on Thursday, August 23, 2007

I'm about to add an export to CSV option to my app. I'm not using the CSV file for persistence or anything, that’s all handled by a RDB. It will just be one off calls (as required), kind of a report generation. Anybody got any suggestion of a good “OO” place to put this. I suppose if my use-case is "generate CSV output" it kind of deserves its own BO maybe? So something like DailyProjectReport : ReadOnlyBase<DailyProjectReport>. With a

string MakeReport(DateTime weekBeginning)

method returning the CSV format of the data ready to put into a file. Sound okay? Has anybody else done something similar?

RockfordLhotka replied on Friday, August 24, 2007

juddaman replied on Friday, August 24, 2007

Hi Rocky

Thanks for the reply and reassurance. I ended up creating a BO to do the fetch and get the data for the report as described before. However I made a new project "Reporting" to deal with exporting the data to different types such CSV, XML, HTML etc, I didn’t want to bung all that in the BO. Also it gives me a layer of indirection, so I could "talk" directly to the DB and not through the BO if ever a situation arose that required this.

Regards

George

yimiqi replied on Wednesday, September 19, 2007

George

How did you achieve converting the data from BO to CSV?  I'm currently working on this similar requirement that you seem already easily implemented, do you mind posting some code examples here? Did you use reflections? 

I'm not familiar with reflection implementation, so struggling with the reflection code that is not giving me back the desired results.

Thanks in advance. 

juddaman replied on Wednesday, September 19, 2007

Hi

My requirement was to produce a report in the format of CSV file. I created a new RO object say MyReportLineInfo this had all the properties for the report lines. Then I created a list of these, say MyReportList. Then I created another class ReportCSV which took the data from the report (foreach line get properties (no clever reflection or anything)) and basically stuck commas between the properties. It then exported the resulting string to a given file. Worked for me! I'm presuming you have a slightly different requirement. Is your resulting CSV a report that you will distribute in some way? I’m not really sure why you want to use reflection are you planning on extending CSLA so all object can expose their properties as a CSV or something?

George

yimiqi replied on Wednesday, September 19, 2007

George

Thanks for the quick reply.  My requirement is much like yours - produce a CSV format file.  There isn't any distribution requirement associated.  I already have RO and ROC object created and loaded with data.  The part I'm stuck is to take the data from the ROC business object (say MyReportList), including column names (which are properties defined in the RO business object., say MyReportLineInfo), to a comma separated value string , and then export the resulting string to a given file.

How did you get all the properties from your BO dynamically without using reflection?

If we are dealing the data in dataset, it would be a completely different approach.

I'm using reflection is because people on this forum suggested me.  see my another post seeking ideas

on what you've complished.

http://forums.lhotka.net/forums/thread/17634.aspx

And I searched the forum on the similar topic, all the answers indicated that I should use reflection to do what i'm trying to do.  I'm curious then, how did you do it?

I feel a little comfort now knowing at least somebody has done it and works!

Thanks for any inputs

juddaman replied on Wednesday, September 19, 2007

ozitraveller replied on Wednesday, September 19, 2007

Sorry to stick my nose in here, but I had a quick look at the other thread. And I think the origianal idea was to be able to do the same thing with a BO as can be done the a datatabe and loop through the fields collection concatenating as you go. And the suggestion was to use reflection to somehow get a list of BO properties to loop through.

Try this is hsould be quicker.

StringBuilder csv = new StringBuilder();
foreach (MyReportLineInfo info in MyReportList)
{
 csv.Append(string.Format("{0},{1}", info.FirstName, info.Surname));
}
csv.ToString();

ozitraveller replied on Wednesday, September 19, 2007

Sorry to stick my nose in here, but I had a quick look at the other thread. And I think the origianal idea was to be able to do the same thing with a BO as can be done the a datatabe and loop through the fields collection concatenating as you go. And the suggestion was to use reflection to somehow get a list of BO properties to loop through.

Try this is should be quicker.

StringBuilder csv = new StringBuilder();
foreach (MyReportLineInfo info in MyReportList)
{
 csv.Append(string.Format("{0},{1}\r\n", info.FirstName, info.Surname));
}
csv.ToString();

yimiqi replied on Wednesday, September 19, 2007

ozitraveller:

Sorry to stick my nose in here, but I had a quick look at the other thread. And I think the origianal idea was to be able to do the same thing with a BO as can be done the a datatabe and loop through the fields collection concatenating as you go. And the suggestion was to use reflection to somehow get a list of BO properties to loop through.

Your summary is exactly right. 

 My use case is the same is George's when dealing with one BO, but mine extends to many reports from different BOs while his is only one report in the entire application.

By the way, welcome to stick your nose in here and thanks a lot for the sample code. I'm trying to avoid hard coding BO properties though.

ozitraveller replied on Thursday, September 20, 2007

RockfordLhotka replied on Thursday, September 20, 2007

yimiqi replied on Thursday, September 20, 2007

ozitraveller, I use StringBuilder to do the string work too.  Thanks for pointing it out.

Rocky, is ObjectAdapter or DataMapper in your csla project (sorry if this question seems too fundamental to ask)?  I'm stuck in the PropertyInfo.GetValue() part, don't know what parameters should be sent.  I'm getting TargetTypeException unhandled error no matter what parameters I pass to the GetValue()

RockfordLhotka replied on Thursday, September 20, 2007

yimiqi replied on Friday, September 21, 2007

RockfordLhotka:

Yes, both are in CSLA .NET. I think both are discussed in Chapter 5 if I remember correctly.

 

Rocky

 

Thank you Rocky.  I'll be reading up on the ObjectAdaptor tonight.

ozitraveller replied on Tuesday, October 02, 2007

Just thought I'd add this, just in case anyone is interested.

        //Util.Export(@"d:\test.txt", salesEnquiryInfoList);
        public static void Export(bool addColumnHeaders, string filename, object source)
        {
            DataSet ds = new DataSet();
            Csla.Data.ObjectAdapter oa = new Csla.Data.ObjectAdapter();

            try
            {
                oa.Fill(ds, source);
                string[] strItems = new string[ds.Tables[0].Columns.Count];

                using (StreamWriter sw = new StreamWriter(filename, true))
                {
                    //Column headings
                    if (addColumnHeaders)
                    {
                        foreach (DataColumn column in ds.Tables[0].Columns)
                        {
                            strItems[column.Ordinal] = column.ColumnName;
                        }
                        sw.WriteLine(string.Join(",", strItems));
                    }

                    foreach (DataRow drRow in ds.Tables[0].Rows)
                    {
                        drRow.ItemArray.CopyTo(strItems, 0);
                        sw.WriteLine(string.Join(",", strItems));
                    }
                    sw.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

umar replied on Wednesday, June 22, 2011

Thank you for posting this code it does exactly what I needed and I was able to add it to my common module and use it across all my modules

Curelom replied on Wednesday, June 22, 2011

This is perhaps slightly off-topic, but if anybody needs a really good .net csv parser they can find it here.

http://www.codeproject.com/KB/database/CsvReader.aspx

yimiqi replied on Wednesday, September 19, 2007

juddaman:

Hi

How did you get all the properties from your BO dynamically without using reflection?
I didn't. :-D I hard-coded. I see no reason not to hard code, I only want certain properties in my report and I have to define that somewhere.

George

George

Thank you for clarifying the use case.  Yeah, hard code would work if only deal with one BO.  In my case, I will need to be able to build the function in a dynamic manner so that any BO that needs a report can call it.

Thank you for your top of head code too.

ozitraveller replied on Sunday, June 26, 2011

I've started doing csv like this lately.

Hope this helps.

Create TABLE _vTable   (id INT, val VARCHAR(100))

INSERT INTO _vTable

SELECT 1,'test 1' UNION ALL

SELECT 2,'test 2' UNION ALL

SELECT 3,'test 3' UNION ALL

SELECT 4,'test 4' UNION ALL

SELECT 5,'test 5' 

declare @csv varchar(max)

SELECT @csv =

  STUFF(

    ( select ',#' + x + '|' AS [text()] from

    (

select x from (

   select convert(varchar(20), id) + ',' + val  x from _vTable

) a

      ) b

      FOR XML PATH('')

    ), 1, 1, '')

select @csv = replace(@csv, ',#', '')

select @csv = replace(@csv, '#', '')

select @csv = stuff(@csv, CHARINDEX('|', @csv, LEN(@csv)), 1, '')

select @csv = replace(@csv, '|', CHAR(13)+ CHAR(10))

print @csv

select @csv