Can BCP command place data in specific positions in text file?

update your query and order the columns here

SELECT * FROM whatevertable 
>>>
SELECT ...col1,..., col2,... FROM whatevertable 

No, you misunderstood my question.

As it is now...the BCP populates the text file with col1 | col2 | col3 |

I want Col1 to go to position 10 in the text file. Col2 go to position 14. Col3 position 25 and so now. No more pipe delimited. I want to be able to say...col1 goes to x position in the text file. Col5 goes to position 105 in the file and so on.

Are you trying to do some kind of mail letters/mailmerge? personalized text files?

EDI flat files that a customer wants and the data has to be in specific locations.

I think I can have one column...insert the cols (each piece of data) into this one column and with spaces between each piece of data. Then BCP this one column into the text file.

For example:
Insert into whatevertable
select col1 + <some space> + col2 + <10 more spaces to get me to the correct position> + and so on

I think what Hain is getting at is to read the data and then output it again.  When outputting by adjusting the order of the select query, you are adjusting the order the data will be written.

You can do this without the database by reading one row at a time, perhaps placing it into an array, then rewriting the line in the new order but I think it would be easier to create a new file that way you keep the original.  In other words, read line one into an array.  Write the line with the new order array(5), array(0), array(10) new line and repeat.

>>I think what Hain is getting at is to read the data and then output it again.  When outputting by adjusting the order of the select query, you are adjusting the order the data will be written. 

Order is not the issue. Space between each col is what I'm trying to solve for.

I think what I posted above should work. Not an ideal solution but that might work. https://www.experts-exchange.com/questions/29201868/Can-BCP-command-place-data-in-specific-positions-in-text-file.html#a43203507 

-t "|"

change this to

-t " "

to make it space

or better

-t "\t"

make it TAB character...
if you really need NOTHING, then you can use "select COELESCE(col1,col2,...) from ..."
instead of "select * from ..."

now, as I said before,
dont use "select *" use "select col2, col3, col1, col5, col24,..." 

Thanks, Hain. That won't work either. I think I need to do what I mentioned. It's not one space between each col data. The space can be 2 or 10 or 20.

 https://www.experts-exchange.com/questions/29201868/Can-BCP-command-place-data-in-specific-positions-in-text-file.html#a43203507  

Thanks, sligghtwv. I hadn't thought of that. I'll try an example and see how it works.

you can use CONCAT/LEFT/RIGHT/REPLICATE

select right(concat(REPLICATE('X',20),'HainKurt'),20),
       left(concat('HainKurt', REPLICATE('X',20)),20),
       right(concat(REPLICATE(' ',20),'HainKurt'),20),
       left(concat('HainKurt', REPLICATE(' ',20)),20) 

Select allOpen in new window

+ order the columsn in select

Thanks, Hain. I'll run a test tomorrow and see how this works.

it is basically, LPAD and RPAD which is missing from SQL server...

Let me take a look. Thanks

>>you can use CONCAT/LEFT/RIGHT/REPLICATE

If they need right side padding, sure, use three functions when one will do:
left(concat('HainKurt', REPLICATE(' ',20)),20)

or
cast('HainKurt' as char(20))

I'll try tomorrow 

Ha, no :). I'll try this and post back.

Thanks, Hain.

I'll close this question and work on it. I'll post back if I need help.