Expand my Community achievements bar.

SOLVED

excel importtextdata

Avatar

Level 3

HI Everyone,

1. I want to make an excel spreadsheet and then save it as some sort of delimited text file. I want this to be the "database". Let's say that this database will contain a list of countries and capitals, like:

Russia, Moscow

Germany, Berlin

USA, Washington DC

2. I want a form that has just two fields and a button on it...Country is a dropdown and Capital is a text field. I'll configure the dropdown in Designer with the choices I need, like Russia, Germany, USA. The text field will be initially blank.

3. I'll put the text file and the pdf in the same directory.

4. I want the user to choose, let's say Russia. Next the user clicks on a button that says "look up". When he does that, the text field displays Moscow.

5. I want to use a data source that is easily created/maintained in a common program, like excel. I could even accept an answer that allows me to copy the data and paste it into a script object or something in the form. I just need to know how the lookup works in that case.

6. I have been reading the forums and it seems like importtextdata is a possibility, but I do not know how to get the logic on the button. I mean...I want to wait until the user chooses Russia or Germany or whatever, then clicks the button, then the lookup happens and then the capital field is filled in with the correct capital.

My actual task does not involve countries and capitals, but if someone could answer it in these terms, then I think I could make it work in my actual form.

Many thanks,

Joe

1 Accepted Solution

Avatar

Correct answer by
Former Community Member

This is doable but realize that when you access an outside reource like this you will have to use an ODBC data source to access it. This means that your solution will only work on windows machines. Also using ODBC connections is not supported by the default Reader. You will have to buy Reader Extensions server to Reader Extend your forms to extend Reader for this functionality. Even after all of that your users will have to be on the same network as the Excel file and you will have to configure a DSN on each users machine (this can turn into a mainetance nightmare).

For these reasons most people will hardcode the values into the form.

Hope that helps

Paul

View solution in original post

6 Replies

Avatar

Correct answer by
Former Community Member

This is doable but realize that when you access an outside reource like this you will have to use an ODBC data source to access it. This means that your solution will only work on windows machines. Also using ODBC connections is not supported by the default Reader. You will have to buy Reader Extensions server to Reader Extend your forms to extend Reader for this functionality. Even after all of that your users will have to be on the same network as the Excel file and you will have to configure a DSN on each users machine (this can turn into a mainetance nightmare).

For these reasons most people will hardcode the values into the form.

Hope that helps

Paul

Avatar

Level 3

Hi Paul,

Thanks for your advice. I believe you. Very good. I will use XML, not Excel.

Now I must say that I am completely perplexed about how to do an XML data connection. I get the idea, that's all.

Have you got a really simple connection example with maybe two fields that you could send me? (Could you send the pdf and the xml file as well? or the text for the xml?)

I have been trying and trying with simple examples from the Help, but no luck. I think I want to use the XML file to import "sample XML data" , but it seems like the Help article is biased towards using a schema.   I do not know how to make a schema.

The furthest I have goten is shown below. The drop down actually "works" like a drop down, but it has no data.

Many thanks,

Joe

problem.gif

Avatar

Former Community Member

Here is a simple example. Just put the PDF and the xml file in teh same location and when you open the PDF in Designer and Preview you will see how it works. The xml file has a node for the Name and the UID. I setup the DDList to display the Name but report the UID for the item chosen. This is done in the DDLists Specify Items Value on the Object/Binding palette. Once you click on that link a dialog opensd up and allows you to set the nodes that you want to display and report. There is code on the exit event of the DDList that put the messge up (showing you how to get at each value when the user selects it). Note that I created this XML structure but any type of structure will do. There is a parameter on the File/Form Properties/Preview tab that allows you to pic the XML file that you want to merge with the form in Preview. This will not do that when you display the finished PDF on your desktop. You will have to import the xml file to get the DDList to populate.

Hope that helps

Paul

Avatar

Level 3

Hi Paul,

Thanks very much for this effort. I think I can construct one of my own  using this example.

I was not quite able to get it to work though. I did a Preview in LC and it choked. I tried it three times and it always caused LC to fail.

I opened the pdf in Acrobat and imported the data from the xml file. It worked fine.

I understand what you mean about specifying the values that are reported based on the values that are chosen, however, in my LC, I see that my ability to do this is grayed out. Any idea why this would be happening? (See picture below)

Anyhow...good concept...I am going to try to build the form I really want based on what you have shown me.

Thanks again,

Joe

problem.gif

Avatar

Former Community Member

If you send your form and data file to LiveCycle8@gmail.com I can have a look and see what is going on. Please include a description of your issue with the email.

paul

Avatar

Level 3

Hi Paul,

No problem. I think I see the problem. I marked your post as the "correct answer".

Thanks so much.

I am going to build a similar file and xml data and see if I can make it work. If not, I'll certainly email you the attempt and we can go from there.

Thanks again,

joe

The following has evaluated to null or missing: ==> liqladmin("SELECT id, value FROM metrics WHERE id = 'net_accepted_solutions' and user.id = '${acceptedAnswer.author.id}'").data.items [in template "analytics-container" at line 83, column 41] ---- Tip: It's the step after the last dot that caused this error, not those before it. ---- Tip: If the failing expression is known to be legally refer to something that's sometimes null or missing, either specify a default value like myOptionalVar!myDefault, or use <#if myOptionalVar??>when-present<#else>when-missing. (These only cover the last step of the expression; to cover the whole expression, use parenthesis: (myOptionalVar.foo)!myDefault, (myOptionalVar.foo)?? ---- ---- FTL stack trace ("~" means nesting-related): - Failed at: #assign answerAuthorNetSolutions = li... [in template "analytics-container" at line 83, column 5] ----