Import Records into Goldmine

Hi DW,

GoldMine needs the data to be in dBase (.dbf) format. You can also use csv, sdf, or sql, but dbf is the easiest to deal with.

To automate the process, typically you'd use GoldBox; but again, you'd have to have your data in dBase format. MS Excel (prior to Office 2007) can do a "Save As" dBase.

If you can program something to convert your XLS file to dbf on a scheduled daily basis, you can use GoldBox to import it automatically on a daily basis.

Warning: GoldBox is not for the faint of heart. It's a techie tool, reasonably steep learning curve, ultimately very powerful. You should create a test instance of your GoldMine database on a  test server while you learn how to use it!

====== Note:
GoldBox imports are known to be valid and acceptable as a 3rd-party import. This is critical if you ever expect to get help from FrontRange and/or if your users are using GoldMine's synch function. If neither of these things are true, you may also wish to experiment using direct back-end import. Again, this is not supported by FrontRange. And I'm only speaking in the context of SQL. (If your GoldMine data is in dbf format I wouldn't attempt it.)

Having said all that, you could, potentially, create a DTS package in MS SQL server that would fire off once a day and import your data directly from Excel into GoldMine.

Good luck,
Steven

Steven,

Ive opened the file in Excel and its possible to resave the file in the following formats

dbf 4, dbf 3 and dbf2.

Which one would Goldmine prefer?

Also, for now - I just want to get the data in manually and I can then concentrate on getting the data import process automated later. Do I use the import wizard built into Goldmine??

dbf4

Yes, use the import wizard.

Also, keep in mind a few points regarding Excel and conversion to dbf:

Keep column names < 10 characters, no special characters, no beginning with numbers.
Insert row of x's under header, to force conversion to character (no possibility of numeric)
Spread columns extra wide to prevent truncation of data
Select your data prior to doing your save-as.
After Save As dbf, immediately open your dbf file and see what MS has done with it. If it looks fine, fine; on my occasion I have seen odd effects and had to re-do.

Ok, limited success but it looks promising. There are a couple of things to fix but the most important bit is the record id.

All are goldmine records have ref numbers which is generated by Goldmine as and when a new record is created. When I import my records, they dont have a ref id, how do I get Goldmine to give the imported record the next available goldmine ref?

Other than that, I just need to find a couple of fields which Im sure I'll find once Ive got the above issue sorted.

When you do a fresh import, i.e. creating new records, GoldMine will automatically assign an accountno and recid. The recid is a unique record identifier (unique, in the strict database sense); the accountno is a key field, unique for each contact, that links various contact and calendar data together for a given contact record.

Anyway, when you do an import you shouldn't attempt to import either the accountno or the recid... GoldMine will (and must) assign these for you.

Im not assigning anything to the accountno and there is no option to select the recid from the import wizards screens. Ive taken another look at the wizard and cant see anything which relates to automatic generation of the recid??

GoldMine will automatically create the recid during your import. There is no option to turn this function on or off -- this is just the way it works.

Ok, in that case, Im must be doing something

Just to confirm the record id is the number which appears at the top right hand side of any contact record right?

No, sorry, miscommunication.

In GoldMine, every record in every table has a unique identifier field called "recid." It looks a little like this: D0R1MMT#;<&(@#-

In GoldMine Premium Edition, in the upper right corner above the contact record you may see something like a record counter, i.e. 567 of 2495.  Is that what you're talking about?

Yes, you got it.

OK, that's not actually a field you can import, and it's not associated with any data attached to the contact record.

GoldMine is simply letting you know which record it thinks you're on, based on how your database is indexed at that moment.

unm errrr, not sure how to go forward here then. Im going to try and import a record into the live database to see if that issues a Ref,

When you say "Ref" are you again speaking of the number in the upper right corner? If so, again, that number is not stored in the GoldMine database in any way shape or manner. If you think of your contact list as a stack of cards, GoldMine is simply displaying where the current record is in the stack, and how large the stack is.

If you need to assign your own sequential number upon import, that can be done using a counter expression, but that's another subject.

Yes, Im talking about the number in the top right corner. All our records within Goldmine have one of these numbers, starting at 1 its slowly worked its way upto 32k records. I just wanted to append the next available number to the record on my import but currently, this is not happening? I think for now, I will have to assign the record ref manually. Im in tomorrow I will be attempting to import 50 records into Goldmine and will see where that takes me.

I was talking to one of the team who input records manually and they informed me when they hit NEW and input the details, they leave the Ref field empty since Goldmine sorts this out automatically. Im thinking there could be a slight difference between my live and test Goldmine server, we will have to see what tomorrow brings!

Thanks for your help so far Steve, I will update the thread tomorrow once Ive imported the records and let you know how it went.

Just to be clear, here's a picture of what I think you're referring to: http://screencast.com/t/KeHIStQJbr

If you have a field that's getting auto-populated, auto-incremented upon record creation, that, again is something else. If that's the case, then you likely have an active lookup.ini in your root GoldMine folder (you may want to check). If that's the case, you can activate the lookup.ini during import by checking the appropriate box in the Import Wizard.

If the picture linked to above is, in fact, what you're calling the Ref field then it's not really a field, and there's no way to import into it.

Ive imported a record into the live database and its not issued the next available goldmine reference number.

Ive investigated further and can confirm there is some sort of automated process which deals with the issuing of the Goldmine Ref number but I have no idea on how this works since our dev guy left last year.

Even if I apply the next available number to the record I imported, the next time someone within the business creates a new record manually, it will use the same number I append to the import record.

What started out to be a simply import process is now snowballing but Im going to keep plugging away at it.

This wont beat me!

Please see http://screencast.com/t/JdB2bdgF and make sure you're clicking Profile Options and then checking the box as shown to activate the lookup.ini functions during import.

Assuming you are now acknowledging that the Ref you're talking about is, in fact, in a distinct GoldMine field (not as shown in my previous link), I'll tell you the way to turn that function on is:

"you can activate the lookup.ini during import by checking the appropriate box in the Import Wizard."

Steven, Ive activated the ini but its not helped.

So, you're saying that when users manually create a new record, your Ref field gets automatically populated with the next sequential number, but during an import this field does not get populated? It's left blank following an import?

Could be that there's something wrong with your installation, that the copy of GoldMine you're using to do your import with isn't "seeing" the lookup.ini file on your server.

Or, it could be that your lookup.ini file is designed to update your Ref field based on the existence of some other data, data that's not being included in your import.

Could you please post (or send me) a copy of your lookup.ini file? A screen shot showing your main GoldMine screen (including your Ref field) would be helpful at this point as well.

Oh, btw, the lookup.ini file will be in the root of your GoldMine installation directory.

Yes, its left blank.

Here is a screen shot and a copy of my lookup.ini

[AUTOUPDATE]
NewRecord = company, Key2
[company]
Otherwise = &padl(ltrim(str(counter([AcctNo],1))), 5, [0])
[Key2]
Otherwise = "ENQ"
Followup=1

goldmine.jpg

{btw, the Ref field you're referring to is in the upper left  portion of your screen, not the upper right, you're not using GoldMine Premium Edition, you're using GoldMine Corporate Edition.}

Your Ref field could be the Company field with its label changed, but not likely. The expression being used in your lookup.ini file should produce a number like 000025 -- not just 25, so I doubt that that's it. Could be a SQL trigger is being used to update it. But that's unlikely too, since the update would then be done at the SQL level.

What do you see for the field definition when you do a control-double-click on your Ref field (then double-click again)? Something like this:? Field Properties Dialog

In particular, in the Field Data section do you see an Expression?

I do remember changing the expression in the ini file when we reached 9999 records, I had to make a change which was advised by my dev guy so the field could display 5 digits.

Your right about the ref field being the company, check out the attahced.


expression.jpg

OK, so it's actually the COMPANY field we're working with. The expression in your lookup.ini file seems to work. And, you say, when entering records manually, the Ref/Company field does get populated properly, incrementally.

So:

1. Be certain, during your import, that you check the lookup.ini checkbox as referred to in my earlier post.

2. Try running your import from another machine, possibly even your server, to reduce the possibility that the lookup.ini file isn't being "seen" from the machine where you're doing your import.

3. Try using an Overwrite = 1 line at the end of your [Company] section.

4. Is the Key2 update triggering during import? I mean, do new records get created with a value of ENQ in the Loan Status field? If so, ignore #1 and #2 above.

5. In a pinch, if all else fails, you can copy/paste the expression from the lookup.ini file and use it in your import profile.

Steven,

1 - confirmed
2 - tried no difference whether on server or desktops
3 - Not sure how implement this?
4 - I think so
5 - Tried but got "Bad Expression" message when trying to view data (see attached)

Dave
express.jpg

3.

[AUTOUPDATE]
NewRecord = company, Key2
[company]
Otherwise = &padl(ltrim(str(counter([AcctNo],1))), 5, [0])
Overwrite = 1
[Key2]
Otherwise = "ENQ"
Followup=1

4. It would be very helpful to know this, so please create a new record and see if that field gets populated with ENQ.

5. You're on the right track, but in this context, remove the "&" and you'll be fine.

Steven

When I amend the expression, the import worked and it append a number to the record. The only problem was the import created about a 100 blank records as well??? Any ideas why that is?

With regards to 4 - Yes the field gets populated with ENQ

Re-examine your dbf file. You should be able to open it again in Excel. Most likely, referring to my comments of
Date:01.16.2009 at 01:53PM EST

Select your data prior to doing your save-as.

Be certain to select only the rows and columns you want; otherwise Excel may make some quasi-default decisions for you.

Ok, got them in WAHEY!! Just need to assign a task to the new records, how easy would that be?

Excellent support from Steven, job done - well done Experts!!!

One final issue, for some reason, its only importing 27 records, is there a limit on how many records can be imported at any one time?

No, no limit.

umm errr?? Ok, onsite Tuesday - will give it another go and see how many the actual import wizard can see when when working through the import process.

No matter what I do with the import file, whether there is one or 50 records in the file, there is only 28 records available for import??  When I import a file with under 28 records (lets say the file has 20 records in), the 20 records are imported with 8 blanks. If I import over 28 records, it imports only the first 28??

Working around at the moment by splitting the import file into 2 files when there is more than 28 records - once import has been completed, locate blank records and delete.