Skip to main content

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

Comments

Post a Comment

Popular posts from this blog

Alexa Is Listening, Recording, and Sending!

Check out this story where a couple in Oregon was having a private conversation and Alexa recorded and sent the conversation to one of their contacts!  They were not aware until the contact contacted them about what had happened.  The couple contacted Amazon and they verified the series of events.  But what's even more bizarre is the couple wanted to return their Amazon home devices but Amazon has not agreed to return the costs for the devices! Check out the article here  by Bruce Brown on  Digital Trends
Post a Comment
Read more

Nonclustered Index in SQL Server

So, I've run into an issue with one of my queries because it takes about 40 seconds to run and according to the Recent Expensive Queries section of the Activity Monitor in SQL Server, there is a section of code taking up about 51% of the query.  First, let me say that I've been writing Transact SQL for quite awhile but have never ventured into the Clustered and NonClustered areas of forming Indexes.  I do use indexes on all my tables but that's just the regular Primary Unique key.  We've been using this query for awhile and it does work but when the result set is about 5K records it takes a little while to run.  So here's the actual query itself SELECT PartNo , SerialNo , HeciCode , ScanDate , OrderType , PoNo , po . IssueDate , SoNo FROM ScanHistory sh WITH ( NOLOCK ) LEFT OUTER JOIN trkRWPOHeader po WITH ( NOLOCK ) ON sh . PoNo = po . PONum WHERE len ( PoNo ) > 2 AND ScanDate >
Post a Comment
Read more

File Size Upload Limits using Godaddy Hosting

I've been working on a new project for a friend of mine and came across an issue with trying to upload photos using the infamous freeASPUpload code.  I've used the code in the past for some past projects but don't recall having issues with limits on uploading files. I'm posting this on my blog b/c there's nothing on the internet that discusses this (or at least I wasn't able to find any explanations), and hopefully this can be a resource to someone else that comes across the same issue. Now I know there are other options, utilities, etc. for uploading pics/files/etc. but  freeASPUpload  is simple, I've used it in the past, and this project is pretty basic.  So, if you want to change the upload file size limit you will first want to go into your Godaddy's hosting account.  Next go into the Virtual Directories option, then click on Directory Properties.  Towards the bottom there is a section called "Maximum size of entity body of an ASP request
Post a Comment
Read more