Excel Tricks & Traps

COBS Tech Support
Site Admin
Posts: 101
Joined: Sat May 15, 2021 9:57 am

Excel Tricks & Traps

Post by COBS Tech Support »

When using Export to prepare data for import into CAPITAL Office, be aware of the following:

* The DBF save filter in Excel is buggy and can produce unpredictable results. For example, columns can disappear unexpectedly, or the DBF file may be malformed and considered by CAPITAL to be 'corrupt'. It is often safer to save your data file as a CSV file or as a tab delimited ASCII file.

* If you want to try creating a DBF file anyway, try saving your data as a simple ASCII file first, and then loading the data back in, and THEN saving it as a DBF.

* Excel will often save your column data based on the width of the column, not the maximum size of the data in the column. To ensure that no data is truncated, select the entire spreadsheet and double click on the column divider to stretch out all columns to their correct maximum size.

* When you save data as a text file using Excel, Excel will sometimes treat the data in those columns as numeric, even if the data is not numeric. This can be problematical if you have product codes with leading zeros, because "0010045" can end up being stored in your data file as "10045".

* Never edit CAPITAL data directly using Excel. This is dangerous and can result in data corruption. For example, if you must open the CAPITAL stock file directly with Excel, use Save As... to immediately save the file under a different name. Then use External File Import to load the data file back into CAPITAL.

* Excel will generally create correctly formatted .CSV and .TXT files for import into CAPITAL. However, problems may arise if you reload these files back into Excel to make changes to them. This is because Excel may reformat the data. For example, it's not uncommon to see Excel change the coding scheme "01-01-15" to "01/01/15" without asking you. If you wish to edit data or convert data into DBF format then the following procedure is recommended:

1. Save the spreadsheet as a tab delimited text file. This will strip out unnecessary formatting within the spreadsheet that may confuse the DBF file creation filter in Excel.

2. Reload the spreadsheet back into Excel. When doing so, the Excel Import Text Wizard should appear. Click on Next until you get to 'Column data format' and select the 'Text' option. Then hold down the SHIFT key, scroll to the far right and tick on the last column of the Data Preview to select all columns.

3. Press Finish to load all your data as 'text' (and therefore inform Excel not to reformat).