BCP / sqlcmd / osql with encapsulated text fields?

Question

Can any of these command line tools export to .csv like:

"int_field", "varchar_field", "another_int_field"
10, "some text", 10
5, "more text", 1

etc?

i don't want to use a view or stored procedure to hack the double quotes in :)

Do you want "" around some of the int fields as well? Or is "int_field" just some text that you want in quotes? — Jaques, Mar 13 '12 at 11:28
sorry- didn't get the formatting right. the first row is meant to be the column headers (which i imagine would be all quoted as they're all strings) — Lee Tickett, Mar 13 '12 at 11:34
Can you write code in C# or something. There is no standard command line tools as far as I know, but it will be easy enough to write one — Jaques, Mar 13 '12 at 11:55
@Jaques i'm heading down that route- but it does surprise me none of the tools are capable of doing it (re-inventing the wheel springs to mind) — Lee Tickett, Mar 13 '12 at 11:58

Jaques · Answer 1 · 2012-03-15T06:14:16.207

Somthing that I've quickly done. If you know c# you can add to it, otherwise it probably will be useless. Not my best code, but it is doing the job. All the field types is not added here, so it needs to be done.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.IO;

namespace SQLCSVExport
{
    class Program
    {
        static void Main(string[] args)
        {
            bool trustedConn = false;
            string Servername = "";
            string Username = "";
            string Password = "";
            bool quotestring = false;
            string fieldterminater = ",";
            string tablename = "";
            string operation = "";
            string datafile = "";
            bool includeheadings = false;

            if (args.Length < 3)
            {
                ShowOptions();
                return;
            }
            else
            {
                tablename = args[0];
                operation = args[1];
                datafile = args[2];
                for (int i = 3; i < args.Length; i++)
                {
                    switch (args[i].Substring(0, 2))
                    {
                        case "-Q":
                            quotestring = true;
                            break;
                        case "-T":
                            trustedConn = true;
                            break;
                        case "-S":
                            Servername = args[i].Substring(2);
                            break;
                        case "-U":
                            Username = args[i].Substring(2);
                            break;
                        case "-P":
                            Password = args[i].Substring(2);
                            break;
                        case "-t":
                            fieldterminater = args[i].Substring(2);
                            break;
                        case "-H":
                            includeheadings = true;
                            break;
                    }
                }
            }
            SqlConnection conn;

            if(File.Exists(datafile))
            {
                try
                {
                    File.Delete(datafile);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    ShowOptions();
                    return;
                }
            }
            if (trustedConn)
                conn = new SqlConnection("Integrated Security=True;Initial Catalog=master;Data Source=" + Servername);
            else
                conn = new SqlConnection("Password=" + Password + ";Persist Security Info=True;User ID=" + Username + ";Initial Catalog=master;Data Source=" + Servername);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                ShowOptions();
                return;
            }
            SqlCommand cmd = new SqlCommand();
            SqlDataReader read = null;
            cmd.Connection = conn;
            if (operation == "out")
                cmd.CommandText = "Select * from " + tablename;
            else
                cmd.CommandText = tablename;
            try
            {
                read = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                ShowOptions();
                return;
            }
            string Dummy = "";
            if (read.HasRows)
            {
                if(includeheadings)
                {
                    for (int i = 0; i < read.FieldCount; i++)
                    {
                        if (quotestring)
                            Dummy += "\"" + read.GetName(i) + "\"" + fieldterminater;
                        else
                            Dummy += read.GetName(i) + fieldterminater;
                    }
                    WriteStrToFile(datafile, Dummy, fieldterminater);
                }
                while (read.Read())
                {
                    Dummy = "";
                    for (int i = 0; i < read.FieldCount; i++)
                    {
                        switch (read[i].GetType().ToString())
                        {
                            case "System.Int32":
                                Dummy += read[i].ToString() + fieldterminater;
                                break;
                            case "System.String":
                                if (quotestring)
                                    Dummy += "\"" + read[i].ToString() + "\"" + fieldterminater;
                                else
                                    Dummy += read[i].ToString() + fieldterminater;
                                break;
                            case "System.DBNull":
                                Dummy += fieldterminater;
                                break;
                            default:
                                break;
                        }
                    }
                    WriteStrToFile(datafile, Dummy, fieldterminater);
                }
            }
        }

        static void WriteStrToFile(string datafile, string dummy, string fieldterminator)
        {
            FileStream fs = new FileStream(datafile, FileMode.Append, FileAccess.Write);
            StreamWriter sr = new StreamWriter(fs);
            if (dummy.Trim().Substring(dummy.Trim().Length - 1) == fieldterminator)
                dummy = dummy.Substring(0, dummy.Trim().Length - 1);
            sr.WriteLine(dummy);
            sr.Close();
            fs.Close();
            sr.Dispose();
            fs.Dispose();
        }

        static void ShowOptions()
        {
            Console.WriteLine("usage: SQLCSVExport {dbtable | query} {out | queryout} datafile");
            Console.WriteLine("[-q quote string fields]         [-S Server Name]        [-U User Name]");
            Console.WriteLine("[-P Password]                    [-T Trusted Connection] [-t field terminator]");
            Console.WriteLine("[-H Add Headings]");
        }
    }
}

score 1 · Answer 2 · answered Mar 13 '12 at 13:19

1

The built-in tool that does this is SSIS, although I appreciate that it might be a "heavier" solution than you want and it's not fully supported in Express Edition (you haven't mentioned either the version or edition that you're using). You can define a text qualifier in the flat file connection manager in the package.

Alternatively, write a small script in your preferred scripting language.

answered Mar 13 '12 at 13:19

Pondlife

15,992
6
37
51

Not that familiar with SSIS, can you execute a query to export to a textfile without creating a package first? – Jaques Mar 13 '12 at 13:24
No, SSIS is package-based so you can't avoid creating one. Personally, if I couldn't use SSIS then I would use a language that already has CSV library support (like Perl or Python) because it's harder than it first seems to write CSV files. Your code doesn't appear to handle the case where a string contains quotes, for example. – Pondlife Mar 13 '12 at 13:32

score 0 · Accepted Answer · answered Mar 17 '12 at 14:52

0

Looks like this confirms my suspicions that the answer is:

No.

Thanks for the alternative suggestions.

answered Mar 17 '12 at 14:52

Lee Tickett

5,847
8
31
55

BCP / sqlcmd / osql with encapsulated text fields?

3 Answers3