Import from Delimited

From RaceTab Community Wiki

Contents 1 User Interface (UI) Overview 1.1 Typical Uses for Delimited Imports 1.2 Screenshot: What you see 1.3 1 - Import Settings 1.4 2 - Preset Format (Column Headings) 1.5 3 - Column Separators 1.6 4 - Other Options 1.7 5 - Column Selector 1.8 6 - Grid Toolbar 1.9 7 - Find and Replace 1.10 8 - Macros 1.11 9 - Grid 1.12 10 - Bottom Bar 2 Video Walkthrough

User Interface (UI) Overview

A Work in Progress: The Delimited Import feature in RaceTab has been significantly improved in release number 3.036. You will find incredible new and enhanced features for importing data into RaceTab. We are writing this documentation ... should be completed in a few days ... especially if you all help -- this is after all a wiki.

The Text Delimited Import feature is a powerful and flexible feature available to RaceTab users. It allows you to bring in data from almost any source. Almost any source offers sort of text output. Rather than just writing our interface to support certain fixed sources, we designed RaceTab to be open ended so that it can be told how to interpret the content.

Essentially the idea is to break any incoming data into rows and columns and then tell RaceTab what each column contains. Given the proper column headings, RaceTab will import your data.

Typical Uses for Delimited Imports

Examples of Data you may choose to import:

• Team Names
• Athlete Names
• Event Entries
• Results from other sources including:
  • Hytek, Sydex, Raceberry Jam
  • news aggates
  • Excel spread sheets
  • on-line results sources

If you can think of another one, please add it to our list.

Screenshot: What you see

We will now explain each of the numbered areas of the Delimited Import feature, what you should put in each and what each one does.

1 - Import Settings

Importing

There are four options when importing:

• Rosters
• Entries
• Results
• Times

One of these four options is typically pre-selected for you. The pre-selected option is based on what screen you entered from. You can change this if you need to at any time. This just says what you are importing and controls what columns are preset before you make adjustments.

Insider Trivial Fact: The import process is actually the same behind the scenes no matter which option you choose. The option just determines what column options you have available to you.

From Row

This setting determines which line of the incoming data that RaceTab should start reading from when importing. In other words, if you set it to 5 then RaceTab will skip and ignore the first four rows of data and begin reading on line five. Often incoming data has a header, such as the name of the meet, etc. that occurs before the actual data begins.

Alternatively you can at any time edit the source to remove lines that you do not want in the import data/file -- this may be important if there are multiple headers for example that appear throughout the data. We will explain how to do that a little later.

2 - Preset Format (Column Headings)

Provides some standard options to set the columns. This just speeds up the process when importing from known formats. We will be adding more to this list in the future and tweaking this to make it even more useful. It is pre-populated for you in most cases based upon which if the four options are selected at the top (Rosters, Entries, Results, Times).

3 - Column Separators

This tells RaceTab how to interpret the text content.

Text Delimiter Typically data is separated by something -- and that something is called a delimiter. Files with data containing delimiters are called delimited files. One of the most common places we see delimited files is in Microsoft Excel files. When you copy Excel data you will see that each column is delimited/separated by a tab character.

Delimited data is however even more common than that! The prior sentence is delimited from this sentence by an exclamation point. Each word within the sentence is delimited by a space. So if we wanted two we can break this paragraph up on delimiters, first by using punctuation (period/exclamation) to make rows out of the sentences and then to break the words into columns using spaces as the delimited! Pretty cool. The same theory will allow us to break meet/results data down.

Common delimiters are:

• space(s) (a character entered by using the spacebar on your computer -- sometimes more than one is used)
• pipe (a shifted character "|" that looks like a straight vertical line on the backslash "\" key on most keyboards)
• tab (the key labeled "tab")
• semi-colon
• comma
• other -- you can use any character not found elsewhere in the data, Sydex for example uses a exclamation mark "!" and we have seen percent signs, at signs, asterisks and many other characters used, they are just not as common as the ones listed above.

Example of semi-colon delimited data (roster information): First Name;Last Name;Team;Grade;Gender Jonnie;Jones;All-Stars;9;M Tina;Burner;WithTUDE;10;F

Fixed Width Sometimes data is presented in fixed widths. Each column of data starts a particular number of characters from the start of each line. This is often true for cross country results.

Example of fixed width data (roster information):

First Name     Last Name     Team     Grade     Gender
Jonnie         Jones         All-Stars9         M
Tina           Burner        WithTUDE 10        F

Notice that in many cases with fixed width data there may be no space or other delimiter between two columns of data, such as is demonstrated between "All-Star" and "9". This is perfectly fine. Also with fixed width data, if some data is longer than the number of spaces allowed for that column, the data is sometimes truncated, meaning the end of the data set is missing.

Hybrid Essentially a hybrid may be a combination of the above. Often in the case of a hybrid file, you will want to do some search and replace to head one way or the other.

Example of working with semi-colon delimited files

4 - Other Options

• Double-Space = Tab - Will consider a double-space a tab.
• Strip Quotes - Automatically strip out quotes (useful for CSV that has quotes in it).
• Add to Age Groups - When importing will add athletes to age groups based on age or birthday (groups must be created before import).
• Wildcard Matching - When importing will search for teams and athletes wild a wildcard around it. In other words, if the team is "Anywhere High" in the import file, but "Anywhere High School" in your RaceTab meet database it would match. If this is unchecked it would require an exact match and so it would create a new team instead.
• Overlay If Exists - If importing a team, athlete, or entry/result that already exists it will overlay it so as to update it (otherwise it will just leave it as is).
• Auto Create Events - If it can't find the event you are importing, it will automatically create a new one with that name. This is not ideal, but can be helpful in some cases.

5 - Column Selector

This allows you to change the column headers of the grid. Choose the column you want (you can either click the column header [not currently working: or just click any cell in that column]) and then choose what the content is from this dropdown. It is critical these match accurately as it determines what is imported and where it goes!

You can select more than one column at a time by holding down your control key while clicking. Once you select multiple columns special features appear under the right click menu: ignore (all selected columns) and hide (all selected columns).

6 - Grid Toolbar

Buttons:

• Clear - Deletes all content and rows from grid.
• Paste - If you have any text content copied to the clipboard will append it and put it into the grid.
• Edit Source - Allows you to edit the raw text content (not in table form).
• Add File - Add a new file to the grid. Appends it to the bottom.

7 - Find and Replace

The find and replace operations really open up a lot of possibilities for transforming text content to get it formatted into columns properly so that it can be imported. Master these techniques and you can transform basically anything.

Mode:

• Search - This is the normal find and replace that people are used to. Searches your entire input content and replaces it with whatever you specify.
• Per Line - Same as "Search" except that it will only do it once per line/row.
• Starts With - Will only do the find/replace if it comes at the beginning of a line/row.
• Ends With - Will only do the find/replace if it comes at the end of a line/row.
• Regex - For advanced users, use Regular Expressions to really do some good stuff!
• Delete - Will delete the entire line/row or cell (depending on what is selected) if it contains what is in Search For (which accepts Regular Expressions in this context). Replace With field is disabled.

Selection

If this is checked, the find and replace operation will only pertain to the cells that are highlighted. Otherwise, it will apply to the entire body of content.

An important side note... only operations applied to the entire body are recorded for macros.

8 - Macros

Macro's provide means to repeat common search and replace actions.

A macro is a saved routine. As long as "Record Actions" is checked, it will silently be saving every find and replace action you do (as long as it is on a global scope) and also many of the right-click options you'll find on the grid. After you have transformed content how you want it, click the Save As... button and save that macro as a file. By doing so it will be available to you again.

The next time you come across a similar file needing search and replace actions performed, just click Apply and run that macro against it. You will find it instantly transformed by those settings... saving you loads of time!

We would like to create a Macro Exchange where people can contribute their macros into a community pool to help others.

9 - Grid

Editing Text Content

You can edit the cell content right in the grid.

Right Click Options

There are many right-click options available. The right menu is contextual depending on what you have selected on the grid. We'll try to highlight all of these possibilities.

Keyboard Shortcuts

Delete - Deletes content from highlighted cells.

Tab - Will indent the highlighted cells or entire rows.

Select Entire Row

Click on the row header (far left of the table) to highlight the entire row. Click and drag to highlight multiple adjacent rows. Hold down the control key and left click to select multiple non-adjacent cells.

Select Entire Column

Click on the column header (top of the table) to select the entire column. Click and drag to highlight multiple columns.

Drag and Drop

You can drag and drop textual or Excel files into the grid and it will be appended.

10 - Bottom Bar

Left Side

• Lines - Tells you how many lines were found in the import file.
• Cols - Tells you how many columns it has split the file into.
• Refresh - Link to force RaceTab to re-parse the content into the table. This shouldn't be necessary as RaceTab should automatically do this when anything changes, but if RaceTab balks at a particular set of data, then you can click this to tell it to update now.

Right Side

• Cancel - Closes it out without importing anything.
• OK - Imports the data and closes the window (will show you a report of what was done when importing a delimited file).

Example of the entries report after clicking on OK. Any exceptions would also be noted. You have the option of saving this report to a file if you wish to, or simply click "OK" to close.

Video Walkthrough

Importing Results, Advanced

This video shows some of the new advanced features such as regular expressions, tabbing results, "delete rows like this", and manually editing content.

Importing Rosters, Simple

This is a simple import with a basic spreadsheet. The version of RaceTab in the video is older and some of the UI has changed slightly.