Export to CSV and return as string

Hi,
I need some code to export a query into a CSV on the fly and be able to return the entire file as a string... so that we could save the file to the client.
Can anybody help?
morudaAsked:
Who is Participating?
 
redpipeConnect With a Mentor Commented:
I am not sure if I have understood you correct, but if you would like to convert a datatabel/resultset to a csv string, try the following.

====
public String ConvertToCsv(DataTable dt, char columnSeparator)
    {
      StringBuilder sb = new StringBuilder();
      foreach (DataRow row in dt) {
        foreach (DataColumn col in dt.Columns) {
          if (row[col.Ordinal] != null) {
            sb.Append(row[col.Ordinal]);
          }
          sb.Append(columnSeparator);
        }
        sb.Append(Environment.NewLine);
      }
      return sb.ToString();
    }
====

Mind you it's written in C# but there are loads of C# to VB.Net converteres out there.
0
 
MikeOM_DBACommented:

OS?
DBMS?
0
 
morudaAuthor Commented:
mssql
using vb.net as a client..
I want to run a query -- get the results as a csv and download it to the client as string so that I can save it locally
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
MikeOM_DBACommented:

Sorry I can't help, I don't use M$SQL.
0
 
RickBeebeCommented:
I have not used this tool, but it sounds interesting:

http://www.download.com/EMS-Data-Export-2005-for-SQL-Server/3004-10255_4-10535673.html?tag=tab_scr

I developed a .NET app from scratch because of business requirements to merge multiple record types, sorting, not standard CSV, etc.
0
 
Anthony PerkinsCommented:
You may want to take a look into using DTS, that would relieve you of the burden of generating a valid CSV.
0
 
morudaAuthor Commented:
i would like to do dts, but I need the text of the file-- can I do dts to a tempfile and read it back?
0
 
Anthony PerkinsCommented:
In general yes.  But it largely depends on your front-end app and the language you are using.
0
 
morudaAuthor Commented:
i am using vb.net--
0
 
Anthony PerkinsCommented:
Than yuo should be able to execute the DTS package which will save the result in a file and then you can use the System.IO namespace and in particular the StreamReader object to read it in.
0
 
morudaAuthor Commented:
yes, but i would like the sql to return the file text instead of the vb. reason is-- I do not want to worry about windows permission issues.
0
 
Anthony PerkinsCommented:
Than DTS is not for you, I would go with one of the other options.

Good luck.
0
 
morudaAuthor Commented:
i did what redpipe suggested--
i built the csv in vb--but i don't feel that comfortable with it (no real reason--just that I trust dts better than my own code)
thanks all
0
 
Anthony PerkinsCommented:
>>just that I trust dts better than my own code<<
So do I :)
0
 
redpipeCommented:
Coding is a personal thing. Some trust there own code over large vendors code, while some prefer the opposite. It's not hard finding arguments for either. DTS must allow for a broad specter of use-cases, and hence loads of "generic" functionality that can, for your isolated case, be troublesome and unnecessary. Writing your own code gives you control over the provided functionality, and hence the complexity of the business logic. The cost of that control is the responsibility for providing error-free code, which is never easy.

BUT in this case, your needs are "simple" and the method is "straight-forward". Every source code site on the net, in addition to the MS .Net Documentation, can provide identic or similar code samples. The .Net library is responsible for running through your resultset (datatable) and the only thing you hav to care about is if the coulm contains NULL values, and formatting the file correctly. By using the static Environment.NewLine variable you give responsibility to the regional settings of the OS to return the correct character representing a new line. The code might not be optimal, but it definitely works...
0
 
morudaAuthor Commented:
yes, i have been doing this type of thing for the past 6 years using asp to export about 20 different file formats specifically customized for each client. it works great, just felt that a simple dts would make it easier than recoding in vb.net. either way, it works fine. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.