Direct Certification Import File Preparation DIRECT CERTIFICATION IMPORT

Direct Certification Import File Preparation

DIRECT CERTIFICATION IMPORT FILE PREPARATION INCLUDING: • What ‘Clean’ data for import looks like • Working with CASE data, like SNAP and Medicaid Reduced • Manipulating the Date of Birth field

• Each state provides a Direct Certification file. • The format and content of these files vary greatly from state to state. • Some must be manipulated to work with the HST import template.

‘Clean’ data for import First, let’s take a look at what ‘clean’ data for HST looks like.

‘Clean’ data for import First, let’s take a look at what ‘clean’ data for HST looks like. Some DC files contain your local student ID and/or state ID. These are the best matching criteria. Beware of leading “ 0”s. Excel may strip them when you open the file.

‘Clean’ data for import First, let’s take a look at what ‘clean’ data for HST looks like. Some DC files contain your local student ID and/or state ID. These are the best matching criteria. Beware of leading “ 0”s. Excel may strip them when you open the file. First+Last+DOB is a very common for matching. Date of Birth will import ‘as is’ if it looks like this or similar.

‘Clean’ data for import First, let’s take a look at what ‘clean’ data for HST looks like. Some DC files contain your local student ID and/or state ID. These are the best matching criteria. Beware of leading “ 0”s. Excel may strip them when you open the file. First+Last+DOB is a very common for matching. Date of Birth will import ‘as is’ if it looks like this or similar. Case, while not required, is highly recommended for import. It must be represented in a single column. (Medicaid Reduced must be imported separately. )

‘Clean’ data for import First, let’s take a look at what ‘clean’ data for HST looks like. Some DC files contain your local student ID and/or state ID. These are the best matching criteria. Beware of leading “ 0”s. Excel may strip them when you open the file. First+Last+DOB is a very common for matching. Date of Birth will import ‘as is’ if it looks like this or similar. Case, while not required, is highly recommended for import. It must be represented in a single column. (Medicaid Reduced must be imported separately. ) Due to privacy concerns, Social Security # is not imported into HST.

‘Clean’ data for Import Your DC file from the state will probably have many more fields than shown below. You don’t need to get rid of them. You’ll simply SKIP them on the import template. See the lesson ‘Importing and Exporting’ for how to build a template and job to import your Direct Certification file.

Let’s take a look a few examples of typical modifications required to make a DC file importable. Fair Warning! If your Direct Certification file does require manipulation, have someone familiar with Excel formulas watch this with you.

Working with CASE data Working with Multiple CASE columns In this example, from the state of Virginia, the student’s Case is defined in multiple columns. HST has a single field to identify CASE (and is translatable from any value, like ‘Yes’ to the appropriate case, like ‘SNAP’). To import the original data from the state we have to combine the data into a single column. Several states use a variation of this multi-column convention. This can be done with embedded IF statements in Excel, by importing the data into Access and querying, etc.

Working with CASE data HST requires that Medicaid-Reduced be imported separately from all Free designations. In the example given in the previous slide, note that Medicaid-Reduced was included w/ Free case types. HST requires that Medicaid-Reduced be imported separately from all Free designations. Create 2 files, separating Medicaid-Reduced from the other Case types. Medicaid- Free CAN be imported along w/ Snap, TANF, etc.

Manipulating the Date of Birth field Shown are the Birth Dates as provided in Tennessee’s Direct Certification file. = 10/24/13 We must convert 131024 to 10/24/13. The excel formula MID(a 1, 3, 2)&”/”&RIGHT(a 1, 2) &”/”&LEFT(a 1, 2) will accomplish this, as will a number of other approaches. Birth Dates are represented in a variety of ways in the state files. As a rule of thumb, they will need to be in MM/DD/YY or MM/DD/YYYY format for import.

Need Help? In cases where the Direct Certification files require considerable manipulation, and they’re imported frequently, building a macro in Excel, Access or a similar program can save considerable time and reduce errors. Horizon offers a Direct Certification Import Service, including before and after Eligibility/Case counts for validation and reporting. Contact Inside. Sales. Support@horizonsoftware. com for more information.

Thanks again for your time and effort. It’s appreciated!