Exporting to/Importing from Excel

<< Exporting field data to t ...
Back to Portfolio FAQ index
Handling ASCII #29 charac ... >>

Manual: v7 - Pages 132-134

Need to Export info to use in an Excel spreadsheet?

You want to use the "Export Text File" function (on the 'File' menu) for this. You get a tab-delimited file, with 1 record per line and each field on the line tab-delimited.

You can which you can then import the file into Excel, which has an explanation of what to do in its Help and there's also an Import Wizard. For export to text see the manual pages 127-128 (v6) or page 184 (v5). For a little animated demo of the text export process see here - it is a little streaming Java Viewlet (v3+ browser with Java enabled required).

The "File - Export - Text" menu selection works well. Outbound from Portfolio, it asks you which columns (fields) to export and dumps to a CSV file. Note that export will only grab the selected entries, so if you want the entire Gallery selected, remember to perform the "Edit - Select All" menu selection. Once exported Excel recognizes this format and it will pop up a wizard from which you can select the defaults to load into a spreadsheet. The first row are the field names. Export order is preserved from Gallery/Selection order.

Need to import Excel data?

Returning info to Portfolio (e.g. if you changed a field value, etc.) you import the CSV, and Portfolio will need to know which field to use as the key. Best to use the filename for this purpose (remember to include it in the original export).

Portfolio v6 (? v7) can have problems importing tab-delimited data from Excel. Data from some fields in the Excel tab-delimited output is imported into incorrect fields within Portfolio . The incorrect fields are empty in the original Excel spreadsheet. The problem seems to arise (or one cause of it is) when the last column(s) of a row have no data. Excel doesn't export tabbed empty cell data - as it should; the Portfolio import routine then compounds Excel's error by repeating the last valid column's data across all the remaining fields in the original 'row'. Putting a "space" in the empty fields within Excel, will fix the problem.

How to avoid this? Force a correct export from Excel. The best solution seems to be to add a dummy column to the right of the real data and put a space - or obvious string like 'XXXXX' - in the first data row and drag by example down all data rows. As the last exported column now has data, all the empty fields in columns to the left of it are correctly exported as empty - i.e. a tab is generated for each empty column/cell. Now, when you import the text file just don't map the 'dummy' field; ignore it, it has done its job by fooling Excel into doing a correct data export.

It is not certain if the problem occurs in all Office/OS versions (it was reported on the Mac OS) but the above does no harm so it is a safe defensive measure.

A separate issue is that if you try importing tab-delimited data from Excel into Portfolio you may get an error message and no result. If so either use the Windows client (if available!) which has no such problem or open the text file with BBEdit and run the "Convert to ASCII" plug-in from the tools menu (don't know if BBE Edit Lite or Text Wrangler has the plug-ins and in BBEdit 8 the plug-in is now part of the app). v7 now uses Unicode as the efault input format - see here.

Question: Exporting to/Importing from Excel [FAQ00031.htm]
Last Update:- 31 May 2006

<< Exporting field data to t ...
Back to Portfolio FAQ index
Handling ASCII #29 charac ... >>

Quick Search of PortfolioFAQ (word, words or phase): or try the Advanced Search

User-to-User Forums  |  Report error/typo/broken link  |  Request new topic  |  Ask a question

Site and articles © Mark Anderson 2001-2007 - Visit my home page

This FAQ is created and maintained using
Friday logo
Friday - The Automatic FAQ Maker