How to Create Text File Using SQL Script with Text "|"

How to create text file using sql script with text |

The pipe character has a special meaning in batch commands, so it must be escaped using the caret character. This should work:

DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = 'Hello world^| '
SET @Cmd ='echo ' +  @Text + ' > C:\AppTextFile.txt'
EXECUTE Master.dbo.xp_CmdShell  @Cmd

Although this is really not a good way to write data to a text file: usually SQL Server should not have permission to write to the root of the C: drive, and xp_cmdshell is disabled by default. I suggest you look at alternatives like sqlcmd.exe, bcp.exe or a small script in your preferred language (PowerShell, Perl, Python, whatever).

It is generally much easier, safer and more flexible to query data from SQL Server than it is to push it out from the server side. In your specific case, it looks like you want to write out a delimited file, and bcp.exe is intended for that purpose.

Using SQL Script, create a text file with new line character

Windows uses a carriage-return line-feed sequence to indicate a new line, character codes 13 & 10 so:

select 'hello' + char(13) + char(10) + 'world'

^{(Note than in SSMS grid view you will just see a double space)}

it won't create the txt file any more

Ah thats the echo command line not supporting new-lines.

You could

echo This is my text line 1 > "C:\FileStore\test.txt" & echo This is line2 >> "C:\FileStore\test.txt"

Or find a different way that does not involve the command line.

How to create text file using sql script with text |

The pipe character has a special meaning in batch commands, so it must be escaped using the caret character. This should work:

DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = 'Hello world^| '
SET @Cmd ='echo ' +  @Text + ' > C:\AppTextFile.txt'
EXECUTE Master.dbo.xp_CmdShell  @Cmd

Although this is really not a good way to write data to a text file: usually SQL Server should not have permission to write to the root of the C: drive, and xp_cmdshell is disabled by default. I suggest you look at alternatives like sqlcmd.exe, bcp.exe or a small script in your preferred language (PowerShell, Perl, Python, whatever).

It is generally much easier, safer and more flexible to query data from SQL Server than it is to push it out from the server side. In your specific case, it looks like you want to write out a delimited file, and bcp.exe is intended for that purpose.

How to create text file in sql server

Writing the query for this necessitates that you have the data in some queryable format (e.g. a table in your DB). Once you have that you can easily write a query that SELECTs just the columns you are looking for (ak_from, ak_to, w) and places the result into a text file via bcp (see: Using bcp utility to export SQL queries to a text file and http://msdn.microsoft.com/en-us/library/ms162802.aspx).

Presuming you'll have more than just 5 records and 3 VIDs you could write the bcp within a cursor so that you can loop and create multiple text files based on your conditions.

For the sake of example, the code below simply puts the 5 records into a temp table, and then outputs each of the three text files.

CREATE TABLE ##tbl
(
    Vid INT NOT NULL
    ,ak_from INT NOT NULL
    ,ak_to INT NOT NULL
    ,w INT NOT NULL
)

INSERT INTO ##tbl
SELECT 11, 164885, 164885, 24
UNION SELECT 11, 164885, 431072, 3
UNION SELECT 51, 731754, 690695, 2
UNION SELECT 51, 204086, 316310, 2
UNION SELECT 54, 818522, 501263, 1

DECLARE @vid VARCHAR(2)
DECLARE @querytextNoVid VARCHAR(100)
DECLARE @querytext VARCHAR(100)
DECLARE @filelocation VARCHAR(100)
DECLARE @cmd VARCHAR(255)

DECLARE vid_cursor CURSOR FOR SELECT DISTINCT vid FROM ##tbl
OPEN vid_cursor
FETCH NEXT FROM vid_cursor INTO @vid

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @querytext = '"SELECT ak_from, ak_to, w FROM ##tbl WHERE vid = ' + @vid + '"'
    SET @filelocation = '"c:\out_vid' + @vid + '.dat"'
    SET @cmd = 'bcp ' + @querytext + ' queryout ' + @filelocation + ' -T -c'
    EXEC master..XP_CMDSHELL @cmd

    FETCH NEXT FROM vid_cursor INTO @vid
END
CLOSE vid_cursor
DEALLOCATE vid_cursor

DROP TABLE ##tbl

You'll need to ensure xp_cmdshell is enabled via sp_configure (see: Enable 'xp_cmdshell' SQL Server)

How to create a .txt file and insert data into it in SQL?

CREATE EVENT createFile 
ON SCHEDULE 
    EVERY 1 DAY
    STARTS CURRENT_DATE + INTERVAL '11:30' HOUR_MINUTE
ENABLE
DO
SELECT *
    INTO OUTFILE 'drive:\\folder\\filename.ext'
    FROM updated_movie
    WHERE created_at >= NOW() - INTERVAL 1 DAY;

Modify conditions, output expressions, add export specifications if needed.

Check secure_file_priv setting and related ones, and justify destination forder accordingly. FILE privilege needed.

PS. BEGIN-END, DELIMITER, etc. - are excess.

How to write string to a text file inside an ms sql statement

Try this

First of all create the stored procedure rather than alter. see linked below which is used write string to files.

spWriteStringTofile

Now before executing we need to allow permission to execute Ole Automation Procedures. In order to do that copy the below code and execute.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Finally execute the below code to store string to file.

execute spWriteStringToFile 'This article describes how to fully access the
                             local filesystem from SQL Server. It shows a
                 way of reading and writing data to file, and
                 accessing the details of the server''s
                 filesystem using OLE Automation to access
                 the filesystem object'
                 , 'D:\Demo','test.txt';

I hope this will help you. If you have any problems or suggestions let me know.

C# script to convert first line of txt document into a create table statement for SQL Server

I would improve few things in this code :

1. StringBuilders are more efficient than just concatenating strings,
   you should use them
2. Handing both windows ("\r\n") and Unix ("\n") lines ends.
3. Reading only the first line of the file from disk instead of reading all of
   the file to the memory and then get the first line from it

Here an example code that contains those improvements :

string tableName = "myTable";
string delimeter = " ";
string line = null;
using (Stream stream = File.OpenRead("FilePath"))
using (StreamReader sr = new StreamReader(stream))
{
    line = sr.ReadLine();
}
string fileHeader = line.Replace("\r", string.Empty).Replace("\n", string.Empty);
string[] fileHeaderSegments = fileHeader.Split(new string[] { delimeter }, StringSplitOptions.None);
StringBuilder sb = new StringBuilder(string.Format("CREATE TABLE {0} (", tableName));
for (int i = 0; i < fileHeaderSegments.Length; i++)
{
    if (i != 0)
    {
        sb.Append(",");
    }
    sb.Append(fileHeaderSegments[i]);
    sb.Append(" varchar(255)");
}
sb.Append(");");
Console.WriteLine(sb.ToString());
Console.ReadKey();

How to load data from a text file in a PostgreSQL database?

The slightly modified version of COPY below worked better for me, where I specify the CSV format. This format treats backslash characters in text without any fuss. The default format is the somewhat quirky TEXT.

COPY myTable FROM '/path/to/file/on/server' ( FORMAT CSV, DELIMITER('|') );

---

Related Topics