Uploaded image for project: 'Solution Center'
  1. Solution Center
  2. SOL-448

Proper csv export from Microsoft SQL Server

    XMLWordPrintable

    Details

    • Type: How To
    • Status: Published
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Solution:
      Hide

      Microsoft SQL Server

      Option 1)

      Using the SQL Server Import and Export Data Wizard / DTSWizard (tool should be included in your SQL Server installation):

      • Open the wizard
      • Select your data source and the database you want to export
      • Continue with Next
      • Select Flat File Destination as your destination and browse to the desired file path for your csv file. It is necessary to use *.csv as your file extension
        Note: If you want to override an existing file, you have to delete the old file first! Otherwise the exported data will be appended to the existing file.
      • Change the Text qualifier to (double quotes) and set UTF-8 as Code page
      • Continue by clicking Next
      • Since double quotes will not be escaped properly, you have to select ‘Write a query to specify the data to transfer’ and proceed with clicking Next
      • Now fill in a valid SELECT Statement to select the data you want to export
        Note: It is important to make use of the REPLACE function to ensure a proper masking of double quotes. It is only necessary for Columns that might contain strings with double quotes.
        (Documentation of REPLACE: https://msdn.microsoft.com/de-de/library/ms186862.aspx)
        Example:
        SELECT 
        [Customer Key], 
        [WWI Customer ID], 
        REPLACE([Customer], '"', '""') AS 'Customer', 
        REPLACE([Bill to Customer], '"', '""') AS 'Bill to Customer', 
        REPLACE([Category], '"', '""') AS 'Category', 
        REPLACE([Buying Group], '"', '""') AS 'Buying Group', 
        REPLACE([Primary Contact], '"', '""') AS 'Primary Contact', 
        [Postal Code], 
        [Valid From], 
        [Valid To], 
        [Lineage Key] 
        FROM Dimension.Customer
        

      • Confirm your settings for the Flat File Destination and continue with Next
      • Start the export by clicking Finish
      • You will receive a short report about the successful export

      Option 2)

      Using the bcp Utility:
      As this method is kind of unhandy, we recommend using option 1. If, for some reason, you are not able to use the DTS Wizard you can use the following manual to export your data with the bcp Utility.
      (Documentation bcp Utility: https://msdn.microsoft.com/en-us/library/ms162802.aspx)

      • The needed bcp command looks something like this:
        bcp “SELECT STATEMENT” queryout “OUTPUT FILEPATH” –c –t”,” –r”\n” –q –S SERVERNAME –T
      • To ensure a correct export, the SELECT Statement has to meet certain criteria:
      • All columns that might contain commas, double quotes or any other special characters, have to be enclosed by “char(34)” (ASCII Code for "). Thus will add double quotes before and after the exported field.
        Example:
        “SELECT
        [Customer Key],
        char(34) + [Customer] + char(34)
        FROM …”
      • All columns that might contain double quotes, have to be selected with the REPLACE function. This way, double quotes will be masked properly in your csv file ("Example" -> ""Example"").
        Example:
        “SELECT
        [Customer Key],
        char(34) + [Customer] + char(34),
        char(34) + REPLACE([Category], char(34), char(34) + char(34)) + char(34)
        FROM …”
        Note: Since the SELECT Statement has to start with double quotes, you have to use char(34) as a replacement for " aswell. Otherwise the console would interpret it as the end of the SELECT Statement.
        REPLACE([Category], ", "") -> REPLACE([Category], char(34), char(34) + char(34))
        (Documentation of REPLACE: https://msdn.microsoft.com/de-de/library/ms186862.aspx)
      • All columns that allow entries being NULL, must use the COALESCE function. This guarantees proper conversion from NULL to “” (empty string).
        Example:
        “SELECT
        [Customer Key],
        char(34) + [Customer] + char(34),
        char(34) + REPLACE([Category], char(34), char(34) + char(34)) + char(34),
        char(34) + COALESCE([Primary Contact],'') + char(34)
        FROM…”
        Note: COALESCE is using two single quotes as second parameter!
        You might have to combine the functions depending on your database design.
        Example:
        “SELECT
        [Customer Key],
        char(34) + [Customer] + char(34),
        char(34) + COALESCE(REPLACE([Category], char(34), char(34) + char(34)), '') + char(34),
        char(34) + COALESCE([Primary Contact],'') + char(34)
        FROM…”
      • A complete bcp command can look as follows:
        bcp "Select
        [Customer Key],
        [WWI Customer ID],
        char(34) + [Customer] + char(34),
        char(34) + [Bill to Customer] + char(34),
        char(34) + REPLACE([Category], char(34), char(34)+char(34)) + char(34),
        char(34) + [Buying Group] + char(34),
        char(34) + COALESCE(REPLACE([Primary Contact],char(34),char(34)+char(34)),'') + char(34),
        char(34) + [Postal Code] + char(34),
        [Valid From],
        [Valid To],
        [Lineage Key]
        From WideWorldImporters.Dimension.Customer"
        queryout "C:\Test.csv" -c -t"," -r"\n" -q -S HW1729 –T
      • Start the export by pressing Enter
      Show
      Microsoft SQL Server Option 1) Using the SQL Server Import and Export Data Wizard / DTSWizard (tool should be included in your SQL Server installation): Open the wizard Select your data source and the database you want to export Continue with Next Select Flat File Destination as your destination and browse to the desired file path for your csv file. It is necessary to use *.csv as your file extension Note: If you want to override an existing file, you have to delete the old file first! Otherwise the exported data will be appended to the existing file. Change the Text qualifier to “ (double quotes) and set UTF-8 as Code page Continue by clicking Next Since double quotes will not be escaped properly, you have to select ‘ Write a query to specify the data to transfer ’ and proceed with clicking Next Now fill in a valid SELECT Statement to select the data you want to export Note: It is important to make use of the REPLACE function to ensure a proper masking of double quotes. It is only necessary for Columns that might contain strings with double quotes. (Documentation of REPLACE : https://msdn.microsoft.com/de-de/library/ms186862.aspx ) Example: SELECT [Customer Key ], [WWI Customer ID], REPLACE ([Customer], ' "' , '" "' ) AS 'Customer' , REPLACE ([Bill to Customer], ' "' , '" "' ) AS 'Bill to Customer' , REPLACE ([Category], ' "' , '" "' ) AS 'Category' , REPLACE ([Buying Group ], ' "' , '" "' ) AS 'Buying Group ' , REPLACE ([ Primary Contact], ' "' , '" "' ) AS ' Primary Contact' , [Postal Code], [ Valid From ], [ Valid To ], [Lineage Key ] FROM Dimension.Customer Confirm your settings for the Flat File Destination and continue with Next Start the export by clicking Finish You will receive a short report about the successful export Option 2) Using the bcp Utility : As this method is kind of unhandy, we recommend using option 1. If, for some reason, you are not able to use the DTS Wizard you can use the following manual to export your data with the bcp Utility . (Documentation bcp Utility : https://msdn.microsoft.com/en-us/library/ms162802.aspx ) The needed bcp command looks something like this: bcp “SELECT STATEMENT” queryout “OUTPUT FILEPATH” –c –t”,” –r”\n” –q –S SERVERNAME –T To ensure a correct export, the SELECT Statement has to meet certain criteria: All columns that might contain commas , double quotes or any other special characters, have to be enclosed by “ char(34) ” (ASCII Code for " ). Thus will add double quotes before and after the exported field. Example: “SELECT [Customer Key] , char(34) + [Customer] + char(34) FROM …” All columns that might contain double quotes , have to be selected with the REPLACE function. This way, double quotes will be masked properly in your csv file ("Example" -> ""Example""). Example: “SELECT [Customer Key] , char(34) + [Customer] + char(34) , char(34) + REPLACE( [Category] , char(34), char(34) + char(34)) + char(34) FROM …” Note: Since the SELECT Statement has to start with double quotes , you have to use char(34) as a replacement for " aswell. Otherwise the console would interpret it as the end of the SELECT Statement. REPLACE( [Category] , ", "") -> REPLACE( [Category] , char(34), char(34) + char(34)) (Documentation of REPLACE : https://msdn.microsoft.com/de-de/library/ms186862.aspx ) All columns that allow entries being NULL , must use the COALESCE function. This guarantees proper conversion from NULL to “” (empty string). Example: “SELECT [Customer Key] , char(34) + [Customer] + char(34) , char(34) + REPLACE( [Category] , char(34), char(34) + char(34)) + char(34) , char(34) + COALESCE( [Primary Contact] ,'') + char(34) FROM…” Note: COALESCE is using two single quotes as second parameter! You might have to combine the functions depending on your database design. Example: “SELECT [Customer Key] , char(34) + [Customer] + char(34) , char(34) + COALESCE( REPLACE( [Category] , char(34), char(34) + char(34)) , '') + char(34) , char(34) + COALESCE( [Primary Contact] ,'') + char(34) FROM…” A complete bcp command can look as follows: bcp "Select [Customer Key] , [WWI Customer ID] , char(34) + [Customer] + char(34) , char(34) + [Bill to Customer] + char(34) , char(34) + REPLACE( [Category] , char(34), char(34)+char(34)) + char(34) , char(34) + [Buying Group] + char(34) , char(34) + COALESCE( REPLACE( [Primary Contact] ,char(34),char(34)+char(34)) ,'') + char(34) , char(34) + [Postal Code] + char(34) , [Valid From] , [Valid To] , [Lineage Key] From WideWorldImporters.Dimension.Customer" queryout "C:\Test.csv" -c -t"," -r"\n" -q -S HW1729 –T Start the export by pressing Enter
    • Category 1:
      ETL / Data Integration
    • Category 2:
      3rd Party Tools - ETL

      Attachments

        Issue Links

        1. MSSQL1.png
          18 kB
        2. MSSQL2.png
          73 kB
        3. MSSQL4.png
          19 kB
        4. MSSQL5.png
          24 kB
        5. MSSQL6.png
          17 kB
        6. MSSQL7.png
          17 kB
        7. MSSQL8.png
          29 kB
        8. MSSQL9.png
          23 kB

          Activity

            People

            • Assignee:
              CaptainEXA Captain EXASOL
              Reporter:
              CaptainEXA Captain EXASOL
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: