Data Format for Files

 

From your accounting package, create two reports: an Income Statement and a Balance sheet.  Have each report written to a file with the following formatting:

 

•      Preferred file format: CSV (it processes faster).  Note: If you try to load an XLS, then Excel must be installed on the same PC as SurvivalWare, and it processes a lot slower.

 

•      First month:  For the first month, use January of a prior year, going back as far as 2 to 5 years, depending on how much history you want to have available. (If you’re on a different fiscal year, use your own starting month.)

 

•      Last month:  Use the most recent “closed” month.

 

•      In column A: This column will contain line item names.  They are best if they are unique.  A combination of the account number and description is ideal.

 

•      In row 1:  Row 1 should contain the date (Months or Years) for each of the columns of data starting in Column B.

 

•      In row 2 thru all rows for the Balance Sheet:  Each column should contain the ending balance for that account.

 

•      In row 2 thru all rows for the Income Statement: Each column should contain the activity for that account for that month.

 

•      Include the following data (rows) so as to provide a data validity check:

 

Net Income

Total Assets

Total Liabilities

Total Equity

 

Here’s an example of the balance sheet file created directly from QuickBooks:

 

 

 

Here’s an example of what was done to an XLS file to create the CSV files in an ideal format:

 

Step 1: If you have extra text spanned over several columns, create a column that concatenates several columns into one row name (in this case columns B, C, and D  were concatenated to column E).

 

 

 

Step 2: Copy columns E through J for all rows, create a new worksheet (Insert/Worksheet), and do a Paste Special/Values to this new worksheet.

 

 

 

This is what the new worksheet looks like:

 

 

 

Step 3: Save this as a CSV file. (File / Save As, then change file filter to CSV Comma Delimited (*.CSV))

 

 

Sometimes, you’ll get the following message when it is time to do a roll over: