Preparing to Import to our Database: Tips for Formatting Data in Excel: PVSWiki

Preparing to Import to our Database: Tips for Formatting Data in Excel

Note: also see: PCT instructions for preparing Candidate Lists for import

Excel and its equivalents (such as OpenOffice Calc) are tools in which you can transform bulk, structured data produced by external organizations into something we can import into our database. By the end of your work, each cell will correspond to a field in Admin.

Imports Vs. Manual Entry

Advantages of bulk imports:

perform bulk operations to clean up your data
save data entry time

Disadvantages of bulk imports:

it may require limited and costly IT resources
longer learning curve
when performing bulk operations, one mistake translates into many mistakes

General Procedure:

Obtain a data file

Some websites will have data available for bulk download. Ultimately you want a .csv or .xls but as long as it is in a structured format, there's likely a converter you can find on Google that will get it into a .csv (though you will want to double check for accuracy in this case).
Extract tables from PDFs using TabulaInstructions
Alternatively, you may try copying and pasting data from the original source

Use your cursor to select and copy the necessary information
Right-click a cell in your spreadsheet and go to "Paste Special"
Select Text and click "OK"
In an ideal world, your paste will look like you typed in everything neatly yourself. This is rare.

As a last resort, you may try contacting the source of the data to see if they will provide the data in bulk upon requests, but this generally only makes sense timewise for large data sources or if your contacting many businesses/governments at once
If it is not possible to copy and paste the data in a usable format, or get the data in bulk, this data must be entered manually into Admin according to normal procedure

Open the file or Transfer the data into Excel (via Copy and Paste or Import)
For data you intend to import, match up columns to the content of a field in Admin

use "Text to Columns" to separate data into separate columns if necessary
use the =CONCATENATE Function to Combine Columns. In parentheses, name every cell in which the data must be merged, separating cell numbers by a comma and no space. For instance, if you wanted to merge the data in cells F2 - J2, the command will be =CONCATENATE(F2,G2,H2,I2,J2)
name your column headers to mirror the naming schema of our database

Use excel functions and formulas to clean up the data so it matches our normal formatting and content standards. For example, you may use the "Find and Replace" function to convert any address abbreviations into their fully spelled-out version. Do not use "Replace All" (see: ResearchSpreadsheetFunctions for other useful functions)

WARNING: DO NOT SORT DATA UNLESS YOU ABSOLUTELY HAVE TO, ARE INTIMATELY FAMILIAR WITH HOW TO DO IT PROPERLY, AND HAVE ASKED YOUR SUPERVISOR FIRST. TRY TO USE AUTOFILTERS INSTEAD.

MatchIds so that your data may be related to existing data in our database
Save as a CSV
Have your supervisor or another authorized individual check your .csv
Submit the file to Mantis for import
Verify the import was done

General tips for formulas:

Once you are satisfied with the accuracy of your formula, use the drag feature to place this command in every cell thereafter (e.g., if you will use the same function in rows 2-200, you can drag down to row 200 and Excel will automatically re-number the concatenate command). You can also double click in the lower-right corner of the highlighted cell to autofill. Spot check to confirm the formula continued to be accurate.
By default, formulas will display the value they equate too in a cell. However, in it's raw form it only a formula, when you ultimately want the values. When your formula is finalized, convert formulas to values:

Select and copy the column of data.
Select an empty column for which to store your values
Right click and select "Paste Special."
Select "Values" under the paste heading and click OK.
If you are still working in the same worksheet, delete the original column with formulas, assuming no other formulas are dependent on that column
If you pasted a value that has an apostrophe at the beginning ("'100%") and you cannot find and replace (CTL+H) it, this is because it is not a character but a metacharacter. Follow these steps to remove it:

1) Highlight all of the cells and use "Format -> Cells" to change the cell format to an appropriate format.
2) With all of the cells still selected, go to the menu "Edit -> Find & Replace" (CTL+H)
3) In the "Search For" box enter ".*" (period asterisk, without the quotes). The .* "means zero or more of any character" and & means "whatever was found". These are regular expressions.
4) In the Replace with box enter "&"
5) Select "More Options" and check "Current Selection Only" and "Regular Expressions"
6) Click "Replace All"

CategoryTechnicalSkills

PVSWiki : FormattingDatainExcel