Export Contacts to CSV - line break issue

[jabhawk]

CE 4.5

I have a problem with Exporting Contacts, Accounts, etc where there are fields containing embedded line breaks. The export to CSV makes it impossible to open in Excel directly without editing the resulting file first. This is seen in the Address and Description fields.

Is there an add-on or other utility that would strip or replace these characters from the output before the user gets the file?

I have to be able to dump the selection into pre-written imports with little or no user interaction. We do not have direct access to the database so ODBC is out.

Jon Black
Hawk Services, Landing NJ

[Angel]

Take a look at using something like Talend Open Studio, SnapLogic or Jitterbit.

The other danger with these types of exports is that someone could actually type a comma into one of the fields and that too would throw off the export. The description (and other large text areas) are especially prone to that problem.

[Greetal]

Take a look at using something like Talend Open Studio, SnapLogic or Jitterbit.

As Angel says, you can use an ETL tool like Talend Open Studio. It is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user.

What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. For more information: http://www.talend.com/

[jabhawk]

I will be looking at the tools discussed.

As a stopgap measure is there any good examples of php scripts to create a csv or XML file from the Sugar tables? I would want to pull only selected columns and include custom fields.

This is me being lazy, i'm just so swamped I am hoping to short-cut developing these custom extracts. Our live database is on an external server that we do not have direct access to with ODBC or other tools.

I am actually looking to setup a mirror database on a server inside our firewall so that we can pull directly from the Sugar data. Not the best solution but may be the only one for now. I have not tried doing a full SQL dump load yet but will try this weekend.

Jon

[Angel]

I don't know about the other products, but I know that with SQLyog, you can run ad-hoc queries and then export the results to CSV, XML, HTML and other formats.

You wouldn't need to do any programming at that point.

[kuske]

If you import the csv-files to access, you can define the separator char and the quotes.
From access you export them to any other file fomat.

[Greetal]

With Talend Open Studio, you can cut the programming and only go through the software's "jobs". Very useful and usable by most people, even with not much tech background, you can select columns and export them into csv with Talend.