creating text files using bcp

I am using BCP and it works just fine. I get a txt file with tab seperated fields. Can i specify the character positions or lengths of each field so that all my fields line up at the same positions.

Help is appreciated.

I created a stored procedure with the below
declare @.filename varchar(50),
@.bcpcommand varchar(2000)

set @.filename = 'c:\report\media.txt'
print @.filename
set @.bcpcommand = 'bcp "select * from table" queryout "'+ @.filename -U -P'
exec master..xp_cmdshell @.bcpCommandYou could format your select as needed.|||Can u elaborate more ...how do i format my sql to seperate the fields.

like i want one field on position 1-10 and the seond from position 11-40 and so on...

Thanks|||Originally posted by hp1000
Can u elaborate more ...how do i format my sql to seperate the fields.

like i want one field on position 1-10 and the seond from position 11-40 and so on...

Thanks
Just create one long field or as many as you need like this:

select field1+replicate(' ',25-datalength(field1)+...
from table|||I am using

set @.bcpcommand = 'bcp "select medium_name + replicate(' ',10-datalength(medium_name)) from database..report_tbl" queryout "'+ @.filename + '" -U sa -P -c'

I get Line 6: Incorrect syntax near '

How can i escape the single quotes

Please help

Thsi method should work for what i want to do .

Thanks|||Originally posted by snail
Just create one long field or as many as you need like this:

select field1+replicate(' ',25-datalength(field1)+...
from table

I am using the above but it does not work with NULL values . How do i make it to work with null values. Help is very much appreciated.

Thanks|||If you attempt to format your fields within BCP...QUERYOUT you may very quickly reach the limitation on the length of the command line (I don't remember what it is, but you can check.)

What you can do instead, is create a stored procedure where you'd format whatever and whichever way you want, and in your BCP specify a call to that procedure along with QUERYOUT.|||Here's how I do it - much easier to read and manage changes.

Much like a structure or type, I create a temporary table in a stored procedure (TABLE vars won't work, as BCP won;t be able to see them), using CHAR data types, one field for each output field. I have a user-defined function for zero-padding, and one for right-justifying (space padding). I populate that table with all my data, then do a BCP statement consisting of SELECT field1 + field2 + field3 etc.

This gives my the spacing I want, and that one create table at the beginning of the procedure allows me to easily control the number spaces each field gets.

I can post a proc if it would be easier to understand.

-b|||My table def looks like this (for an SAP feed):

CREATE TABLE ##sap_table(sales_organization CHAR(4) NOT NULL,
distribution_channel CHAR(2) NOT NULL,
division CHAR(2) NOT NULL,
filler_1 CHAR(2) NOT NULL,
sold_to CHAR(8) NOT NULL,
delivering_plant CHAR(4) NOT NULL,
profit_center CHAR(10) NOT NULL,
material_code CHAR(10) NOT NULL,
pricing_date CHAR(8) NOT NULL,
charge_type CHAR(4) NOT NULL,
order_reason CHAR(3) NOT NULL,
project_number CHAR(24) NOT NULL,
contract_number CHAR(10) NOT NULL,
purchase_order_number CHAR(12) NOT NULL,
internal_sales_reference CHAR(12) NOT NULL,
usage_type CHAR(3) NOT NULL,
usage_detail CHAR(60) NOT NULL,
requestor_name CHAR(40) NOT NULL,
user_number CHAR(10) NOT NULL,
[user_name] CHAR(40) NOT NULL,
comments CHAR(100) NULL,
quantity CHAR(15) NOT NULL,
unit_of_measure CHAR(3) NOT NULL,
currency CHAR(5) NOT NULL,
extended_amount CHAR(15) NOT NULL,
customer_cost_object CHAR(40) NOT NULL,
cco_flag CHAR(3) NOT NULL,
xy_flag CHAR(1) NOT NULL,
filler_2 CHAR(6) NOT NULL )|||If you want fixed width data, why not use a format file?|||Thanks for all ur feedback . I am using coalesce and datalength functions and it did the trick.

I create a view with the fields i want in the format i want and then do the select on the view with my bcp and it writes to the text file just fine.

Thanks a lot,

Creating Text File from Stored Procedure

Greetings,

I have a sp that dumps text into a textfile but I am having trouble
creating the textfile.

EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'

The directory "The File" has a space in it. I've tried putting the
carat ^ before the space, and putting double quotes...but I keep
getting this error

'\\servername\d$\The' is not recognized as an internal or external
command, operable program or batch file.

If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
File\sub\filename.dat"' I get the same thing.

If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
File\sub\filename.dat""' I get

'"\\servername\d$\The File\sub\filename.dat"'
is not recognized as an internal or external command, operable program
or batch file.

Does anybody see what I am doing wrong?At a quick glance, it looks like filename.dat is simply not an
executable file - what exactly are you trying to do?

Simon|||Hi

Enquote the filename with double quotes should have worked, therefore the
file may not exist or could be still open. If "\\servername\d$\The
File\sub\filename.dat" from a command prompt says the file does not exist
then it probably doesn't or permissions are wrong.

If this is a batch file you may want to use a more appropriate extension.

John

"phantom" <phantomtoe@.yahoo.com> wrote in message
news:1126224325.956647.140030@.g43g2000cwa.googlegr oups.com...
> Greetings,
> I have a sp that dumps text into a textfile but I am having trouble
> creating the textfile.
> EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat'
> The directory "The File" has a space in it. I've tried putting the
> carat ^ before the space, and putting double quotes...but I keep
> getting this error
> '\\servername\d$\The' is not recognized as an internal or external
> command, operable program or batch file.
> If I do EXEC master.dbo.xp_cmdShell '"\\servername\d$\The
> File\sub\filename.dat"' I get the same thing.
> If I do EXEC master.dbo.xp_cmdShell '""\\servername\d$\The
> File\sub\filename.dat""' I get
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.
> Does anybody see what I am doing wrong?|||phantom (phantomtoe@.yahoo.com) writes:
> '"\\servername\d$\The File\sub\filename.dat"'
> is not recognized as an internal or external command, operable program
> or batch file.

Windows executes files depending on the suffixes. .dat is normally not
tied to any application. Thus, Windows does not know how to run the file.

If the file is a simple command file, the extension should be .BAT.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

creating text file ?? ways

1. How can i create text file using SQL query and T SQL ?
2. How many ways are there to achieve this. ?
thnkz
--
Mahesh kumar.R~Maheshkumar.r wrote:
> 1. How can i create text file using SQL query and T SQL ?
> 2. How many ways are there to achieve this. ?
Read BOL article "Copying Data From a Query to a Data File" for more info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||http://www.aspfaq.com/2482
On 3/15/05 11:20 PM, in article evadE#dKFHA.1476@.TK2MSFTNGP09.phx.gbl,
"~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote:

> 1. How can i create text file using SQL query and T SQL ?
> 2. How many ways are there to achieve this. ?
> thnkz
> --
> Mahesh kumar.R
>|||What is that specific you would want on the text file? The data too ? or
query output? Can you elaborate. To list a few options, You can look into
Query Analyzer features of output to file. You can see bcp and osql commands
in conjuction with xp_cmdshell.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote in message
news:evadE%23dKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> 1. How can i create text file using SQL query and T SQL ?
> 2. How many ways are there to achieve this. ?
> thnkz
> --
> Mahesh kumar.R
>|||thnk, Yes i want to export table > text file and few query outputs to text
file ?
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:d18hgs$v68$1@.news01.intel.com...
> What is that specific you would want on the text file? The data too ? or
> query output? Can you elaborate. To list a few options, You can look into
> Query Analyzer features of output to file. You can see bcp and osql
commands
> in conjuction with xp_cmdshell.
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "~Maheshkumar.r" <mfcmahesh@.hotmail.com> wrote in message
> news:evadE%23dKFHA.1476@.TK2MSFTNGP09.phx.gbl...
>

Creating one flat file per record in the data flow

I have a table that holds in each record an image (varbinary(max) actually), a text reference for the image and a MIME type for the image. I need to read this table and for each record that has been created since the last run, I need to create a file with the image as the content, the mime type as the file extension and the text reference as the file name. There will be one file created per record found by the data flow source.

I was assuming that I could use the flat file destination and manipulate the file naming using the contents of each record in the flow but am completely stumped on how to achieve this.

Does anyone have any ideas?

thanks

Have a look at the export column transform.|||cheers, it worked great.....

Creating Notepad file through SQL Server 2005

Hi friends,

Is there any way to create a text file or word file through sql server 2005.

If you have any source please suggest me..

P.Kumaran

The question is vague at this point. Can you explain more details?

|||

Hi Dinakar,

Actually I want to write the output result (which i get from a select statement) to a notepad file.

Is it possible to write the select query result in a notepad from sqlserver 2005.

P.Kumaran

|||

You can use BCP utility to create a text file with the results of a query. Check out Books Online (or even google) on how to use the BCP tool.

Creating mailing label using database

Hi!

I have these tables:

DB1
MID
IIN
Title
FullName
Address
Apt
City
Province
PostalCode

DB2
MID
Lang
Version

How can I output a text file formatted like the one below?

(05) 01046 (F 05)
Mr. Sylvain Cote
123 Sesame Street
Apt 6
Charny, BC
L8T 5G6

where
(05)-MID
01046-IIN
(F 05) - LangConcatenate the various address components, separated by carriage return characters.|||Could you please post a sample code?

Thanks.|||select Title + ' ' + FullName + char(13)
+ Address + Char(13)
+ 'Apt ' + Apt + char(13)
+ City + ', ' + Province + char(13)
+ PostalCode + Char(13)
from YourTable

Creating File

Is there a corresponding utility in SQL Server like
UTIL_FILE package in ORacle which can be used to create
text files from Stored procs...Thanks in advance for helpYes, try with
execute xp_cmdshell 'echo blahblahblah > c:\test.txt'
--
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr
"Kris" <maran123us@.hotmail.com> wrote in message
news:00a701c34ce1$e1e994c0$a001280a@.phx.gbl...
> Is there a corresponding utility in SQL Server like
> UTIL_FILE package in ORacle which can be used to create
> text files from Stored procs...Thanks in advance for help

Creating excel file from DTS package

Hi,

I have a created a DTS packges which is reading data from sql server table, manipulate this data as required and then create a text file with that data. I created the text file using FileSystemObject. I was writing one field at a time to the text file.

I need to same thing but instead of creating text file, I need to create a excel file with each column from database going to separate column in excel sheet. I tried to do this with FileSystemObject, but it was wrting all the columns from database to one cell in excel sheet. How can I fix this problem?

Thanks!Am I missing something or can't you just create a data transformation task going from your database to a excel spreadsheet?

Create a conenction to a spreadsheet and a connection to a database and build your transformation. DTS will take care of everything else. If you need to play with filesnames etc you can do that after the package has been created (using the filesystem object)|||is there any sample to show that how to ceate an excel file after reading data from sql server?

Thanks|||start>programs>Microsoft SQL Server>Import and Export Data

follow the wizard. select your db>Select your output file (excel)>hit next>run immediately>done|||Sorry, I didn't explain my question properly. I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.

Thanks!|||I can create an excel file and write data after reading from database but I also need to write totals, tax and grand totals after I finish writing all the records from the database. How can I add totals to the last line? I need to do all of this from a DTS package.

Thanks!|||I 've create a template with the functions for addign totals, etc already built in. using VB SCRIPT, copy the template and populate it every time you run the DTS package.|||Could you please explain how can I create this template and copy it every time I need this?

Thanks|||'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim objFSO, objFileTemplate, strFile

Set objFSO = CreateObject ( "Scripting.FileSystemObject" )
strFile = DTSGlobalVariables("strRootPath").Value & "Template.xls"
Set objFileTemplate = objFSO.GetFile ( strFile )
strMonth = Right ( "0" & DatePart ( "m", DTSGlobalVariables("dtStart").Value ), 2 )
strFile = DTSGlobalVariables("strPath").Value & _
DatePart ( "yyyy", DTSGlobalVariables("dtStart").Value ) & _
strMonth & _
" - " & _
DTSGlobalVariables("ID").Value & " " & DTSGlobalVariables("Id").Value & _
".xls"
DTSGlobalVariables("FileName").Value = strFile

If ( objFSO.FileExists ( strFile ) ) Then
Main = DTSTaskExecResult_Failure
Exit Function
End If

objFileTemplate.Copy strFile, 0

Main = DTSTaskExecResult_Success

End Function

Note: This is basically the code. You have a template in a directory. This vbscrip takes the template, copies it to a different directory. The name is obtained by using the 'Id' field and concatenating month and year.

If you want, we can take this offline, just let me know your email address.