BACKGROUND To export CSV files from IBM DB2 Options to export csv from IBM DB2   Option 1: Using the   IBM command line processor: Connect to the database you want to export.   Use the EXPORT TO-command combined with a SELECT query to export a table (Documentation:   https://www.ibm.com/support/knowledgecenter/SSEPGG_9.8.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html ) We recommend to apply following parameters to ensure compatible formatting for your exported csv file: o   MODIFIED BY datesiso -> exports dates in ISO format (YYYY-MM-DD) o   MODIFIED BY timestampformat=”YYYY-MM-DD HH:MM:SS” -> exports timestamps in selected format o   COALESCE(column,’’) -> checks if content of data field equals   NULL   and replaces it with “” (empty string) if necessary. This addition is needed for every selected column that allows   NULL   values. Example: Additional Notes: IBM command line processor   automatically masks double quotes ("Example" -> ""Example"")   Option 2: Using the   IBM Data Studio: Connect to the database you want to export Select the table you want to export Open context menu by right clicking on the table Select the option   Unload   ->   With Export Utility   to open the integrated export tool Select   JDBC   as   Run method Pick your path and file name for the csv file To specify additional options, select the tab called   Options   (top left) To ensure a compatible format for your csv file, you should check the available control boxes as follows Click  Run  to start exporting the table You will find the csv file in your selected path Additional Notes: IBM Data Studio   automatically converts   NULL   to "" (empty string) IBM Data Studio   automatically masks double quotes ("Example" -> ""Example"")
View full article
BACKGROUND To export CSV files from Oracle DB Options to export csv from Oracle   Option 1: Using the   Oracle SQL Developer : Connect to the database you want to export Select the table you want to export Open context menu by right clicking on the table Select   Export   to start the integrated   Export Wizard To guarantee a proper export, you should deselect the option   Export DDL Change format to csv and set the path for the exported file Click   Next   to continue The following screen allows you to specify the columns you want to export (SELECT *… by default) Use the   Edit-Button   to change settings - otherwise you can proceed by clicking   Next Finally, you will receive a summary for the export process Make sure that your settings are valid and complete the export by clicking   Finish Notes: Oracle SQL Developer   automatically converts NULL to "" (empty string) Oracle SQL Developer   automatically masks double quotes ("Example" -> ""Example"")   Option 2): Using   SQLcl:SQLcl   is a free tool provided by Oracle. (Download:   http://www.oracle.com/technetwork/developer-tools/sqlcl/overview/index.html (Short documentation about how to get started and some of its features: https://mikesmithers.wordpress.com/2015/04/12/sqlcl-the-new-sqlplus/) Connect to the database you want to export To guarantee a proper export of your data, you have to change some of the system variables of   SQLcl: o   SET SQLFORMAT csv   (automatic conversion into proper csv) o   SET TERMOUT OFF   (suppresses output on display - this option is only necessary when using scripts) o   SET FEEDBACK OFF   (suppresses displaying the number of records returned by a query) To apply each option, just press   ENTER   after every single command Note: These options have to be set every time   SQLcl   is started! Use the integrated spooler with the appended file path to start the process SQLcl  will write the result set of your  SELECT-statement  to  table1.csv To finish the process, you have to stop the spooler by using the command:  spool off Notes: o You are not able to access the exported csv file until you stopped the spooler o  SQLcl  automatically masks double quotes (“Example” -> “”Example””) o  SQLcl  automatically converts NULL to “” (empty string)
View full article
Background To export CSV files from Microsoft SQL Server Options to export csv from  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
View full article
Background To export CSV files from PostgreSQL Options to export csv fromPostgreSQL Option 1: Using   pgAdmin III: Connect to the database you want to export Open the   SQL Editor   by clicking the   SQL-Button Select the tables individually with separate   SELECT Statements To run the query press   F5   or use the associated button When the task is finished you can continue the export with opening the integrated   Export data to file   tool To do so, open the   File   menu on the top left and proceed with   Export   from there Now you are able to make some adjustments for your csv format To guarantee a proper csv file we recommend the following settings Select your desired filename and start the process by clicking   OK You will receive a message about the successful export Notes: pgAdmin III   automatically converts NULL to "" (empty string) pgAdmin III   automatically masks double quotes ("Example" -> ""Example"")   Option 2) Using   PSQL: Connect to the database you want to export Use the   \COPY   [table_name | query]   TO   [filename]   WITH CSV   command (Documentation:   https://www.postgresql.org/docs/current/static/sql-copy.html) Add the following parameters to export a proper csv file: o   FORCE QUOTE *   (all values will be quoted) o   ENCODING ‘utf8’   (specifies encoding option) Example: Start the export by pressing   Enter You will receive a confirmation about the number of rows that have been exported Notes: PSQL   automatically converts NULL to "" (empty string) PSQL   automatically masks double quotes ("Example" -> ""Example"")
View full article
To ensure a proper import of your data there are some rules that have to be considered when creating the csv file.
View full article