export to table fixed filed size

Hello I need to export my table fixed filed size by making numbers 15
characters right alignment and strings 20 characters left alignment.Hi Jim
There may be an easier way but with say:
CREATE TABLE mytemp ( id int NOT NULL, name varchar(10) NULL)
CREATE VIEW MyExport
SELECT RIGHT(SPACE(15) + CAST(id AS varchar(15)),15) AS Id,
LEFT(name+SPACE(20),20) as Name
FROM mytemp
The you could use BCP to export from the view.
John
"JIM.H." wrote:
> Hello I need to export my table fixed filed size by making numbers 15
> characters right alignment and strings 20 characters left alignment.

export to table fixed filed size

Hello I need to export my table fixed filed size by making numbers 15 characters right alignment and strings 20 characters left alignment.

You could either use the BCP utility or a SELECT statement if your table is small. Your SELECT statement would look something like this:

SELECTCONVERT(char(15),col1) ,CONVERT(char15,col2)...FROM yourtable

|||

Thanks for the reply, how do I arrange alignment, numbers to right, strings to left?

|||

Perhaps something like this:

SELECT space(15-len(NumericVal)) +convert(varchar,NumericVal),CONVERT(char(20), StringVal)FROM yourtable

export to table fixed filed size

Hello I need to export my table fixed filed size by making numbers 15
characters right alignment and strings 20 characters left alignment.Hi Jim
There may be an easier way but with say:
CREATE TABLE mytemp ( id int NOT NULL, name varchar(10) NULL)
CREATE VIEW MyExport
SELECT RIGHT(SPACE(15) + CAST(id AS varchar(15)),15) AS Id,
LEFT(name+SPACE(20),20) as Name
FROM mytemp
The you could use BCP to export from the view.
John
"JIM.H." wrote:

> Hello I need to export my table fixed filed size by making numbers 15
> characters right alignment and strings 20 characters left alignment.

export to fixed filed size

Hello I need to export my table fixed filed size by making numbers 15 characters right alignment and strings 20 characters left alignment.

You could define a view or use a query that contains the expressions to produce the character format of the data and use it to export the data. Ex:

bcp "select right(replicate(space(1), 15) + cast(numcol as varchar), 15) as numcol, cast(stringcol as char(20)) as stringcol from tbl" queryout ....

But this is probably not a task for SQL or command-line utilities. You could use DTS/SSIS packages but I am not sure of the performance implications. Ask in the SSIS forum.

Export to a Text file in SQL Server Express

I have a table in a database has the same filed with a text file:

RBW_AR_BOT_REC

Size

Comment

Justification

Padding

record_id

1

'R'

None

None

date

6

MMDDYY

None

None

time

4

HHMM

(24 hour time)

None

None

cust_num

8

1

- 9999999

Right

Zero

odometer

6

0

- 999999

Right

Zero

receipt

8

1

- 99999999

Right

Zero

exception_customer

1

'0' = No

'1' = Yes

None

None

valid_void

1

'0' = valid

'v' = void

None

None

Justification

Padding

None

None

So that, after export data from the table, the file file must has that struct
Tell me, how can i do?

None

None

None

None

Right

Zero

Right

Zero

Right

Zero

None

None

None

None

Justification

Padding

None

None

None

None

None

None

Right

Zero

Right

Zero

Right

Zero

None

None

None

None

I am going to move this to the sql express group at it is more a problem with using the software.

On another note, you can use the management studio for the express systems (Free Download) and have the query saved or displayed as text, or you could format up the sql script and then use the sqlcmd command line tool to execute that command and save the output as text. But with the information you supplied we are going to need more information, for exable an definition of the table and if you could format the output sample better in the forums message so it is easily read.

|||

hi,

there's no direct way to "export" to file in a defined format like old style random access files...

you can query a table object to return what you are looking for, but the export features are usually performed a client level (thus via an external application) and not at the server level..

you can perhaps use SqlCMD.exe (or oSql.exe), the command line (not graphics tools) provided with SQLExpress, providing the "-oc:\myfile.txt" parameter (-o indicates to output the result to the specified file) (more info and synopsis about SQLCmd at http://msdn2.microsoft.com/en-us/library/ms162773.aspx)... and play with some Transact-SQL function to obtain something similar to the required result..

say you are querying the AdventureWorks database for Person.Address table and you want to return the

[AddressID] (int) formatted as right padded 6 chars and the [ModifiedDate] (datetime) formatted with the yyyy-mm-dd (without time dimension) 8 chars values...

the typical query could be

SET NOCOUNT ON;

USE AdventureWorks;

GO

SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,

CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate

FROM Person.Address

ORDER BY ModifiedDate;

--<--

AddressID ModifiedDate
61 1996-07-24

234 1997-02-19

224 1997-12-05

11387 1997-12-29

1997-12-29

( result abridged )

and want the results to be output to c:\myRes.txt..

create a file to store your query like

<c:\myQuery>
SELECT RIGHT(REPLICATE(' ' , 6) + CONVERT(varchar, AddressID), 6 ) AS AddressID,
CONVERT(varchar(10), ModifiedDate, 121) AS ModifiedDate
FROM Person.Address
ORDER BY ModifiedDate;
</c:\myQuery>

and execute, from the command line, the SQLCmd utility like

c:\..\>sqlcmd -E -S(Local)\SQLExpress -dAdventureWorks -iC:\myQuery -oC:\myRes.txt

this will output the result as

AddressID ModifiedDate
61 1996-07-24

234 1997-02-19

224 1997-12-05

11387 1997-12-29

1 1998-01-04

( result abridged )

you can play with the other SQLCmd parameters as wel, like -h for repeted headers, -s for col separator and the like...

regards