H-1B Prevailing Wage Claims vs. Reality:
What They Say, Versus What They Pay

By Robert Hill, CUNY, Murphy Institute for Worker Education

Appendix B: Transforming and Grooming the Data, Details

When data is stored in a database file, certain decisions are made about its “metadata” characteristics. For instance, metadata about whether the data is text, or an integer, or a floating point number is stored along with the data itself. 

It was discovered that the numbers representing the wage data, both LCA and prevailing, were stored in the database file as “text.” To the human eye, the numbers looked like numbers, but to the computer, the numbers looked like text and were unavailable for even simple calculations, such as addition and multiplication. 

Because there were hundreds of thousands of data points with this fundamental flaw, a method was required to convert them from “stored-as-text” values to “stored-as-integer” values. One technique for doing this is to multiply the contents of each cell by the integer “1,” which effectively transforms the character of the cell from “stored-as-text” to “stored-as-integer.” This was accomplished by picking some empty cell and entering the number “1” in it, then selecting that cell and copying it into the system clipboard.  Then, each cell of the column where the “stored-as-text” numbers were found was selected by the technique first selecting the top-most cell, then performing the function CTRL+SHIFT+END to reach the bottom of the data. The focus was redirected solely to the column that required manipulation, and then a special Paste operation was performed.  Using the right-mouse-click command, the Paste Special sub-menu was accessed. On that sub-menu there is the option to perform mathematical operations while pasting the data. By selecting the operation for “multiply,” the contents of the cells were effectively multiplied by one. Once this transformation was accomplished by this technique, the LCA and prevailing wage data was available for calculations. 

This was repeated for each of the four columns of data that needed this transformation, and for each of the 10 Excel files resulting from the export from the source Access database.