Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Importing inconsistent excel data into a sql server table

Posted on 2014-07-24
11
Medium Priority
?
181 Views
Last Modified: 2014-11-16
I have a bunch of spreadsheets with info listed in multiple rows and columns, similar to the attached file.  Each sheet represents a box with multiple files in it.  The box number is listed 1 time in column A.  Then column D starts the specific data for a file.  Some rows have data in columns E, F and/or G. Some files have 1 or 2 rows of info, others may have 3, 4 or 5.  (See attached).  The last row of data for each file contains "Attorney Initial(s)" in column D and "Destroy Date" in column G .  

What I'm trying to do is develop a process that can read the info and import it into a table where each "File" is in one row.  Column Headers are:

Box #
Description
Attorney Initials
Destroy Date
Client # (the data in column G with numbers)

Thanks.
Sample.xls
0
Comment
Question by:RavenTim
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40218796
If someone asked me to do that I'dd write some VBA code in Excel to process the data. This macro can then loop though all the Excel files, open them one by one an loop through all the sheets in the Excel files. Then it loops though all the rows and columns of the sheet and extracts the data and inserts it into SQL server. When you have never created VBA code before this is quite a big task. You might consider hiring someone to do this for you. The code should do something like this:
Loop through all the .xls files in a folder
   Open the file
   Loop though all sheets in the file
      Get the box number from column A
      loop though all the rows
          if first row or new file 'remember' client number from column G
          if not add and 'remember' all the desciptions
          until column d starts with "Attorney Initials" then get the initials and the destoy date from the row and store all the data in SQL server
          start with the next file

Might not be 100% correct but you'll need some kind of rule set like this. Also keep in mind that a slight variation in the structure that would be totaly clear to a human (having 'DFG (Attorney Initials)' in a field somewhere, would create undesired results.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40219056
Concur with Nicobo.

From your example, what you would like is 2 records or 3?  The inconsistency that row 6 does not have any initials but has text above it would be a complication, but could simply leave that field empty.

I assume that the "Box #" field would contain "Tub 7 - J Cool", "EPA", "Tub 8 - Kelly Hump"

But would you want to compile all of the info from Cells D8, E9, D9, D10, D11, D12 into a single description field?

Also, the 1721.002 is a bit confusing as a "Destroy Date".  That value might mean something in the legal profession, but is hard to fathom as a "date" field.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 40219098
And I agree with Dale. It's not totally clear how you would like to process the data. I think it would help if you described the exact result (so the data in the SQL table) that you would like from the Sample.xls. Don't forget that you might have been working with these sheets for hours/days/months/years but we see them for the first time.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 12

Expert Comment

by:James Elliott
ID: 40220266
I normally love this sort of thing, but I agree with the above, your data is a complete mess. I think you need at the very least (for a free solution) to provide an annonymised file of your data so we can see the full problem, and, a draft mock up of how you want it to look after.

Thanks
0
 

Author Comment

by:RavenTim
ID: 40230037
Hey Guys, sorry, but I was on vacation and been catching up... I'll get some more info together & post it later today.

Thanks.
Tim
0
 

Author Comment

by:RavenTim
ID: 40230411
Ok.   I've attached two different samples showing the data as is (sheet 1) and the "Desired Result" (sheet 2).  I'm not sure how doable this is going to be as I have about 1500 separate files to process and there is little consistency.  Please see attached samples.

Thanks.
Tim
BGL-Sample.xls
BGL-Sample2.xls
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 2000 total points
ID: 40230498
I'm slightly confused by the first file. Are we to assume that rows 80 onwards should have been completed in the pro forms cells above? Is this how the files differ? Ie. People have chosen to adhere to the template at random?

if so i fear a quick generic approach is not going to be possible.

If u were able to provide a list of every partner initials and surnames then a generic routine might hit 70% accuracy but I'm guessing the final outcome needs to be better than that?

I'd suggest getting a fast-typing temp for a week :/
0
 

Author Comment

by:RavenTim
ID: 40230517
:-)  That's kind of what I thought...  

Thanks!
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 40445447
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question