How would I get a query output with different than comma delimiter?

[katrina11]

Hi experts,

Being a new in MS SQL Server 2005 just wonder how can I change delimiter “,” in the output to any other (for example a pipe?)

Actually after I had got a query output I chosed submenu “results to” under “Query” menu.

After that I right click somewhere on the output and get the menu options:
-To the text
-To the Grid
-To the file

…and chosed the "text" option . It always generate comma delimited output as .csv text.

Try as I might I was unable to get a file with different delimiter or fixed width file (even when I chose other than “text” options.

Could you please tell what I am supposed to do in order to get:
-the output with different delimiter
or
-fixed width file

Any help greatly appreciated

Katrin

 

[markros]

The simplest way to get the file in the desired format will be to use Import/Export Wizard.

PluralSight Learning Library

 

[katrina11]

markos,
Running query from Microsoft SQL Server Management Studio I was unable to to find import & export options (other than what i described in the initial post). Could you please kindly tell me how can I export my query output to change a delimiter?

It is a kind of urgent matter at the moment....

Thank you very much for your support!

Katrin

 

[markros]

Right click on your database, select Task, Export data. Then follow the wizard.

Alternative solution will be in using bcp utility or SSIS package, but for one time task I'd start with the Import/Export wizard.

There is a simple video showing the Wizard, but it shows import

http://www.youtube.com/watch?v=9Wmdhnx1niU

PluralSight Learning Library

 

[katrina11]

markos,
my understanding is that SELECT query (as I did before) is not enough and I need to create a temp. table based on my SELECT query (in order to use task menu).
Is it correct?
Would the following be a correct syntax for the purpose?
CREATE TABLE dbo.TempSiteIR
[memberId][int]
[membername][varchar](60)

INSERT INTO dbo.TempSiteIR

select distinct
memberid
,membername

Thank you for your help!

 

[Qik3Coder]

katrina,

declare @_tbl as table(
id int Identity(1,1), col1 varchar, col2 varchar,col3 varchar)

Insert into @_tbl Select 'A', 'A', 'A' 
Insert into @_tbl Select 'B', 'B', 'B' 
Insert into @_tbl Select 'C', 'C', 'C' 

--Standard
Select * from @_tbl
--Pipe Delimited
Select rtrim(id) +'|'+ col1 +'|'+ col2 +'|'+ col3 from @_tbl

Lodaiden

You've got questions and source code. We want both!

 

[markros]

I believe you can use the result of the query in the Wizard, but for simplicity we can try using a temp table for now as you indicated.

PluralSight Learning Library

 

[katrina11]

Lodaiden,

Does it mean that it is not necessarily to create a temp table on server? It looks like this technique does not require any export OR

it is within an export process?

Also sorry for the following naïve questions:

1. What “id” & “ identify(1,1)” really mean?
I assume that id is not a column as columns are already specified as col1, col2, col3….Is it a primary key or what?
2. Why do I need the same columns for multiple times ( Select ‘A’, ‘A’, ‘A’)?

 

[katrina11]

markros,

The thing is that I do not see any other way to export through the wizard except creationg a temp table on the Server....


How can I do otherwise?
How can I export query through the task menu while task menu
seems to be belonged to the particular database/table while my select query is based on a couple of tables?

It seems like I cannot even right click a table because it is not an only one taht I used for the query.....

I am totally confused...

 

[Qik3Coder]

Oi! Welcome to working with SQL.

This is independent of an "export" process. No temp tables needed. I use this method when I have to create ad-hoc flat files for other systems.

1.) an identity column is an autonumber column that gives you a primary key. Ideally you have some other value that makes the data unique, but this works. The id column here was actually to highlight that you have convert non-text fields to text to do this.

2.) Your data will not be the same. I merely used the same value for simplicities sake. you will notice that the select is referencing col1, col2, and col3. If there were different values in the table, then you would have gotten different values in the results.

Lodlaiden

You've got questions and source code. We want both!

 

[markros]

I've just tried this exercise myself. On the step when you select either a table or a query to execute I selected the query and all went well.

Again, select your database.
Right click, select 'Tasks', then Export data. I followed the wizard and after a few trial and errors it worked.

Caveat - each field name should be unique even though you're creating a text flat file.

PluralSight Learning Library

 

[katrina11]

markros,

Did you use Flat text destination?

Actually I am trying this way now and I am not sure I am doing it right in terms of the file name.
Do I need to specify file as a path f:\home\input\site.txt

or just without extention f:\home\input\site ?

 

[markros]

Either way should work, I think, but I used c drive and I have SQL Server installed locally. Make sure that the drive is visible for SQL Server (from the server).

PluralSight Learning Library

 

[katrina11]

Do you think "Fixed width" option should work without specifying a width?

 

[katrina11]

Sorry for so many questions, but it looks like system does not accept option First row column names ...

Pre-execute (Error)
Messages
Information 0x402090dc: Data Flow Task: The processing of file "F:\HOME\Input\SITEIDf.txt" has started.
(SQL Server Import and Export Wizard)

Error 0xc0202095: Data Flow Task: Failed to write out column name for column "serviceproviderid".
(SQL Server Import and Export Wizard)

Error 0xc004701a: Data Flow Task: component "Destination - SITEIDf_txt" (46) failed the pre-execute phase and returned error code 0xC0202095.
(SQL Server Import and Export Wizard)

Have you experienced the similar trouble?

 

[markros]

I didn't use the option First row column names. I did use Fixed width option.

Yes, I did get a few errors at first, but finally everything worked.

PluralSight Learning Library

 

[katrina11]

Now when I finally exported it I tried to read it into MS Access.

However after a while system complained that my "first record is too large". The only assumption which come to my mind is that without specifying the width in the process of exporting, each column appeared to be too wide by default....

I have no idea how to overcome the issue.....

 

[katrina11]

or actually I am going to try some adjustments in the initial select query:

cast(membername as char(60)) as membername

Hopefully it will narow the width....

 

[Qik3Coder]

You can link to the SQL table from Access, so that you are working with current, and not stale data.

You've got questions and source code. We want both!

 

[katrina11]

The previous seemed to having been wrong assumption because even after specifying width in the initial query and successfully exporting afterwards it takes forever to be imported into Access....

Does anybody have any idea why it happened?