Field Mapping

[John Cunradi]

I am writing an app to run in the A5 run-time. Currently V 10.5

My customers will be presented, by their customer, a file containing information about the inventory of their customers' store...item numbers, descriptions, prices, etc.
The file could be any number of data formats (Text, Excel, DBF, etc.). And the field names, if they are present at all, will not match anything in my app.
There is no way to know in advance anything of the data they will receive from their customer

I need a way that my customers can import that data into an existing table in my app.
They are not computer savvy and, since it is a run-time, the standard import is not available.

A few years back someone helped me in a similar situation but the scripts and such were password protected and he wouldn't give me the password.
While grateful for the help solving the immediate problem, I really didn't learn anything from it.

Ideally, if someone has some code or scripts available to do this sort of thing, I can dig into that and learn how to do it by tweaking their code to my situation.

Any help would be appreciated.

Thanks.

[Ray in Capetown]

Re: Field Mapping

If by "standard import" you mean the import wizard - then its easy for you to take a sample of each client's clients' file , make an import and save the resulting xbasic code, named appropriately in a function or script.

[Stan Mathews]

Re: Field Mapping

I don't have access to a runtime machine at the moment. What happens in the runtime if you have a button with the code

a5_importasciigenie()

?

[John Cunradi]

Re: Field Mapping

If I know what the structure of the data is, I can easily create an import and then have a button to make it work.

The trouble is that my customers need to be able to import the data without my help or have a pre-defined import button.
Many of my customers are "Computer-Challenged". Even basic things like Windows-Explorer or file naming confuses some of them.

It was already done once before on a password protected script on a previous app.

[Ray in Capetown]

Re: Field Mapping

Yes John
I do understand that there was a script as you describe.
I also understand the technical shortfalls of clients. Besides, its not their job. That is why you and I exist in this business.
It would have quite an accomplished set of well tested complex codes - and provide understandable options to the client when alternative possibilities exist.
Probably explains why it was password protected.
You could possibly however follow what I described earlier, get eyes on each only once. Easier and probably just as (or more) financially viable, than trying to develop a catch-all without the necessary programming skills and time.
Only my practical advice - which may be out of place not knowing your capabilities at all. So said with respect.

[John Cunradi]

Re: Field Mapping

import1.jpgimport1-b.jpgimport2.jpgimport3.jpgimport3-b.jpg


Stan,

the import ascii genie brings up the import genie as you'd expect. Thanks.

I think this is a bit confusing to my customers. I've attached some screen shots of the password protected app that might illustrate what they are using now and how it's simpler for them to use.

You'll notice two of the screens are shown as they first appear and then after a file or the fields are selected.

[John Cunradi]

Re: Field Mapping

Ray,

no offense taken. I appreciate your input.

My customers are often given these files at the last minute and there's no way for me or anyone else to be available for them at the exact moment they need it. They might need it any hour of any day.
The odds greatly increase that they will need it at just the moment when I'm overworked the most.

[Ray in Capetown]

Re: Field Mapping

John
That looks a lot like an import genie. The same questions are required for both.

If your clients use that one already they could probably handle the Alpha genie , no?
It's no more complex than that one, once the browsing , saving stuff is removed.

I would use the xbasic code and modify it to automate the lookup, testing and final destination of the data for them in there.
Back to what Stan suggested.
Make your own home grown version.

[Tom Cone Jr]

Re: Field Mapping

John,

In my view its not reasonable for your clients to expect you to be able to import any number of records from any number of related data stores, of varying data formats, with or without fieldnames or datatypes. Surely there are some parameters that could be used to narrow the huge range of possibilities your initial post suggests. Maybe it would be productive to find out what specific data formats are possible or preferred from the customers of your customers.

[John Cunradi]

Re: Field Mapping

The situation is this; my customers' customer will give them a file and say "make this work". They don't care what needs to be done to make everything go.
The control over the format of the file is even further beyond control. That's the venue of the POS (cash register) system and they are notorious for not cooperating with 3rd parties.

Even if I can, somehow, train my clients to do the imports, they have to train their employees.

There are some standard data formats and I will add import buttons to the form to handle those. The trouble is that there are, literally, a thousand different systems out there and variations between versions within the same systems.

Every A5 developer is a database person, but databases freak out a lot of people. The A5 import is intimidating to them and requires them to understand the data format they are given
whereas the password protected script that I've been using lets them play with the data until it looks right.

I do have an idea that I'd like to test to see if I can make this go.
Is there a way that I can pre-set some of the parameters of the import genie (through XBasic maybe?) Specifically, to import into a new table and, hopefully select the table name as well ?

[DaveM]

Re: Field Mapping

Correct me if I am wrong, but been doing import for awhile(hate it). I have one table built just for this. It is built so the user can click a button to import data to this table. Once imported, the user can check the data for correctness and go on from there.
Since there is little of the imports and the data types are know(there are 3 file types), there are 3 buttons to accomodate.

I am not sure at all how to import anything like what you are suggesting. Possible a script to figure out what the file type is would be a start? Then choosing a premade script to do the import?

[Stan Mathews]

Re: Field Mapping

Is there a way that I can pre-set some of the parameters of the import genie (through XBasic maybe?) Specifically, to import into a new table and, hopefully select the table name as well ?

You can capture the xbasic of an import operation you have designed to import into a new table. You can then modify the code as necessary....

like

import_filename = filename_decode("c:\temp\salesrpt.csv")
could be
import_filename = ui_get_file("File to import","CSV(*.csv)|DAT(*.dat)|TXT(*.txt)","C:\temp\\","X")


and
import_destination_table = "h:\executive\hdb\sls_mast.dbf"
could be
import_destination_table = ui_get_file("New table name","DBF(*.dbf)","C:\temp\data\\","N")+".dbf"
import_destination_table = strtran(import_destination_table,".dbf.dbf",".dbf") 'if user enters .dbf remove it

[John Cunradi]

Re: Field Mapping

That gives me some good code to start with. I'll let you know how it goes. Thanks.