Subject: FW: FYI: Genus Import Utility - Data File Formats

Administrators:  Please distribute this to any of your staff or contractors that may be involved with Importing files for Genus.

We've recently had some confusion regarding the Genus Import Utility, specifically the Data File Format radio buttons.   I hope to explain some of the basics about imports, using Cruise imports as an example.   See screen shot below...

While Genus is capable of importing either "Delimited flat files" or "Positioned flat files", we're reccommending that you ask your contractors to submit their files for importing in a "positioned flat file" format.

A Quick Overview of the differences:

The difference is that a delimited flat file separates the various data entries by either TABs, commas or semi-colons while positioned files have a reserved # of character placeholders reserved for each field of data.

Delimited Flat Files:

• For example, if we had two fields First Name and Last Name, a comma delimited file would give us an import file that would look like:
• eg.
• Brian,Quinn          Note: there are no spaces in the data.  The comma tells the program where one field ends and the next begins

Positioned Flat Files:

• However a positioned file would be set up with a reserved number spaces for each field.  For example say it was 10 for each field.  In such a case, the import file would look like:
• eg.
• Brian     Quinn     Note: the extra spaces that weren't used because the name only has 5 letters in each field.  The position of the data tells where one field ends and the next begins.  The first name occupies positions 1-10 and then the second field begins at position 11 and goes through to 20.

If the data had more than 10 characters, the import would still assign 10 characters to each field and a overflow would happen in the first field into the second field or a truncation would occur if the long name was in the 2nd field.

• eg.  Suppose the first name was HR and the 2nd name was Puffenstuff
• It should properly be entered as HR________Puffenstuf    Note: 8 spaces after HR and one letter deleted from Puffenstuff because there wasn't room for 11 characters.

Advantages of Positioned Flat Files:

We prefer our data coming into BCTS in a positioned flat file because we can assure the truncation above doesn't happen when the data is imported into Genus.  Every database field is (including Genus) is defined by a certain number of characters and so our import specs reflect these limitations.  With the delimited files someone could try to import a name that is say 55 characters long and only 10 (or whatever the real number is) would show up in the database.   With a positioned flat file, our contractors set up their data with the required number of positions alloted and they just enter data within the allowed space.  Think of an excel column that is set to a pre-defined width.  This would be the position length of a Positon flat file field.

Another advantage of a positioned file is that it doesn't preclude us from using commas or semi-colons within our dataset (not that we want them anyways).  If we were using a delimited flat file with the data separated by commas, then a dollar amount of 1,000 would cause the import to separate the $1000 into a 1 and a 000.

Which type of data do I have?

You can open most of your import files by using the Notepad found under >Start >Program >Accessories.  They're really just text files often with a made up file extension.  Double click on the file to open it and if Windows prompts you that it doesn't know how to open it, just select the option that you'll open it with a program of your choice.  Then select Notepad (or Word)  from the list that is presented to you.

Once it opens, positioned flat files often look like they have lots of spaces in them because the data doesn't completely fill each field more often than not.

Delimited files will have the data all run together and separated by commas or semicolons or tabs.  See the attached examples with this email for an example of each .  The files themselves won't mean much, but it will help you to recognize the difference in formatting between the two which will help you troubleshoot if you can't import a file properly.

Access to the Data File Format:

Most users won't have the permissions or the ability to change from one format to the other and so it should always be set to Positioned  Flat File .  However, it's important to realize that if you do have access (administrators) , this is a global setting and by changing it for your own needs, (which may occur from time to time), it's important to set it back to Positioned for the rest of the users.  We don't want to lock this function down.

Position Flat Files Specifications:

In order for a position flat file to work properly, contractors will need to know what 'width' they need to set up their import files to.  These specs have been distributed previously to the administrators for silviculture imports and also for interior and coastal cruises and we're in the process of posting these to the BCTS website but they're not there at the moment (but will verrrry soon).  We'll be launching the new web page shortly (week or two) and you'll find the specs under Genus Reference Materials - Templates.  Keep an eye on http://gww.for.gov.bc.ca/bcts/genus/ for changes.

Hopefully this helps clear the air....

____________________________________________

Brian T. Quinn,
Planning and Information Officer
BC Timber Sales Headquarters
: http://www.for.gov.bc.ca/bcts/