Easy Way To Save Excel Data in your Program

Lets face it, everyone still uses Excel for one reason or the other.  I think it's a great tool, especially for organizing and tracking information, creating quick budgets, lists, etc. 

Sometimes at work I get a request for the ability to store Excel data into our database.  For example, we have a QR Scanner that imports data into Excel.  We also have a Scan History table where we store all scans.  Note, we are able to scan traditional bar codes directly into the database, just not the QR Scans.

So, the user wants to copy the scan data into our history database but also assign some other information to it, like a PO Number.  So one of programs I've created is actually pretty simple.

You create a "textarea" input and give it whatever dimensions that seem reasonable.  I also added a Po Number text field for this example. The trick is to separate the data by tabs (but make sure the data itself has no tabs within the cells).  Below is an example of how I separated 3 columns of data, which I then save into the database:

arySkid = split(SONums, chr(13)) 'SONums is the name of the textarea

'reCreate the array b/c there are carriage returns in the front of the skid nos after the first one listed

        for x = 0 to uBound(arySkid)
          if x = 0 then
  SkidNos = trim(arySkid(x)) & ","
  else
  if len(trim(arySkid(x))) > 1 then
     SkidNos = SkidNos & right(trim(arySkid(x)), len(trim(arySkid(x))) - 1) & ","
  end if
  end if
        next

        SkidNos = left(SkidNos, len(SkidNos)-1)

        arySkid = split(SkidNos, ",")

'HERE WE START LOOPING THROUGH EACH PART

for x = 0 to uBound(arySkid)

             xLen = len(trim(arySkid(x))) 'LENGTH OF THE ENTIRE STRING (PART NO AND QTY)
             SpaceStarts = InStr(trim(arySkid(x)), chr(9)) 'SHOWS WHERE TAB STARTS B/W PART NO AND QTY VALUE

             xPartNo = left(trim(arySkid(x)), SpaceStarts-1) 'PART NO VALUE
             xSection2 = right(trim(arySkid(x)), xLen-SpaceStarts) 'second section of data
             xLen2 = len(trim(xSection2))

             SpaceStarts2 = InStr(trim(xSection2), chr(9)) 'SHOWS WHERE the 2nd TAB STARTS B/W serial no and heci

             xSerialNo = left(trim(xSection2), SpaceStarts2-1) 'serial NO VALUE

             xHeci = right(trim(xSection2), xLen2-SpaceStarts2) 'last section of data
         
             'INSERT DATA HERE

next