I am a DBA of about 7 months. The company that I work for has put me in charge of changing our sales import process. Up until 2 months ago, I use to import files from SQL Server 2005, perform ETL with FileMaker Pro 11.2, then export 2 files, a Quickbooks "cash" file and a Quickbooks "invoice" file. These files were exported in .iif format (Intuit Interchange Format). We have a new point of sales system and FileMaker Pro is now obsolete. I have created a rough draft of the cash and invoice files. I must now find a way to add the quickbooks format using SQL Server 2008 R2. I have attached 2 pictures, kind of like a before and after picture. The Filemaker process is somewhat broken, so you need not pay any attention to the amounts. You can ignore the memo field also in the after picture.
Quickbooks requires that you have a header row (the first 3 rows in the 2nd picture), then a TRNS for the first transactionline, and an SPL for any additional transaction line per line item. At the end of the trasnaction line item, an ENDTRNS is inserted. When a new line item appears, the TRNS is put at the beginning of the transaction and the process repeats. If there is only one transaction line for a line item, you will only have a TRNS and an ENDTRNS. When the transactions end of records is reached, a final ENDTRNS is inserted, and thats it. I need to know if it is possible to create the TRNS, SPL, and ENDTRNS in SQL Server 2008 R2. You will see that in the formatted file, when there is a change in store (DWF03, DFW04, etc) you will see a change in the transactions. adding the cash sale to the rows is no problem. I just need to add the first two header rows, an !endtrns row, then the actual line item formats (the trns, spl(if there is more than one line item, in my example it is) and an endtrns). Anyone's knowledge of this matter would be greatly appreciated. My supervisor and I had a talk this week, and if anyone is familiar with this type of process, or has an idea of how to make this process easier or better please let me know. Thanks!
asked Apr 14 '12 at 09:05 PM in Default
the details seem straight forward and I think that reporting services/report builder or SSIS might be the best way to get this done. Report Builder will allow you to build up the expected results in an easy enough way. You would define groupings by the class column and then give the groups a header and a footer to give you the split and allowing you to add the "ENDTRNS" line along with the entire header lines.
Be that as it may, I have neither system at hand and thought that this can also be done in T-SQL (it is sunday evening and nothing is on TV!).
Give the following a try to see how it works for you:
answered Apr 15 '12 at 08:57 PM