asked
export data to a positional flat file
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. 1. Insert the one facility record. 2. Insert an account record with the key information to the account. 3. Insert the first task record that is assigned to the account without the key. 4. Insert the second task record (with a different format of different columns). 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?
@rjimshaffer What you describe seems to be a rather strange design. Why do you want different formats inside of the same file? It would be very difficult to parse these on the other end because you mention that there maybe several task records and then there are possibly multiple actual charges. There has to be a way to differentiate between the record types. Also, defining columns as varchars will not help you to create positional file, because there are no trailing spaces in varchar values and you will need them in order for your what you call records to be of consistent/specified size. This means that the values you select will have to be cast as char of appropriate size to align your data properly.
Thanks to all. I agree with Oleg too. My problem is that this is a real life set of requirements that are laid out to the T, which prevents me from doing it another way. It has to be exported this way to keep the account information together between the several record formats. It has to go to a file that can be pulled into another system. The information is patient data, but basically what the first format is the hospital (facility and 1 row), the next record is the patient info at discharge. The 3rd record is all the diagnoses of the patient. The fourth is the procedures that were performed and the final record is all the charges. I have everything in 4 different tables and 1 view. All are in the right size varchar formats needed in the export file. Each column is sized by the spec and all are varchar. There are actually 2 files for export, In patients and Out patients, however both will be done the same way. Yes, it is a nice little mess, for sure. My only problem is how to export it. Where I have everything in tables, I am hoping it is just a matter of spitting the the data to the export files somehow without any need of convertion there because I have already done that. Everything except the facility record is in tables because we are talking about some 70K to 100K rows in the 3rd, 4th and 5th records each. Each table has the key (account_no) for each row even though they will not be exported except in the Discharge record. That is why it has to be looped, so that the 3rd, 4th and 5th records will identify the correct patient. I figured it would be like Thomas' and Daniel's advice. I have tried Daniel's way, but can't figure out which format to choose on the exported file. (Other than a flat file) I am using 2008 SSIS wizard. (This may be part of my problem.) I only have Express on my machine, but can use a machine with a full version so I can save the SSIS package. I can make another post Monday with more specifics. I can make expamples, but I can't use live data because HIPPA doesn't let you do something like that. I will need to do the export this week. Thanks, Jim
Most medical records I've worked with have a standard EDI format. Is that what you are trying to create here? If so there are several 3rd party utilities that can aid in generating these. If you go this route, Matt has the right idea in creating an app
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...
+1 - agreed. You could crow-bar a solution to almost anything out of T-SQL but that is just to prove a point rather than to be the most effective, consistent, reliable and maintainable solution.
I've gotta disagree with everyone on this one. What's an "app" going to do here? It's going to read rows from a DB, format them, and put them in a file. The OP has more than half the work done... formatting the rows and storing them in different tables. The OP doesn't have an "app" and needs to get this done. The "app" can very easily be T-SQL. You guys are going to give up THAT easily? I'm REALLY surprised at all of you on this one. What makes you think a T-SQL solution isn't the most effective, consistent, reliable, and maintainable solution if there's nothing else available to the OP? C'mon... I know all of you better than that. ;-)
@Jeff Moden - Well, he doesn't actually say there's nothing else available. If I was doing this, I would write an application to handle it, simply because to me that feels the most appropriate way to achieve the desired result in a manner that is flexible. It's not a case of 'giving up' it's simply that's what I feel is the most appropriate course of action. Even if the data is in the tables with fields of the right sizes - often these kind of interfaces require fixed / implied point field formats (have *so* been there before :) ) and really an app is going to provide a much more flexible method of achieving that. For example, I did something similar which exported all of the credit card transactions from a supermarket chain's petrol stations - and I'm glad I did it in an application. The requirement changed later on and it was easy to split out files and have differing formats both from the same data. Yes, you *can* do it in T-SQL, I just don't think you *should*.
@Jeff - thanks for the poke with a pointy stick ;) . I guess it wont be too difficult to build a load of CTEs and views that in turn shuffle the data into the necessary format with ordering and aggregation as needed and then churn that all out in one final process. Not sure how agile this will be if the schema or the need changes though. To my mind there are more people around who could unpick sections of VB or C than there are that are comfortable with complex TSQL and if the need changes after the guy who implements the solution leaves then there could be a real problem in keeping up with business needs. Its off topic and I'm not going to get anymore in depth than to say I agree an app would have been my solution of choice for this process. I may however get myself attached to AdventureWorks and see if I can make something simple. Cheers Jeff.
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. Good luck!
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][1] clause may help here. Then either create a view or a CTE that builds up the output - something like SELECT 1 AS StepID, NULL AS KeyID, data from #Step1 UNION SELECT 2 AS StepID, Key AS KeyID, data FROM #Step2 UNION SELECT 3 AS StepID, Key.... and then your final output is SELECT data from CTEorView ORDER BY StepID, KeyID 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. [1]:
http://msdn.microsoft.com/en-us/library/ms189461.aspx
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.
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? See [
http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/][1] and [
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/][2] [1]:
http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/ [2]:
http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/
