Import into existing table (Newbie - sorry)

[fsinger]

I have built my database and now want to import data from various sources into my existing tables. I can't seem to find a "mapping" feature so I can map a table into my exiting tables. Since I have built reports, forms, etc. with one to many links, etc., I can't just plug in an imported table as Alpha5 seems to want me to do.

I really have searched the documents and FAQ. Can someone get my pointed in the right direction??

Thank you,
Fred

[Tom Cone Jr]

Re: Import into existing table (Newbie - sorry)

Fred, take a few minutes and tell us in more detail where you're stuck. Importing data into existing tables is usually done using an "import operation". Why isn't that working for you? Alpha imports records, I'm not sure what you mean you say that you can't just plug in an imported table... what's an imported table?

[Selwyn Rabins]

Re: Import into existing table (Newbie - sorry)

I have built my database and now want to import data from various sources into my existing tables. I can't seem to find a "mapping" feature so I can map a table into my exiting tables. Since I have built reports, forms, etc. with one to many links, etc., I can't just plug in an imported table as Alpha5 seems to want me to do.

I really have searched the documents and FAQ. Can someone get my pointed in the right direction??

Thank you,
Fred

have you looked at the Append operation? this is used to append new records to an existing A5 table.

[fsinger]

Re: Import into existing table (Newbie - sorry)

Tom,
Thank you for taking the time. I'm going to try Selwyn's suggestion to use "append" before I go any farther. Luckily I have my entire application written in Filemaker Pro, so I'm not losing any work time. I can just see that Apha5 has a lot more potential than Filemaker - just more learning curve at this point.

Fred

[CALocklin]

Re: Import into existing table (Newbie - sorry)

Since I have built reports, forms, etc. with one to many links, etc., I can't just plug in an imported table as Alpha5 seems to want me to do.

If the table structure of your existing tables is the same as the structure of the ones in your new application, then you can "just plug in" the other table. Otherwise you can't.

By "table structure" I mean the name, type, and size of the fields.

The reports, forms, etc. are completely separate from the data. To copy the data, you only need to copy the .dbf files and any .cdx or .fpt files with the same name. The reports, forms, etc. are all in the .dd* and .se* files.

So, IF you have valid .dbf files that have the same structure as the ones in your application, then you can simply copy them into your application. However, if the structure is not the same then you may have problems because the .dd* files won't match the data files. You might get away with something like a different field width but different field names (which includes the same names in a different order) or different field types will probably cause problems. If the structure is not the same, then an append operation is your best bet.

Some people even like to append the data even if the structure IS the same. That way anything that is "bad" (fields don't match or data doesn't match the field rules) will cause an error and, with a bit of work, you will be able to start out with good data once the append is complete.

Just to pick a nit and provide a little better understanding of the terminology: I don't believe Tom's suggestion to do an Import is wrong but it takes a more thorough understanding of what's going on to fully understand the distinction between that and an Append. A 'classic' import operation just imports data into a new table. If you have selected the option to "import to an existing table" then the initial import will be done into a temp table and that will then be appended into the final table. After the append is complete, the temp table is deleted. Since this is usually handled by a genie, the genie takes care of this behind the scene and it isn't obvious - but it can easily be seen by looking at the xbasic behind the operation. In your case, it sounds like the data is already in a .dbf file so it's only necessary to run the Append.

Note that the previous paragraph refers to a 'classic' import. I'm not sure exactly what happens with the newer ADO/AlphaDAO imports.

[fsinger]

Re: Import into existing table (Newbie - sorry)

I'm afraid that Alpha5 may not work for me from what I have learned from everyone so far. Here is my situation in more detail so that someone might offer a suggestion. BTW, I tried the "append" route, and it requires a .dbf file to start with. And, as CAlocklin indicated, you have to be working with a .dbf table with the exact same structure!

I have a company that examines commercial banks. I have developed an exam application (in Filemaker Pro) that has two main tables (there are more tables, but for this question I'll limit it to the two involved). The tables are BORROWERS (the parent) and NOTES (the child table). This is a one to many relationship because the borrower can have several notes. So to make it simple BORROWER has these fields: borrower_name, borrower_number, shortname. NOTES has these fields (among many others): borrower_number (the link field), note_number, note_date, note_balance, note_maturity, etc.

Based on these tables I have written a number of reports, forms, etc. for the examiners working for me to use in the field when reviewing a bank. At the start of an examination I request a "pullfile" from the bank's computer service company. These pullfiles come in dozens of variations, some might be ASCII comma delimited, Excel files, and several variations of the same. The fieldnames vary all over the place, the dates might be in Julian date format (or not), just about anything. I spend some time getting it cleaned up, but basically I need to "import" or "append" that data into my existing database tables as noted above.

Filemaker Pro has a neat "mapping" function, where you can match or map the fields one by one into your tables. The raw data table is on the left side field by field, and the target table (my NOTES table, for example) is on the right side. I then match the raw table to the target table field by field. It doesn't matter what the fieldname is or the format in the raw data, it will be imported to the fieldname and format of my target table.

Doesn't Alpha5 have that ability? Or something like that? Isn't it common for someone to have to "populate" their database tables from an outside source and go from there?

I really want to start using and converting to Alpha5, but I am getting worried!

Fred

[Stan Mathews]

Re: Import into existing table (Newbie - sorry)

Isn't it common for someone to have to "populate" their database tables from an outside source and go from there?

An import operation is what you want from your latest description. An import operation to an existing table is actually an import (to a temporary table) followed by an append to the existing table. You will have to define the operation each time you receive a file with a new layout. It seems as if that is what you are doing at present if by another name.

[CALocklin]

Re: Import into existing table (Newbie - sorry)

... as CAlocklin indicated, you have to be working with a .dbf table with the exact same structure! ...

That's not what I said at all. The append operation does NOT require the exact same structure. For an append operation, the field names can be different, the lengths can be different, and, with a bit of work, even the field types can be different. Of course, if you append a 20 character field into a 10 character field, it will get truncated.

The only time you need to have the same structure is if you are going to completely replace an existing table with a new table of the same name. By that I' mean something like going to Windows Explorer and copying the file from another folder into the current application folder. This is not an uncommon thing to do with data files when developing a new or updated application but it would be rather unusual on a day-to-day basis.

What you want to do can easily be done in A5. It's just done differently than it was in your other program. If you had been using A5 for years and then switched to your current program, you would probably be complaining and saying it couldn't be done because it didn't work like A5.

A5 DOES have mapping capability for append operations. For Import operations there is nothing to map to because the import is only from an outside file to a new .dbf file. Therefore, the field names in the resulting .dbf file from an import operation are simply "assigned" (rather than "mapped") and can be anything you want them to be.

The first attached screenshot shows what the screen looks like if you use the Import operation for an "ASCII" file. (The choices are actually: character separate ascii, table ascii, rich text (RTF), Excel, Lotus, Symphony, and Damaged .dbf.) Once you have selected the file that you want to import, it will suggest field names and you can change them to whatever you want. (Remember, the Import is technically only bringing the data into a new table. BUT, there is an option to "import" into an existing table. The "import into an existing table" is really just a combination import/append operation behind the scenes. I'm separating them in this discussion only because combining them would add another layer of complexity when discussing them.)

The second screenshot shows the mapping screen for appending the records from the Actions table in one folder to the Actions table in the current application. In this case, the fields were identical so A5 mapped them automatically. In other words, this append operation took me all of about 30 seconds to create from start to finish.

There is also a genie that can be used for the initial import/append definition.

HOWEVER, all that stuff is designed to create a standard import/append. Once that import/append is created and saved, it can be used again and again if another file with the same name and structure needs to be imported.

If you are going to be getting various different types of files with various different structures, you would either have to redesign (a.k.a. remap) each one (which doesn't take very long once you figure it out the first time) or you would have to create a more generic mapping routine via xbasic.

If I was going to be doing these imports/appends myself, I'd just use what A5 already has. If I was designing something to be used by every saleman in the office, I'd build something with xbasic that was more menu driven.

[Selwyn Rabins]

Re: Import into existing table (Newbie - sorry)

I would just like to second what Cal has said.

A5 can definitely do what you want.

Plus, if this is something that you want to do over and over, Xdialog would allow you to create a highly customized user interface to your 'Import' operation.

[G Gabriel]

Re: Import into existing table (Newbie - sorry)

I can't seem to find a "mapping" feature

Alpha has a "mapping feature". When you append (and for many other operations), a dialogue will ask if the fields in both tables have the same names, if you choose: NO, it will take you to mapping and furthermore, it will suggest mapping for you.

There are no "Standardized" nomiclature among databeses (if there was, there will be a lot of lawsuits)

[G Gabriel]

Re: Import into existing table (Newbie - sorry)

I see that I need to take a course in speed-typing. Cal has already said what I am saying.


I thought you were going to ask a bit more difficult question: how do I turn all these garbled dates into an understandable date?

I see it coming.

[fsinger]

Re: Import into existing table (Newbie - sorry)

Cal,
You were correct - I did misquote (and misunderstand) your explanation about importing .dbf tables directly - sorry. Your recent reply really helps me get where I am going! And, you are correct also in saying that if I were going from A5 to Filemaker I'd be grousing about the lack of features in Filemaker! I forget about the learning curve for new program - but at 65 years old I deserve some slack ;-) At least I still roll up my sleeves and try. Running a small company like mine is a challenge in many ways, but I see this as more recreation than anything. I really appreciate your support with this issue - I'm on my way past that hurdle.

To G Gabriel - the date formats are really fun, but I can usually write an Excel formula to convert them before I do any importing to my database. In fact, I typically parse the raw data into Excel tables and clean it up before I go any farther with the rest of the process. I am comfortable with Excel and more of my bank clients are getting the data to me in Excel tables. One of my last steps in converting to Alpha5 will be learning to do all that in A5 and skipping the Excel process. In fact, I also use Excel to normalize the data by extracting a parent table using Excel's new "Remove Duplicates" function. I know A5 can do that, but that's down on my list.

To Stan - your overview of my issue was right on the mark. It helped me clarify where Cal was taking me. After I write a few reports to get me going I'll run A5 parallel with my Filemaker application on a bank exam. I now realize there is much more depth to A5. I wish some of the big publishers (Que, etc.) would compile a decent book on A5 v.9 - maybe that will come.

Thanks again to all. . .

Fred

[Raymond Lyons]

Re: Import into existing table (Newbie - sorry)

.... Filemaker Pro has a neat "mapping" function, where you can match or map the fields one by one into your tables. The raw data table is on the left side field by field, and the target table (my NOTES table, for example) is on the right side. I then match the raw table to the target table field by field. It doesn't matter what the fieldname is or the format in the raw data, it will be imported to the fieldname and format of my target table....
Fred

Of course others have already said that A5 has a way to what Fred wants. But this reminds of a situation many years: A customer wanted an import mapping along the lines of what Fred says Filemaker has. I pointed out how to do it A5 and the response was that many of the users who would be doing this needed something far simpler--more like what Fred is talking about and what these users had been used to with another program (not Filemaker). I looked at what they were talking about and had to admit that it was far more user friendly, even though it was more limited in what could be done with it (no expressions, for example). I have always thought this was something Alpha should add, not to replace what is there now but as another option--my hunch is it would be fairly easy to do with xbasic/xdialog.

Ray

[fsinger]

Re: Import into existing table (Newbie - sorry)

Thanks, Ray - that makes my day. It is just dumb simple in Filemaker and in Lotus Approach (my first relational database program).
Fred

[jjfcpa]

Re: Import into existing table (Newbie - sorry)

I am a Visual Foxpro programmer and VFP has some very sophisticated import and append operations; however, none of them are easy to use. You've basically got to write your own mapping routine if you want one. The import option is a little better, but limited in what you can import from. It basically creates a dbf with the field names found in the import table, but as an example, if you are importing from an Excel spreadsheet, then the field names would be Field1, Field2, Field3, etc. Not very helpful.

One question I would have is if A5V9 can import from the new Excel format (2007)? VFP will not import from that format so you have to make sure you're saving the spreadsheet in the earlier format (2003) before you try to import.