July 1, 2010 at 1:50 pm
Hi
I'm new to SQL, so I dont know much about that. If you guys can help me with my question, I'll really appreciate that.
Basically I generate reports by merging various data files(.xls and .cvs) into one single file (.xls). I'm doing this my cut,copy,paste. But since the data is huge, so it time consuming.
I want to use Microsoft SQL 2008 for this. I want to build an Automated procedure to import all the data (.xls and .cvs) into the database from various sources and arrange it into the way I want so that I can use Reporting Services to build reports from that data.
So if you guys can give me any ideas to do that, I'll appreciate that.
Thanks
July 1, 2010 at 3:25 pm
July 1, 2010 at 3:56 pm
Novicejatt (7/1/2010)
I'm new to SQL, so I dont know much about that.
You may want to consider hiring a temp DBA, work with him/her in this project and take advantage of the learning opportunity 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2010 at 4:49 pm
Using Excel and CSV files as a permanent data store is ill-advised. Using Excel or CSV for data entry is even worse. The fact that you need to bring data from Excel/CSV into a database on an automated schedule suggests that you are doing one or both.
I do understand that sometimes you are stuck with what you were given, but you can push for a better data storage model. It may take time, but in the long-run it will save you a lot of hair-pulling.
First of all, data should be stored in a database. Excel is free-form and doesn't enforce any constraints on the data, data types, or even where you can put data on the worksheet. You can protect a sheet to enforce some of this, but it will always fall short. If someone inserts a column, your import process fails. Or worse, it keeps running while silently cranking bad data. You want to avoid this. There's a lot of other short-comings, but I think it's better to look at the advantages: SQL Server has forces data types, data constraints, foreign key constraints, triggers, and a whole host of robust functions all suited for one purpose: good, easy to manage data.
The other major problem with Excel is that only one person can modify the worksheet at a time. I'm sure you've already run into this, and it can be a real pain to manage.
So you put your data in a database, how then do your users modify the data? Well, this is definitely the hard part, but there are some fairly easy solutions. Probably the easiest is by using a Microsoft Access .ADP file (Access Data Project). You can create a new ADP from the new dialog, and it will immediately ask to connect to a database. All the tables in the database are instantly available, and you can start building forms to modify the data. The nice thing is your user base probably already knows Access fairly well. Even better, you probably already know Access quite well and can build forms fairly quickly. The other nice thing is it can use Windows integrated security, and will enforce the database access restrictions at the application level with no login. I have a "Lookup tables" .ADP file where I work. Several lookup tables are available for users to modify (like state to sales region mappings). The foreign key relationships of the host database are of course enforced, so I can force them to pick valid state and sales region names (and Access lets you build handy drop-down lists). All of the tables are available to edit, but only if the Windows user has access. I have one database role for each group of tables. Users get assigned to these roles when they need to modify the data. Users can't modify data they don't need to. You could accomplish the same security in Excel by using Windows security, but I doubt you do, because it's much harder to manage. An ADP file offers a lot, while still being very quick to deploy; usually one to a few days is all. One thing you must know however: your tables must have primary keys, or they will be read only in Access.
An even more robust option would be offering a web interface, but this will require more time and money. Microsoft could discontinue support for ADP files, but HTML isn't going anywhere. If you really need a lot of data modified, though, a good web interface really is your best choice. I highly recommend just grabbing some third-party app that does it all. I don't have a specific in mind, but you should be able to find one that works just like Excel and integrates with Windows security. This will offer a high level of comfort for your users. It shouldn't be too expensive, which would make it easier to sell to your boss.
You could of course program you own interface, but you're not going to program something that truly works like Excel and integrates with Windows security. You're just not. So don't.
Once you have the data in your database (where it belongs), and you're your users the ability to modify it, reporting on it is easy. Although they might not immediately want to give up their precious Excel, in the end they will thank you because you can give them truly great real-time reporting. Oh, and two people can modify the same table at once.
--J
July 1, 2010 at 5:04 pm
jvanderberg (7/1/2010)
Using Excel and CSV files as a permanent data store is ill-advised. Using Excel or CSV for data entry is even worse. The fact that you need to bring data from Excel/CSV into a database on an automated schedule suggests that you are doing one or both.
Your conclusion is a pretty big assumption. The initial post didn't make it sound like a dynamic edit-able environment, he's just trying to figure out how to get that data into SQL Server.
Often times, the need to import CSV or XLS files comes as a result of not having control over the company systems as a whole and needing to get data extracted from other systems (hopefully in an automated manner) into the SQL environment.
July 7, 2010 at 1:45 pm
Thankyou Very Much Guys. I will use SSIS for that purpose.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply