{"id":2023,"date":"2024-02-02T14:43:52","date_gmt":"2024-02-02T19:43:52","guid":{"rendered":"https:\/\/help.studentclearinghouse.org\/stcu\/?post_type=ht_kb&p=2023"},"modified":"2026-09-21T16:11:29","modified_gmt":"2026-09-21T16:11:29","slug":"creating-formatting-a-batch-query-file","status":"publish","type":"ht_kb","link":"https:\/\/help.studentclearinghouse.org\/stcu\/knowledge-base\/creating-formatting-a-batch-query-file\/","title":{"rendered":"Creating & Formatting a Batch Query File"},"content":{"rendered":"\n

A batch query file is an Excel spreadsheet that contains the students you want to query and is formatted to our system\u2019s requirements.<\/p>\n\n\n\n

Sample Batch Query File<\/strong><\/p>\n\n\n\n

\"Spreadsheet<\/figure>\n\n\n\n

<\/p>\n\n\n\n

Step 1: Set Cells to Text Format<\/h2>\n\n\n\n

To retain leading zeroes in your file, you must format all cells in text format.<\/p>\n\n\n\n

    \n
  1. Create a new spreadsheet in Excel.<\/li>\n\n\n\n
  2. Highlight columns A<\/em> through L.<\/em><\/li>\n\n\n\n
  3. Right-click within the highlighted columns and select Format Cells<\/strong>.<\/li>\n<\/ol>\n\n\n\n
    \"Shows<\/figure>\n\n\n\n
      \n
    1. On the Number<\/em> tab, select Text<\/strong>.<\/li>\n<\/ol>\n\n\n\n
      \"Shows<\/figure>\n\n\n\n
        \n
      1. Click OK<\/strong>.<\/li>\n<\/ol>\n\n\n\n

        Step 2: Create the Header Row<\/h2>\n\n\n\n

        The header row informs our system who sent the file, when they sent the file, and what type of query to perform. This row must be the first row in the file.<\/p>\n\n\n\n

        Sample Header Row<\/strong><\/p>\n\n\n\n

        \"Shows<\/figure>\n\n\n\n

        Header Row Details<\/h3>\n\n\n\n
        \n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n <\/colgroup>\n\n \n \n \n \n \n \n \n \n \n
        Header Row Details<\/caption>\n
        Column<\/th>\n Required?<\/th>\n Field<\/th>\n Characters<\/th>\n Description<\/th>\n Sample<\/th>\n <\/tr>\n <\/thead>\n
        A<\/th>\n Yes<\/td>\n H1<\/td>\n 2<\/td>\n Indicates the start of the header row.<\/td>\n H1<\/td>\n <\/tr>\n
        B<\/th>\n Yes<\/td>\n School Code<\/td>\n 6<\/td>\n First six digits of your institution’s OPEID.<\/td>\n 001234<\/td>\n <\/tr>\n
        C<\/th>\n Yes<\/td>\n Branch Code<\/td>\n 2<\/td>\n Last two digits of your institution’s OPEID.<\/td>\n 00<\/td>\n <\/tr>\n
        D<\/th>\n Yes<\/td>\n School Name<\/td>\n 40<\/td>\n Your institution name.<\/td>\n HOMETOWN UNIVERSITY<\/td>\n <\/tr>\n
        E<\/th>\n Yes<\/td>\n File Creation Date<\/td>\n 8<\/td>\n Date you are creating the file in YYMMDD<\/em> format.<\/td>\n 20240202<\/td>\n <\/tr>\n
        F<\/th>\n Yes<\/td>\n Query Option<\/td>\n 2<\/td>\n
          \n
        • CO = Longitudinal Cohort<\/li>\n
        • DA = Declined Admissions<\/li>\n
        • PA = Prior Attendance<\/li>\n
        • SE = Subsequent Enrollment<\/li>\n <\/ul><\/td>\n
        		SE<\/td>\n <\/tr>\n
        G<\/th>\n Yes<\/td>\n Capital “I”<\/td>\n 1<\/td>\n “I” for Institution.<\/td>\n I<\/td>\n <\/tr>\n <\/tbody>\n <\/table>\n<\/figure>\n\n\n\n

        Step 3: Create the Student Detail Rows<\/h2>\n\n\n\n

        Our system uses the information you enter in the student detail rows to locate matching records in our enrollment database. Enter these rows between the header row and trailer row in your file. Include one header row for each student you are querying. You must enter a minimum of 11 and a maximum of 250,000 student detail rows per file.<\/p>\n\n\n\n

        Sample Student Detail Rows<\/strong><\/p>\n\n\n\n

        \"Shows<\/figure>\n\n\n\n

        Student Row Details<\/h3>\n\n\n\n
        \n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n <\/colgroup>\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
        Student Row Details<\/caption>\n
        Column<\/th>\n Required?<\/th>\n Field<\/th>\n Characters<\/th>\n Description<\/th>\n Sample<\/th>\n <\/tr>\n <\/thead>\n
        A<\/th>\n Yes<\/td>\n D1<\/td>\n 2<\/td>\n Indicates the start of a student detail row.<\/td>\n D1<\/td>\n <\/tr>\n
        B<\/th>\n No<\/td>\n Social Security Number<\/td>\n 9<\/td>\n
          \n
        • PA Query on Non-enrolled:<\/strong> Enter nine-digit SSN.<\/li>\n
        • CO, DA, or SE Query:<\/strong> Leave blank.<\/li>\n <\/ul><\/td>\n
        		 <\/td>\n <\/tr>\n
        C<\/th>\n Yes<\/td>\n First Name<\/td>\n 20<\/td>\n Student’s first name. Do not include accented and\/or special characters.<\/td>\n ASANTE<\/td>\n <\/tr>\n
        D<\/th>\n No<\/td>\n Middle Initial<\/td>\n 1<\/td>\n We strongly recommend including this field for best results. Do not include accented and\/or special characters.<\/td>\n A<\/td>\n <\/tr>\n
        E<\/th>\n Yes<\/td>\n Last Name<\/td>\n 20<\/td>\n Student’s last name. Do not include accented and\/or special characters.<\/td>\n ALBERTSON<\/td>\n <\/tr>\n
        F<\/th>\n No<\/td>\n Suffix<\/td>\n 5<\/td>\n Student’s suffix. Do not include accented and\/or special characters.<\/td>\n JR<\/td>\n <\/tr>\n
        G<\/th>\n Yes<\/td>\n Date of Birth<\/td>\n 8<\/td>\n Student’s date of birth in YYYYMMDD<\/em> format.<\/td>\n 19900320<\/td>\n <\/tr>\n
        H<\/th>\n Yes<\/td>\n Search Date<\/td>\n 8<\/td>\n Must be in YYYYMMDD<\/em> format. Earliest date accepted is 1\/1\/1960.\n
          \n
        • CO Query:<\/strong> Search date cannot be more than 16 years in the past.<\/li>\n <\/ul><\/td>\n
        		20150601<\/td>\n <\/tr>\n
        I<\/th>\n Yes<\/td>\n Blank Column<\/td>\n 0<\/td>\n Leave blank.<\/td>\n  <\/td>\n <\/tr>\n
        J<\/th>\n Yes<\/td>\n School Code<\/td>\n 6<\/td>\n  First six digits of your institution’s OPEID.<\/td>\n 001234<\/td>\n <\/tr>\n
        K<\/th>\n Yes<\/td>\n Branch Code<\/td>\n 2<\/td>\n Last two digits of your institution’s OPEID.<\/td>\n 00<\/td>\n <\/tr>\n
        L<\/th>\n No<\/td>\n Requester Return Field<\/td>\n 50<\/td>\n Use a period to separate identifiers (e.g. ID1.ID2.ID3<\/em>). Do not use symbols (e.g. [, ], @, #<\/em>). We do not match using identifier information.<\/td>\n 10045643.M.ROTC<\/td>\n <\/tr>\n <\/tbody>\n <\/table>\n<\/figure>\n\n\n\n

        Searching by Lived Name<\/h3>\n\n\n\n

        Beginning in December 2023, the National Student Clearinghouse allows you to report a lived (or preferred) first name, middle name, and last name when reporting student enrollment. StudentTracker allows you to search for any version of a reported first, middle, or last name through its batch query feature. The name returned in your student detail report will always reflect the name that was submitted in your batch query file.<\/p>\n\n\n\n

        Step 4: Create the Trailer Row<\/h2>\n\n\n\n

        The trailer row informs our system that your file ends at that row. This row must be the last row in the file.<\/p>\n\n\n\n

        Sample Trailer Row<\/strong><\/p>\n\n\n\n

        \"Shows<\/figure>\n\n\n\n

        Trailer Row Details<\/h3>\n\n\n\n
        \n\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n <\/colgroup>\n
        Trailer Row Details<\/caption>\n <\/table>\n \n \n \n \n \n
        Column<\/th>\n Required?<\/th>\n Field<\/th>\n Characters<\/th>\n Description<\/th>\n Sample<\/th>\n <\/tr>\n <\/thead>\n
        A<\/th>\n Yes<\/td>\n T1<\/td>\n 2<\/td>\n Indicates the start of a trailer row.<\/td>\n T1<\/td>\n <\/tr>\n
        B<\/th>\n Yes<\/td>\n Row Count<\/td>\n 6<\/td>\n The number of rows in your file.<\/td>\n 13<\/td>\n <\/tr>\n <\/tbody>\n <\/table>\n<\/figure>\n\n\n\n

        Step 5: Save the File in Text (Tab Delimited) Format<\/h2>\n\n\n\n
          \n
        1. In Excel, click File.<\/strong><\/li>\n\n\n\n
        2. Select Save As<\/strong>.<\/li>\n\n\n\n
        3. Navigate to and select the location where you would like to save your file.<\/li>\n\n\n\n
        4. Enter a filename. Do not include any special characters (e.g. !, @, #<\/em>).<\/li>\n\n\n\n
        5. From the file type drop-down, select Text (Tab delimited) (*.txt).<\/strong><\/li>\n<\/ol>\n\n\n\n
          \"Shows<\/figure>\n\n\n\n
            \n
          1. Click Save<\/strong>.<\/li>\n<\/ol>\n\n\n\n

             <\/p>\n","protected":false},"excerpt":{"rendered":"

            A batch query file is an Excel spreadsheet that contains the students you want to query and is formatted to our system\u2019s requirements.<\/p>\n","protected":false},"author":3,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"ht-kb-category":[52],"ht-kb-tag":[],"class_list":["post-2023","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-querying-student-data"],"_links":{"self":[{"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/ht-kb\/2023","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/comments?post=2023"}],"version-history":[{"count":32,"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/ht-kb\/2023\/revisions"}],"predecessor-version":[{"id":3038,"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/ht-kb\/2023\/revisions\/3038"}],"wp:attachment":[{"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/media?parent=2023"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/ht-kb-category?post=2023"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/help.studentclearinghouse.org\/stcu\/wp-json\/wp\/v2\/ht-kb-tag?post=2023"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}