As an alternative to using Microsoft Excel®, consider using Notepad or Notepad++. However, if you prefer to work in Excel, please review and follow these tips to minimize unexpected changes to your data caused by Excel's formatting engine. Adhering to these tips may help you avoid file processing errors when you attempt to import your data files into the HMH system.
Some of the fields in the template files, such as the LASID and the DOB (date of birth) fields, may include leading zeros as part of their value. To retain those leading zeros when building files in Excel, you must do the following:
○ set the field as a text field, and
○ prefix the field with an apostrophe (') to ensure leading zeros are not truncated.
If you do not use these specific settings prior to saving the file as a CSV file, then the leading zeros may be stripped away when the file is reopened.
WARNING: Excel converts numeric grade ranges to dates. For example, a grade range entry of "1–8" is converted to "8-Jan". This affects Simple File Format users; specifically, it affects grade range entries for teachers in the GRADE field of the USERS.csv file.
Note: Do not double-click the CSV file to open it! – Leading zeros and quoted identifiers are stripped away. – Date of birth (DOB) field is reformatted. |
If you double-click a CSV file to open it, Excel strips away the leading zeros and quoted identifiers and/or reformats the DOB field. To retain the formatting of leading zeros and DOB fields, you must manipulate the Excel file as detailed below.
When editing your SIS extracted data, you must preserve the formatting of the leading zeros in the LASID, Student ID, CLASSLOCALID, ISBN, Product, and DOB fields. A text editor such as Notepad++ is ideal for manipulating your data and reduces the potential of formatting changes. However, if you do decide to edit your files with Excel, follow the steps below.
Note: Leading zeros in PIDs are automatically retained.
1. Open the Excel application (not a file).
2. On the top menu bar, click Data and then click From Text. The Import Text File dialog box opens.
3. Select the CSV file to open it. The Text Import Wizard opens.
4. In the Original data type box (Step 1 of 3), click the Delimited option button, and then click Next.
5. In the Delimiters box (Step 2 of 3), select the Comma check box and then click Next.
6. In the Column data format box (Step 3 of 3), click the Text option button, and then in the Data Preview box, select all columns by clicking in the first column, pressing the Shift key, and then clicking in the last column.
7. Click Finish, and then, in the Import Data dialog box, verify that the cell placement is =$A$1 and click OK. The file is now open in Excel.
Excel changes date formats every time you open a file. To retain the necessary format in the DEMOGRAPHICS.csv file's DOB (date of birth) field, you must follow these steps below.
1. Highlight the DOB (date of birth) column, right-click, and select Format Cells. The Format Cells dialog box opens.
2. In the Category list on the Number tab, select Custom.
3. In the Type box, type yyyy-mm-dd and click OK.
4. Repeat steps 1–3 every time you open the file in Excel.
v7.0