Stored Procedure That Exports Data into CSV Files Only Exports to One File

Stored procedure that exports data into csv files only exports to one file

Seems to work fine for me. I have a few suggestions:

(1) stop doing all that string concatenation to build a date. You can do the same thing much easier as in:

SELECT @StartDT = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @MinDOS), '19000101');

(2) stop declaring varchar without length. And to ensure the right output, I prefer convert:

SET @FileLocation = 'C:\test\' + @TableName
   + CONVERT(CHAR(10), @StartDT, 120) + '.csv';

(3) instead of "debugging" the code by running the stored procedure and inspecting the output in the folder, why not sanity-check your input first? Also, why use two variables for the date?

DECLARE 
   @StartDT DATE, 
   @TableName NVARCHAR(50), 
   @FileLocation VARCHAR(255);

SET @TableName = N'ViewAccountDetail';

SELECT @StartDT = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', MIN(dos)), '19000101')
   FROM dbo.accn_demographics;

   PRINT @StartDT;
-- ^^^^^ debugging 101 - what month do we think we're starting at?

WHILE @StartDT < '20110901'
BEGIN
    SET @FileLocation = 'C:\test\' + @TableName
      + CONVERT(CHAR(10), @StartDT, 120) + '.csv';

      PRINT @FileLocation;
    --^^^^^ again, debugging 101 - what does the filename currently look like?

    --EXEC BCP_Text_File @TableName, @FileLocation    
    SET @StartDT = DATEADD(MONTH, 1, @StartDT);
END

Export table to csv file by using procedure (csv name with timestamp)

There are a few steps:

• create a directory using `CREATE DIRECTORY my_dir as 'C:\dir';
• make sure that Oracle has read,write on the folder on the computer (best accomplished by creating it in the Oracle install folder)
• grant the user executing the procedure GRANT read,write on DIRECTORY my_dir to the_user;
• download and compile the handy procedure here

I have used this and it works really nicely.

Usage

data_dump ( 'Select emp_name from emp',
             CURRENT_TIMESTAMP||'filename.csv',
             my_dir);

(vastly simplified sample!)

After creating the directory verify your work by running this:

• Select * from ALL_DIRECTORIES;
• you should see your directory
• logon to the machine where the database is located and verify the folder path exists and the oracle user has permissions on it. Networked drives are only possible if the user running the oracle service has permissions on that folder

Export to CSV in stored procedure in SQL Server 2008

You are seeing "Incorrect syntax near queryout". That is a T-SQL error message. You are ,as you said, running this from within a stored procedure. Both BCP and SQLCMD are command line utilities, meaning you will have to run them from a command (DOS) prompt.

There is a way to execute directly from SQL, but you will have to use: xp_cmdshell Here is a good article on how to use it:

https://www.mssqltips.com/sqlservertip/1633/simple-way-to-export-sql-server-data-to-text-files/

This was written for SQL Server 2005 but should work on 2008 as well.

Procedure to export table to multiple csv files

You can using the UTL_FILE package. You can only read files that are accessible from the server on which your database instance is running.

See http://www.devshed.com/c/a/Oracle/Reading-Text-Files-using-Oracle-PLSQL-and-UTLFILE/
and Oracle write to file

---

Related Topics