@
Converting from spreadsheets to the SPOLD format and vice versa
A. Converting spreadsheets from Excel to the SPOLD Format
The following procedure uses facilities of Microsoft Word® to convert a Microsoft Excel® spreadsheet to a SPOLD readable format. Similar procedures may be designed avoiding the use of Word and for other spreadsheets than Excel. You may download an example of application of the procedure, in the form of a zipped file containing a number of Excel and text files, which illustrate the steps in an example conversion. These example files are also referred to below.
- For each column in Excel that you wish to convert, create 4 columns, of which the third one should contain the data that you wish to convert. Empty fields must not occur: in numerical columns they should be filled with 0’s, in text columns with N/A or similar text. Please observe that the entries in Excel must adhere to the SPOLD format, e.g. an entry under CV must be formatted as a number, not as a percent. The example file xls-spold-ex.xls is an Excel file, used as starting point for the conversion in our example.
- In the first of the 4 columns, write the name of the SPOLD field that the data should be converted to. In the second column, write in each cell an increasing number starting with 1 in the first row, 2 in the second row and so on. In the fourth column enter two backslash (\\). If part of a column is not filled (as e.g. the missing entries for Exchanges.Compartment in the first 6 rows (line 4-9) of the example file xls-spold-step2.xls), the fourth column must still contain the two backslash (\\). In some cases it may be necessary to add more SPOLD fields, for example for Exchanges it may be necessary to add Exchanges.Direction, Exchanges.Input (or Output) Category and Exchanges.Compartment if these were not already part of the Excel file. In the example file xls-spold-step2.xls you can see a result of step 2. The first 3 times 4 columns contain the data for the mentioned additional fields.
- Remove any unwanted rows (e.g. column headers). See xls-spold-step3.xls
- Mark the entire table and copy to an empty Word-file. The resulting table in Word should not contain any empty rows. See the file xls-spold-step4.doc
- Mark the Word-table and use the command “convert to text (tab-separated)” in the Functions-menu. (see the result in xls-spold-step5.doc)
- Reformat the text using the Replace function in the Edit-menu, replacing first ^t\\^t with \\^l then ^t\\ with \\ and finally \\^l^t\\ with \\ (^t and ^l is special signs for tab and line break, respectively, and may be written from the keybord or selected from ‘Special’ under ‘More’ of the Replace function). For an example of the result, see xls-spold-step6.txt
- Save the Word-file as “text only”.
- Mark the full text in the text-file and copy it to the desired SPOLD text-file (e.g. a file previously exported from the SPOLD Data Exchange Software). Note that the file you copy to must not already contain entries in the field area that you wish to add data to, since this will result in duplicate lines. The resulting text-file may now be imported into the SPOLD Data Exchange Software or any other SPOLD-compatible software. For an example of the result of the conversion of the xls-spold-ex.xls, see the SPOLD file XXX00020.txt
B. Export of data from SPOLD Data Exchange Software to Microsoft Excel®A special facility of the SPOLD Data Exchange Software allows export to a Microsoft Excel® workbook of the Reference Code of a dataset together with the following SPOLD fields:
ReferenceFunction.Name
ReferenceFunction.Unit
ReferenceFunction.Amount
Exchanges.Name
Exchanges.Location
Exchanges.Unit
Exchanges.MeanValue
Exchanges.CoefficientOfVariance.
In most cases these fields are adequate for the calculations to aggegate two or more unit processes.The facility: ‘Export current dataset to Excel workbook’ is available from the File menu when the program is started with the parameter –x or /x, e.g: C:\…\sdes.exe /x from a DOS-promt or with 'Run...' in the Windows Start menu. Remember that when using this procedure to start a program, the path must not contain blanks.
This functionality and the above description was added courtesy of the EU research project BIOFIT (FAIR CT98-3832).
The SPOLD memorial site is sponsored and maintained by: