I need to export data to a positional flat file without a delimiter that has several records with different formats. I, also, need to loop through the data being exported to arrange the data in the file by a key. (Account) I have created 5 tables in 2005 with the different formats needed as all varchar columns at the proper size required by the spec and are ready for export. These tables are sorted by the account key; however the key is only in one record.
The first record identifies a facility, which is a single record. The next record identifies an account. The next 2 records identify all the different types of tasks that are assigned to an account that have different formats. Then the final record for each account is the actual charges. Therefore, my task is to write the flat file in the steps.
Both 3 and 4 can have several records of each with the same format that belongs to the original account. 5. Insert the 4th record type with the actual charges. Again, can have several records. 6. Loop back to a second account record (step 2) that is the next account. Then repeat steps 3-5 for that account until all the accounts have been entered.
Each record is delimited with a LF and CR only without any column delimiters.
There must be someone that has done this and can tell me what the best way to do it is and how they did it. SSIS, CLR, BCP?
asked Oct 06, 2010 at 02:56 PM in Default
there are things that databases are for, and there are things that applications are for. this is one of those circumstances where you should really be writing an application...
answered Oct 06, 2010 at 03:35 PM
Matt Whitfield ♦♦
Looking at it, this could be done as a T-SQL script, assuming you can get the keys sorted out.
What I would first try to do is create intermediate tables holding the results for each intermediate step (1-5) as run across all records - keeping the key and any sequencing / ordering information in. OVER & ROW_NUMBER clause may help here.
Then either create a view or a CTE that builds up the output - something like
and then your final output is
Wrap up the script in a .sql file, and use SQLCmd (or oSQL) to connect to the server, execute the script, and capture the output in a new file.
you could definitely do this in SSIS, but it is a bit tricky and i would need table structures and sample data plus sample output to provide any meaningful help though.
All you need to do is create a flat file connection with row delimiter LF CR, and concatenate the mixed format records in a single row to your requirements. Oleg is right though, even though you have the format in the database, you will either have to convert them to Char in you source query, or use an SSIS transform component to pad the values to the right.
So, if you would like some more help, feel free to post table structures and sample data and sample output.
answered Oct 06, 2010 at 07:29 PM
Daniel and Thomas are headed in the right direction on this one. The key will be to number the rows internally (perhaps using more than 1 column) so that they come out in the file in the order you need. BCP could certainly provide the output but, because all the rows have a different format, you need to contatenate the data for each row into a single column for all the rows. An output staging table would be good for all that work and then you'd just BCP out the final data column in the correct order.
answered Oct 10, 2010 at 05:34 PM
This can be done using nothing more esoteric than TSQL, but I can't give you the full sample code to do it merely from your description. I don't agree that you need a special application to do this unless there is something of the complexity that I've missed. There are some cases (usually the arcane and mysterious rules of IT Departments) that make it impossible to wack out supporting applications in C# or whatever. What on earth are the production staff or confuguration managers going to think?
answered Oct 11, 2010 at 01:58 AM