Export Data From SQL to a Delimited Text File

[jmiskey]

I have a SQL database, where I need to join 8 different tables (and perform different levels of grouping) and export a delimited text file. It is basically an extract used to merge into a participant statement at an external printing vendor.

For each person, a set of records will be exported in the following format (each row to have multiple fields delimited with a pipe symbol):

H - Header row
A - Account header
T - Account transaction details (multiple rows)
S - Account summary
F - Footer row

Each person may have multiple accounts, so the A-T-S section will repeat within each person for each account.

Can someone recommend a good tool to use for such a data extract? While Crystal Reports is great for statements, it does not seem to work well for data exports (especially delimited files that have long fields). With all the grouping and different row types for each person, I fear it may be difficult to do with straight Transact-SQL.

Thanks.

 

[mrdenny]

BCP is the recommened tool to extract data from SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jmiskey]

I have never heard of BCP before (other than Business Continuity Plan).

Can you link multiple tables?
Can you do grouping?
Can you use/set criteria?
Is it easy to use?

 

[mrdenny]

BCP is bulk copy program. It's a command line tool used to import and export data from SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jmiskey]

I get the feeling that what I want to do is probably far too complex for that. We are talking about joining 8 different tables, with multiple join fields, multiple levels of grouping, numerous calculations, criteria, and returning dozens of fields (in the 5 different record type formats outlined in my original thread).

I think I need something pretty advanced and powerful. Currently we do the report in Crystal Reports. If I could just convert it from a printed statement to a text file extract in Crystal, it would work great, but Crystal is horrible at creating delimited text files.

We have another report writing program here can Report Writer that has a SQL version that we have used to create data extracts from other database applications before in the past and it has worked well. I have never used the SQL version before, but maybe I should give it a try...

 

[mrdenny]

BCP can export data. It doesn't matter how many tables are involved. It accepts a query as one of the paramaters. You can give it either a select statement or a stored procedure name.

Take your query, and create it as stored procedure then call the procedure from BCP. It will create your text file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[xlbo]

Given that you have asked the question in a Reporting Services forum, I would suggest MS SQL Server Reporting Services....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jmiskey]

mrdenny,
The problem is, I currently have no query! All the table linking, grouping, and calculations, are currently done within Crystal Reports. To try to re-write as SQL is probably going to be a monster, and may be beyond my level of SQL writing expertise!

Geoff,
I will look into MS SQL Server Reporting Services...

Thanks.

 

[mrdenny]

You can setup SQL Profiler to monitor the SQL Server, then run the Crystal Report. Profiler will then show you the query that Crystal is running against the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jmiskey]

Denny,
That sounds pretty cool! If I can capture that code, it could save me a ton of work! Do you know of any good write-ups on how to do what you proposed? In the meantime, I will scour the web to see what I can find out about it.

 

[mrdenny]

If you have a low load database server just fire up SQL Profiler and leave the events with the defaults.

If the server is higher load then click the filter button and put a filter on the loginname field for the login that the crystal report uses to log into the database. This will give you all the commands which that login kicks off. Then just find the correct one, highlight the line, and the SQL Statement will be in the bottom half of the window.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jrennatc2]

You could also get the query from the Crystal report itself (in the database menu item). The problem you may face is that any of the formulas or calculations done in Crystal aren't going to show in either the SQL profiler or the Crystal reports query.