Search code examples
sql-serverexcelbcp

BCP divides content in Excel in two columns because of semicolon

I am trying to export table content from SQL-Server to csv file. I am using BCP utility and "|" as delimiter. However, the content of one column is divided in two columns in Excel, because of semicolon (ABC;D)

A | B

ABC |D

Wanted:

A | B

ABCD

Any ideas? Thanks.

Solution

  • This problem has nothing to do with BCP or SQL Server, and all to do with how you didn't correctly define the file's properties when you tried to open it.

    Open a new Worksgeet/Workbook in Excel and go to the Data tab in the Ribbon. Then click the "From Text" button, navigate to your file, select it and then press Import. A window like the below will then appear:

    enter image description here

    Most likely the File Origin with me ANSI or UTF-8 not MS-DOS. Also (at least for the example I have) I tick "My data has headers".

    Click Next, and then you'll need define your delimited, that's a pipe (|), so select "Other" and then put a pipe in the text box. I've also select none for text qualifier, as I assume you have none, like so:

    enter image description here

    Notice that there are now 2 columns in the data in the image.

    Then, finally, you can select and define some additional information. For example, for a phone number you'll want to define it as text, and if you have dates, define those with the correct format. Anything else leave as "general".

    Then select Finish, and the position you want the data, and done!