locked
Export the SQL Server Table data to Text file using BCP (Number format issue) RRS feed

  • Question

  • Hi,

    We are doing the exporting of data from SQL Server 2008 tables into Text files. For this we are using the BCP utlity (BCP is our approved utlity). While exporting the data into Text file rest of the things are fine except the Number columns.

    Example : In SQL Server table data is like

    0.012

    -0.254

    But when we export the data into file it is expoting like

    .012

    -.254.

     

    But I want the data needs to be exported to text as the same format in the table. I tried to use all the options supported by BCP to acheive this. But i failed.

    Can anyone have any idea to overcome this issue please help me ?

     

    Thursday, May 13, 2010 6:55 AM

Answers

  • Example : In SQL Server table data is like

    0.012

    -0.254

    Actually that is one of frequent misconceptions related to numeric data representation in a database. The numeric data is stored in internal binary format within SQL Server for data types like money, float, etc.

    A particular software like SSMS may show it one way, while another like bcp a different way.

    If you need format control, you have to convert it to varchar prior to exporting.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Wednesday, May 19, 2010 9:04 AM
    Thursday, May 13, 2010 7:16 AM
  • Create a format file with -c option and refer that format file in your BCP utility. -c will treat all data as character data type.

    Non XML Format file is enough for you.

    • Marked as answer by KJian_ Wednesday, May 19, 2010 9:04 AM
    Thursday, May 13, 2010 7:49 AM

All replies

  • Example : In SQL Server table data is like

    0.012

    -0.254

    Actually that is one of frequent misconceptions related to numeric data representation in a database. The numeric data is stored in internal binary format within SQL Server for data types like money, float, etc.

    A particular software like SSMS may show it one way, while another like bcp a different way.

    If you need format control, you have to convert it to varchar prior to exporting.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by KJian_ Wednesday, May 19, 2010 9:04 AM
    Thursday, May 13, 2010 7:16 AM
  • Create a format file with -c option and refer that format file in your BCP utility. -c will treat all data as character data type.

    Non XML Format file is enough for you.

    • Marked as answer by KJian_ Wednesday, May 19, 2010 9:04 AM
    Thursday, May 13, 2010 7:49 AM