Solved

Export to CSV and return as string

Posted on 2006-06-13
16
525 Views
Last Modified: 2008-02-01
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?
0
Comment
Question by:moruda
  • 6
  • 5
  • 2
  • +2
16 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16896791

OS?
DBMS?
0
 

Author Comment

by:moruda
ID: 16896940
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 16897106

Sorry I can't help, I don't use M$SQL.
0
 
LVL 3

Expert Comment

by:RickBeebe
ID: 16897136
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
 
LVL 8

Accepted Solution

by:
redpipe earned 500 total points
ID: 16897621
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16904957
You may want to take a look into using DTS, that would relieve you of the burden of generating a valid CSV.
0
 

Author Comment

by:moruda
ID: 16906332
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16907328
In general yes.  But it largely depends on your front-end app and the language you are using.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:moruda
ID: 16907631
i am using vb.net--
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16908203
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
 

Author Comment

by:moruda
ID: 16908275
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16908352
Than DTS is not for you, I would go with one of the other options.

Good luck.
0
 

Author Comment

by:moruda
ID: 16908907
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16913531
>>just that I trust dts better than my own code<<
So do I :)
0
 
LVL 8

Expert Comment

by:redpipe
ID: 16915963
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
 

Author Comment

by:moruda
ID: 16916395
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now