Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / integration manager ad...
Microsoft Dynamics GP (Archived)

integration manager advanced ODBC

(0) ShareShare
ReportReport
Posted on by Troy Cantrell

Hello everyone, i am having trouble with an integration.  I am trying to import our AMEX bill into Dynamics GP via integration manager.  I want to use advanced ODBC against a text file.  I have seen video's on how to do it and everything seems to be working except that when you are building the query no columns come through.  Has anyone had this issue / have any suggestion?   I also tried using an Access database instead of text with the same non-functioning result.

Running GP 10, installed the latest IM service pack, latest MDAC.

*This post is locked for comments

  • Suggested answer
    bobbyboy Profile Picture
    bobbyboy 10 on at
    RE: integration manager advanced ODBC

    Hi, Troy.  I had the same problem.  The issue was that I had spaces or special characters in one or more column names and was not delimiting them with brackets.  You can have one such occurrence among 100 correctly defined columns and the parser will still tell you you have no columns.

    Column Name  >>>>>>>>> SHOULD BE >>>>>>>>>> [Column Name]

    And that means everywhere.

    Hope this helps.

    Bob

  • Troy Cantrell Profile Picture
    Troy Cantrell on at
    RE: integration manager advanced ODBC

    What i do is create a spreadsheet with 3 tabs.  1 is the final export file format, 2 is the interim working file, 3 is the raw export (amex for us).

    in spreadsheet 2 I have all the column headers i need in the final file i give to integration manager.  I hard code the distribution type in the spreadsheet.  So tab 2  are a combination of formulas, pulling from the raw data tab, and hard coded values, like Dist Type.

    Finally tab 1 is cut / paste special, values only.  That becomes the TXT file i hand to integration manager.

    if you email me i can send you a sample file.  tcantrell@visitcalifornia.com

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: integration manager advanced ODBC

    The out-of-the-box AP integration requires two files (one physical file, but a header and a detail) with the detail file having a 6 to denote "purchases" and 2 to denote the accounts payable. My file is basic with just vendor, description, date, gl for the expense side and amount and I am trying to have it default the credit/accounts payable account

  • Bill Campbell Profile Picture
    Bill Campbell 22,647 on at
    RE: integration manager advanced ODBC

    SHiggins, can you explain what you mean by 'change the file to designate the type 6 and type 2'

    Reason I ask is I am wondering if you might be able to have the system evaluate that value and convert it from the TEXT format to the numerical value - as you are doing the integration?

    Might be all wrong for so many reasons, but, it seems like I have done something similar in the past.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: integration manager advanced ODBC

    Ugh - did you have to change the file to designate the type 6 and type 2 and the correct gl accounts? I'm trying to minimize how much work my user is going to have to do to the file. Thanks!

  • Troy Cantrell Profile Picture
    Troy Cantrell on at
    RE: integration manager advanced ODBC

    Sadly i ended up not using the ODBC and had to resort to the standard integration using an export file and integration manager.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: integration manager advanced ODBC

    I am running into the same issue with the columns not showing - could you please share how you resolved the issue?

    Thanks!

  • Verified answer
    Troy Cantrell Profile Picture
    Troy Cantrell on at
    RE: integration manager advanced ODBC

    thanks for the offer Leslie.  i am pretty sure the file structure is correct.  

    I did find another way to handle it, requires a bit more work on our end to prep the file, which is what i was going to do with the SQL statements.  Then i just use a standard text integration that  i built.

    without too much work now i can add some columns, add the offset to AP for each card and import 300 lines of transactions from the AMEX download file right into GP.  Something the accounting team was manually entering.

    Thanks everyone for the assistance.

  • L Vail Profile Picture
    L Vail 65,271 on at
    RE: integration manager advanced ODBC

    Can you use a text driver and hook it to Excel? I'm just trying to confirm the file format is OK. Check the header selections as well. Also, make sure the correct delimiter is selected.

    If you want, you can e-mail me the .txt file and I can try to build a data source from it.

    Kind regards,

    Leslie

  • Troy Cantrell Profile Picture
    Troy Cantrell on at
    RE: integration manager advanced ODBC

    Hi Leslie,

    Yes, if you click the builder button the file directory is correct, you can see the file.  if you select the file the columns populate and you can select them.  You can NOT however add a criteria, it throws an error. When you finish it builds the query in the window as well.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans