January 19, 2012 at 6:10 am
Goodday,
I am pretty new to SQL having just started, but i have managed to create a database with linked tables and foreign key connections.
However now i have run into the problem that there is an excel file that contains the information we need.
It contains the column Branches which houses the names of the different departments that are part of it.
However in the SQL table the branches column is denoted with a numerical value due to its linkage with its parent table Branches which contains more information.
What i would like to learn if its possible to use a query to search the excel column branches with the branches table in sql for similar values and replace those values with the correct Identifier?
I hope i am making it clear and if not then please let me know so i can try to create a more visual display of what i mean
January 19, 2012 at 6:20 am
What I would do is this:
1. Save this spreadsheeet as a CSV (importing Excel sheet into a database is not so easy)
2. Import the file to a staging table using SSIS.
3. Perform transformations and load your database from this staging table again using SSIS again.
Rob
January 19, 2012 at 6:31 am
You can also open the Excel document directly from SQL Server and SELECT from it just like using a table.
From: http://support.microsoft.com/kb/321686
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])
SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
January 19, 2012 at 6:49 am
If you are using excel 2007 , i.e .xlsx format file, use below code.
SELECT * INTO ExcelTable FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\test.xlsx', 'SELECT * FROM [Sheet1$]');
Note: To be able to import directly to sql server, you must keep your Excel file on the server where sql server runs, or a shared path that has access to sql server.
January 19, 2012 at 7:05 am
Given Excels lack of true data types, I am not a big fan of the OPENROWSET approach to your problem. I've seen more than than enough problems with numbers being mistaken for text and nulling out when they arrive in SQL to go with Rob's suggestion.
I have no doubt OPENROWSET would miss some data, especially if you're using 64 bit sql, which does not like Excel one bit.
January 19, 2012 at 7:22 am
Thank u all for this information. Importing an excel i will use SSIS so far i have run a few trials and have not run into any trouble with datatypes due to the simplicity of the files.
I would like to know however if there is a way to change data when u import it.
I will try to make a small layout of what i mean:
SQL Database:
(excuse the tablenames they're mostly in dutch)
Table: Branches Column: BR_ID and Branches
Table: Catering: Column: BR_ID and etc.
There is a foreign key relation between the 2 BR_ID of the 2 tables where the Branches(BR_ID) is the primary key.
Now when i do the excel import however the excel column Branches contains the data used in the Branches column.
I would like to know if with an import i can chance the information in the excel column Branches into the corresponding BR_ID numer in the SQL Branches Table?
January 19, 2012 at 7:42 am
It's all in the mapping. If you're using an actual SSIS package (as opposed to the Import/Export Wizard), you create a file connection manager with different names for the columns, then point those file columns to different columns in your staging table (under the Data Flow Task).
Don't do your data splitting until after importing the Excel file.
Your life will be much less complicated if you do the import first and the data splitting after.
January 19, 2012 at 7:48 am
Thank u,
Guess the best method would be then to just import it and then use queries to join the tables together so the right information get placed into the values that havnt been filled from the corresponding other tables.
Well not sure if my english is making much sense, but I do think i am getting the general direction i have to go in now.
Thank u all very much for the information so far, hopefully i'll become alot better and be able to help others as well.
January 19, 2012 at 7:50 am
Your English is fine. I understand you and you understand what you need to do.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply