CODEWITHSUNDEEP
c#sql-servervbaexcel
Vityata
Vityata

Reputation: 43575

SQL Server : a way to present an Excel table into SQL Server

I have like 50 different by size, columns and rows Excel files. Roughly about 400 rows and 70 columns each (+-20%). The columns are named differently, the rows are also named differently.

I am looking for a way to move all this fun into SQL Server and to do stuff there. E.g., I would like to download the files from SQL Server to Excel, to make changes (even in the headers of the rows) and to upload them back. With VBA or C#.

My question is about the upload - does anyone knows how to do it? Have you done something similar? Should I do something like this (see code in one article I wrote some time ago - http://www.vitoshacademy.com/mssql-import-data-from-excel-to-sql-server-with-vba - if this is considered to be spam I will remove it) or there are more advanced way to do it?

Roughly speaking, these are my questions:

  1. What should I be careful with?
  2. What are the possible mistakes, I should try to avoid?

  3. What would you do, if you had a task like this? E.g.:

    3.1. Create a table pro Excel file;
    3.2. Read the headers of the Excel file,
    3.3. Create columns with them and fill the data.

Or something else? I am not interested in great performance or so ever, up to 5 seconds waiting time is quite ok.

I know the question is too broad and etc., but any ideas would be appreciated.

Thanks!

Upvotes: 0

Views: 171

Answers (1)

daZza
daZza

Reputation: 1689

I am looking for a way to move all this fun into SQL Server and to do stuff there. E.g., I would like to download the files from SQL Server to Excel, to make changes (even in the headers of the rows) and to upload them back. With VBA or C#.

Why would you want to "download files" from a database to change the contents and then reupload them? That defeats the whole purpose of a database...

I suggest you work exclusively in the database and edit your data via proper SQL statements or, if you are uncomfortable with that, using a tool like SQL Developer or SQL Server Management Studio.

What should I be careful with?

Mixing up Excel and a database. They have different purposes and possibilites and should be used accordingly.

What are the possible mistakes, I should try to avoid?

See above. Check the data in your Excel sheets, it might need escaping because of special/reserved characters if you insert it into the database. Choose matching datatypes for the database columns. Make use of constraints to enforce a certain level of data quality. There's a ton more, if you are totally new to databases this might be a good start (I'm sure there are alot of other tutorials, just make sure to understand the basic concepts): http://www.techonthenet.com/sql_server/

Create a table pro Excel file;

Use a for-loop to go through all the files. If they are all in the same folder it should be rather straight forward, if they are distrubuted throughout the system you'll need to either have a configuration sheet with all the filepaths or you need to think if some other logic to identify the relevant files on your PC.

To open a file use the Workbooks.Openmethod in VBA. Assign the workbook to an object so that you can work with it more easily. This way you'll have access to all its properties and data. Use these to create a CREATE TABLE [...]SQL-Statement. This statement will create your table.

Connect to the database and execute said statement via the ADODB.Connection object.

The for-loop will do this for all your Excel files, so that there will be a database table for each Excel file.

Read the headers of the Excel file

This will be part of the step above as you'll most likely need the header information to create your tables (defining the columns, etc.). In case your "header" consists of the first row in a worksheet you can get the contents using the Worksheet.Cells or Worksheet.Range properties in VBA.

Create columns with them and fill the data.

Creating the columns will also be part of creating the table. Filling the tables with content could work rather similarly.

Use a for-loop to go over each row and create an INSERT INTO [...] SQL-statement for each row, then execute it (or if it's viable for you create an array to store all insert statements and execute them in bulk. I'd prefer this but it's more advanced than simply creating a statement and executing it immediately). Don't forget to commit.

If you have any concrete questions don't hesitate to ask. I hope this helps setting up the framework for your project. If you run into trouble while trying to implement certain aspects of the whole thing, there will most likely also be an existing solution somewhere on StackOverflow or Google. I think with VBA I've never ran into anything that has never been done before, there's plenty of resources available to get code snippets for specific problems.

Upvotes: 2

Related Questions