CODEWITHSUNDEEP
sql-serverbcp
user7577070
user7577070

Reputation: 97

SQL Server BCP Export where comma in SQL field

I have successfully exported a file to a CSV. I used the following code to export the file: 

exec xp_cmdshell 'bcp "[DC_TrainEnvironment].[dbo].[HAFacilities_Master]" out "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master.csv" -f "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_FORMAT.fmt" -o "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_LOG.txt" -T -S "HAPDBCDC02,2112"'

This works well, but there is a problem. There are certain fields that have commas within them. I am using this format file:

enter image description here

What can I do in the format file to avoid having to change the entire format file? Is there a quick way to indicate a field terminator will not end with the comma in those certain fields? I.e. "','" or "'',''"?

Appreciate any help. Thanks

Upvotes: 2

Views: 5112

Answers (3)

Danimal111
Danimal111

Reputation: 2062

This is my C# solution, you will have to morph for use in SQL Server, but a template none the less:

private static void EliminateCommasInFields(List<string> files, string path)
{
    foreach (var f in files)
    {
        string filename = path + f.Substring(0, f.Length - 4);

        StreamReader sr = new StreamReader(filename);
        String[] rows = Regex.Split(sr.ReadToEnd(), "\r\n");
        sr.Close();
        StreamWriter sw = new StreamWriter(filename);

        for (int i = 0; i < rows.Length; i++)
        {
            //search the row for the dreaded ," (indicating a comma in the field)
            while (rows[i].IndexOf(",\"") > 0) 
                {
                    //find the position of the first ," and it's ending ",
                    int start = rows[i].IndexOf(",\""); 
                    int end = rows[i].IndexOf("\",");
                    //find the position of the first comma within this field
                    int comma = rows[i].IndexOf(",", start + 1, end - start);
                    while (comma > 0) //eliminate all the commas within this cell
                    {
                        //Replace the offending comma with a space 
                        rows[i] = rows[i].Substring(0, comma) + " " + rows[i].Substring(comma + 1, rows[i].Length - (comma + 1)); 
                        //Search for next comma
                        comma = rows[i].IndexOf(",", start + 1, end - start);
                    }
                    //Save the rest of the row eliminating the double quotes for this cell
                    rows[i] = rows[i].Substring(0, end ) + rows[i].Substring(end + 1, rows[i].Length - (end + 1));
                    rows[i] = rows[i].Substring(0, start + 1) + rows[i].Substring(start+ 2, rows[i].Length - (start + 2));

                }

            sw.WriteLine(rows[i]);
        }
        sw.Close();
    }
}

Upvotes: 0

jamie
jamie

Reputation: 775

If a field you are exporting will can contain the character you are using to delimit the fields in your file, your choices are:

  1. Use a different delimiter - this seems the easiest way go to. Just use "|" or "~" as your delimiter. Do a global replace of "," to "|" in the format file with just about any text editor. It's not clear why it would be difficult to modify "the whole" file. Maybe you have a recipient of the file who requires comma-delimited?

  2. If you must use commas as the delimiter, you must change your column delimiter from comma (,) to quote-comma-quote (","). To do this, you need to use the escape character to get the BCP program to ignore the quotes you want to use as delimiters in the output file so it does not think of them as the quotes it understands to contain the delimiter in the format file. So...

Instead of ","... use... "\",\""

This will result in the following

col1,col2,"col,3",col4

For col1 the delimiter is: , to represent this in format file use: ","

For col2 the delimiter is: ," to represent this in format file use: ",\""

For col3 the delimiter is: ", to represent this in format file use: "\","

For col4 the delimiter is: , to represent this in format file use: ","

I hope that helps.

Upvotes: 2

mvisser
mvisser

Reputation: 670

With great difficulty. You will need to select your columns in the bcp command and replace the commas.

exec xp_cmdshell 'bcp "SELECT ''ColumnHeader'' UNION ALL SELECT REPLACE(Column1, '','', '''') FROM [DC_TrainEnvironment].[dbo].[HAFacilities_Master]" out "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master.csv" -f "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_FORMAT.fmt" -o "\\fspincdc01\data$\HIMSSAnalytics\Data Analyst\HG Data\Technical Documentation\HA Facilities\HAFacilities_Master_LOG.txt" -T -S "HAPDBCDC02,2112"'

Upvotes: 0

Related Questions