?
Solved

Export to CSV and return as string

Posted on 2006-06-13
16
Medium Priority
?
598 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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
 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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