On 09/17/2014 06:08 PM, John DeSoi wrote:
>
> On Sep 17, 2014, at 11:52 AM, Martin Waite <waite.134@gmail.com> wrote:
>
>> You are right that I need an intermediate step. I will probably use a CSV parser that is liberal in what it
accepts,but writes out strict CSV data suitable for postgres.
>>
>
> If you find such a utility, please share. My clients love Excel, but it takes perfectly valid CSV files and makes
themunreadable by Postgres. In particular, Excel saves rows with fewer columns than the header header row if the cells
areempty. It also mangles valid UTF-8. I often take Excel CSV files and re-save them from Open Office to fix them for
Postgresimport.
Have you tried Save As Unicode:
http://www.ablebits.com/office-addins-blog/2014/04/24/convert-excel-csv/#export-csv-utf8
I vaguely remember using Access to pull in an Excel file and saving CSV
from there. I also seem to remember an option when saving a text file to
create an export template that allowed you to specify the csv options
and formatting. This was with the Pro version of Office and the data
tools installed.
What I do now is use the Python xlrd module to read in the Excel file
and then the csv module to output a CSV file.
>
> John DeSoi, Ph.D.
>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com