TSV2CSV2Insert

I frequently have to take information from spreadsheets and insert it into a database. I prefer not to insert numeric data into generic text fields. I would rather have data that is strongly typed. It’s harder to run a sum() or avg() function on a text column. Tired of manually replacing bad characters in number fields and converting XLS to CSV to an import or loader utility, I cobbled together some JavaScript to do my work for me.
While trying to avoid the manual process of cleaning up some spreadsheets, I found that I can copy and paste spreadsheet data into an HTML textarea and get quoted, tab-separated values. With a little regexp and replacing, the tabs turn into commas. With a little Googling, I found a script to turn commas into arrays. The arrays are then looped over and put into text fields which can be submitted to an action page and inserted into the database. So, this little script converts about 30 minutes of work into 30 seconds. Copy, paste, submit, done. Me likee. Here are the details.

First, here is a small block of tab separated values; three rows and twelve columns.

DEPTNO	DEPTNAME	STAFF	ST_HRS	ADMIN_HRS	SICK_HRS	HOLIDAY_HRS	INJURIES	RELEASE	HIRE	PHONE	FAX
10	INFORMATION SERVICES	32	1,534	80	14	40	1	1	2	5552	5551
20	HUMAN RESOURCES	12	400	80	10		0	0	1	5553	5555
30	ACCOUNTING	8	320			8	0	0	2	5554	5556

Next, let’s look at the html source file. There’s a bit of style, a form and a bunch of JavaScript. I have to give credit to Ben Nadel via Google. An article in the “Ask Ben” section shows the CSV2Array JavaScript and does the regexp stuff that I used.

The style bit is just a way to setup the field headers and form fields.

<style type="text/css">
.fHdr {
float: left;
width: 100px;
font-weight: bold;
text-align: center;
border: 1px solid silver;
background-color: cecece; }

.fld {
float: left;
width: 100px;
border: 1px solid silver;
background-color: ffffff; }
</style>

Next, the form is very basic to start with since most of it is built dynamically by the JavaScript.

First, copy and paste the data into this textarea. Second, hit the go button. This should process the data into neat little fields below.

Records to be inserted: Finally, review the data and then click the insert button.

Now for the fun part. A JavaScript variable with a list of field names. I add a hidden field that lists the field names so that my action page doesn’t have to do extra work. Next we use CSVToArray() and walk through the new array to create DIVs that will hold the fields populated with the pasted data.

var myFields = "deptno,deptname,staff,staff_hrs,admin_hrs,sick_hrs,holiday_hrs,injuries,release,hire,phone,fax";

document.writeln("
");

var myFields = CSVToArray(myFields);

for(x=0;x

After the user pastes the data, we prompt them to click the “GO” button that fires off the processing function, proc().

function proc() {
var myTxt = document.getElementById("cnp").value;
var myTxt = myTxt.replace(/\,/g,'');
var myTxt = myTxt.replace(/ /g,'');
var myTxt = myTxt.replace(/\t/g,',');
document.getElementById("cnp").value =  myTxt;
var myArray = CSVToArray(myTxt);
document.getElementById("lim").value=myArray.length;
for(i=0;i

Inside proc(), we first collect the pasted data and stuff it in the myTxt variable. Then we use replace() and regexp to remove commas and spaces and then replaced tabs with commas. While it isn’t necessary, I replace the pasted text with the newly formatted text to help with debugging. Now that we have several lines of comma-separated data, we push them through CSVToArray() and create the myArray. The next line shows the user how many rows of data we found.

Next, we walk through the array of data, marry each element in the data array with a field in the field array and populate a field with the value. Since we have rows of columns (or is it columns of rows?), we pick up a row for(i=0;i < (myArray.length);i++) and walk through each column for(x=0;x < myFields[0].length;x++). I chose to fill the DIV with input fields so that the user could make corrections as necessary.

The bit about if myArray[i][x] = “” is there to create a field and fill it with a value. This is mostly just to help with the processing on the action page, plus, I wanted to give the user the option to enter a value if one is not present.

Once the fields are populated, the form is ready to be submitted. Here’s a working demo for you to try out. Copy the small block of tab-separated values at the top of this article for use in the demo.