Export Data

[Chunky]

Hi,
I am really struggling exporting data from Sequel Pro. I know i have
fields that have html in them so i'm assuming this is the issue but i
can't get anything useful into a csv or tab delimited file for people
to use in Excel or similar.
Help!
Thanks

[Jakob Egger]

CSV Export is working fine for me.

What happens when you try to open the file in Excel? Do you get an empty
document, or do you get garbled data? Are only the fields containing
HTML data incorrect? Do you see any data at all from the exported table?

Excel is pretty picky about delimiters. For example, some localized
versions require semicolons as delimiters and commas as decimal
seperators (which sequel pro doesn't support). You may want to open your
file as a TEXT file (rather than CSV) in Excel, which allows you to
specify decimal separators etc.

Did you try looking at the resulting file with a text editor?

[Rowan Beentje]

Hi Chunky,

I think (hope) that Sequel Pro produces valid CSV files, but it's quite possible that certain types of content aren't by default escaped in a format that Excel likes...

It might be worth copying one problem row into a new table and exporting it as CSV, and then also setting up that row in Excel and exporting from there in CSV. You can then compare the files and see if anything is different - I think (off the top of my head) that Excel often escapes the quote character with another instance of the quote character, rather than using a backslash. You may be able to play around with the CSV export options to get it to work...

Please let us know the outcome - if it's a bug in Sequel Pro, or if different export settings are required! A new export interface is being worked on, and having settings for Excel compatibility would be quite useful...

Cheers,
Rowan

> --
> You received this message because you are subscribed to the Google Groups "Sequel Pro" group.
> To post to this group, send email to seque...@googlegroups.com.
> To unsubscribe from this group, send email to sequel-pro+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sequel-pro?hl=en.
>
>

[Chunky]

I've tried various delimiters in various combinations.
tab, comma, semi colon delimited
various characters for the fields enclosed
various characters for the fields escaped by
and various lines terminated by options
as a text file and a csv, all have the same issue.

I/Rowan think its due to the extra line feeds in some of the "Text"
fields.
Basically the data that has say a couple of line feeds after the text
breaks the record and starts a new record in the csv.
The html in some of the fields doesn't seem to be the problem as far
as i can tell, although it might be once the line feed problem is
cleared.

[Jakob Egger]

Okay, I just tried it. There is a problem with linefeeds:

MacOS X and Unix use a 'newline' character (NL) to designate new lines.
Windows (and Webservers) use two characters to designate a new line: a 'carriage return' (CR) and a 'newline'

Sequel Pro escapes only newlines. Thus if your database contains Windows-Style newlines (CR-NL), Sequel Pro escapes the newlines with a backslash, and you get the sequence CR-backslash-NL, which in turn confuses MS Excel.

Sequel Pro should probably convert CR-NL to NL before escaping it, so that a valid MacOS text file is the result.

All the text input fields in Sequel Pro allow using either CR, NL, or CR-NL for newlines. You will see the difference in hex mode (CR becomes 0D, NL becomes 0A).

As a workaround, you can use search-and-replace in a text editor to repair the broken CSV file.

If you only have linefields in a few database fields, you can also use a command such as the following to create a query result with MacOS linefeeds:

SELECT id, REPLACE(html_field, UNHEX('0D0A'), UNHEX('0A') ) `html_field` FROM some_table

Here UNHEX('0D0A') is a windows linefeed (CR-NL) and UNHEX('0A') is a Mac/Unix linefeed (NL).

[Jakob Egger]

Hmm. I just figured that you probably have mixed linefeeds in your text field. (Some windows style CR-NL, and some mac style NL). Thus, no matter what line endings you choose in the Sequel Pro export screen, you'll always have troubles.

Try using the code from my previous mail to repair your database... eg. run something like:

UPDATE my_table SET html_field = REPLACE(html_field, UNHEX('0D0A'), UNHEX('0A') )

Then you'll have only one type of linefeeds in your DB.

(make sure you have a backup before you try this!)

Best regards,
Jakob

On Jan 7, 2010, at 14:18 , Chunky wrote:

[Rowan Beentje]

Nice work Jakob!

> Sequel Pro escapes only newlines. Thus if your database contains Windows-Style newlines (CR-NL), Sequel Pro escapes the newlines with a backslash, and you get the sequence CR-backslash-NL, which in turn confuses MS Excel.

One minor correction - Sequel Pro only escapes characters used as separators or line endings - so NL is escaped if that is set as your line separator.

Excel, however, doesn't escape newlines or carriage returns at all; simply having them wrapped in the quote character seems to be enough for it. But I haven't yet tried opening - with Excel - a CSV file with newlines in it that Excel exported itself; will try that later.

Certainly something we'll have to work on...

[Anthony York]

Any news on exporting issue?

Thanks
Anthony

[Rowan Beentje]

Hi Anthony,

I emailed you on the 10th - possibly it got caught by your spam filter! Here's the email again:

Begin forwarded message:
> Date: 10 January 2010 16:33:14 GMT
> To: Anthony York <snip>
> Subject: Re: [sequel-pro] Export Data / Sorting
>
> Anthony,
>
> Opening the CSV in Excel 2008 actually almost works for me - the only issue is additional "/"s in the description field, but otherwise all the columns contain the information they should! So it looks like Excel's CSV parser must have been mostly fixed recently; which version were you running into problems with, so I can fully replicate the issue?
>
> Thanks,
> Rowan

[Chunky]

I'm using excel 2008 V12.2.3
The problem definitely seems to be the problem but the /'s are generated by the export aren't they because they see a carriage return or something?
And this breaks it when i try to open in excel.
Thanks
Anthony