1 2 Previous Next 18 Replies Latest reply: May 24, 2012 5:09 AM by Julie Wynne RSS

Export Subset Of Clients

Mark Moran

Hi guys,

We need to expoprt a subset of our contacts\clients from SDE with the intention of importing them into another system, the most likely candidate will be Sugar CRM. Can anyone advise the best way to do this?

 

Thanks a million,

Mark

  • 1. Re: Export Subset Of Clients
    Julie Wynne

    No knowledge of the target system but any CRM system worth its salt should be able to take an import from a spreadsheet, so suggest you use Integration Engine to create an Excel file from your module – possibly joined with .

     

    You can either create a new Data Source on your IE server to do this, or use a DSN-Less connection – but don’t forget to specify readonly=No in the connect string.

     

    HTH

     

    Julie

  • 2. Re: Export Subset Of Clients
    Mark Moran

    Hey Julie,

    Cheer for getting back so quick! Why is it important to have "readonly=No"?

     

    All the best,

    M

  • 3. Re: Export Subset Of Clients
    Julie Wynne

    Quite simply, it won’t write the file if you don’t – the default is readonly=yes.

  • 4. Export Subset Of Clients
    Mark Moran

    Hey Julie,

    I managed to get connected to the DB and I think I have narrowed down the fields for the subset of clients I want to export but I don't now see a way to export them to Excel (the target)? In the adapter options I have checked them all and Excel is not showing up as an option?

     

     

    Cheers,

    M

    1.png

    2.png

  • 5. Re: Export Subset Of Clients
    Julie Wynne

    You need to use the ODBC connector , then choose the DSN-less option.

  • 6. Export Subset Of Clients
    Mark Moran

    Thanks Julie,

    I have selected that option and now it asks for a Connection String? Do you know what I should be using here? I had thought it would just now give the option to export to Excel?

     

    Thanks,

    M

     

    3.png

  • 7. Re: Export Subset Of Clients
    Julie Wynne

    Hi Mark

     

    Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=MyExport.xls; ReadOnly=0;DefaultDir=c:\Exports

     

    (obviously change the Dbq and DefaultDir values to match your file and folder name respectively)

     

    The above syntax works every time for me.

     

    Julie

  • 8. Export Subset Of Clients
    Mark Moran

    5.pngThanks so much for all the help Julie! I am nearly there.....just getting this message now? Which DB is it now trying to connect to?

     

    4.png

  • 9. Re: Export Subset Of Clients
    Julie Wynne

    It’s trying to open your Excel file.  If you haven’t already, create the Excel file with the file name and path you specified (with just column headings for the items you want to export – you will need these for the mapping stage) .

     

    Julie

  • 10. Re: Export Subset Of Clients
    Mark Moran

    Made those changes Julie and now getting this? It's complaining about the Data Source name not being found??

     

    Thanks,

    M

     

    6.png

  • 11. Re: Export Subset Of Clients
    Julie Wynne

    Hi Mark

    From the screenshot, it looks like you’ve made your Excel file the source of your data rather than the target ?   Set the Source adapter to your SDE database, then put the ODBC stuff we’ve been talking about in the Target adapter section.   Also make sure that the permissions on the Excel file are OK.

    Julie

  • 12. Re: Export Subset Of Clients
    Mark Moran

    Hi Julie,

    I thought I had it all pretty correct, especially on the source side as it's connecting to the SDE DB and pulling back the client records no problem (see the first screenshot above, it has the correct information for the sample)? I am getting the error "

    ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" and "Could not connect to database with the connection string" but the target should not need to be connecting to the SDE DB, right?? Also, the default driver is Microsoft Excel? I am running Excel 2010 on the machine I am trying all this out on. The SDE app server may not have Excel even installed on it.

     

    Should I be doing all this directly in the DB server or the SDE app server or is it OK to do it on another machine?

     

    Thanks for all your help up to this point!

     

    M

  • 13. Re: Export Subset Of Clients
    Julie Wynne

    Hi Mark

    You do this from wherever your integration service runs – the app server by default.   So the path should be as seen from the app server, and should be accessible from there.  I have created a ‘Data Import’ folder on my app server because I do regular imports of inventory through the Integration engine.  I place the source files in my folder, and it is referenced as “E:\Data Import” in the connect string for my IE package.   I write output files to the same location.

     

    The app server doesn’t need to have Excel on it, but it does need the default set of ODBC drivers installed.  I think they are just there as part of Windows so you usually don’t need to do anything special (I certainly didn’t !).

     

    Does that help ?

     

    Julie

  • 14. Re: Export Subset Of Clients
    Mark Moran

    Hey Julie,

    Just logged into the SDE app server and fired up the Integration Consol but alas am still getting the same error? Totally stumped now as to what it may be??

     

    Thanks,

    M

     

    7.png

1 2 Previous Next