Solved

import xlsx file contents into a table in sql

Posted on 2010-09-23
5
996 Views
Last Modified: 2012-08-13
hi guys,

i have a excel file that gets outputted from a accounts package , it has 12 columns in it but only 6
have information that i want, i have a sql table called lnkinvoices which has 6 fields property1,property2,property3,property4,property5,porperty6 . is there a sql query that can get the
excel file from a location and import its contents into 6 colums into the table lnkinvoices.
im no sql expert so if anyone could explain as simple as possible

thanks
0
Comment
Question by:eitrax
5 Comments
 
LVL 1

Accepted Solution

by:
amarsale earned 167 total points
Comment Utility
OPENROWSET command is used to do import/export with excel file.

Please check this link ->
http://www.mssqltips.com/tip.asp?tip=1202
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 333 total points
Comment Utility
Yeah, I would start with openrowset as well...

it looks like :

Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\order_worksheet.xlsx;HDR=Yes', 'SELECT * FROM [order_worksheet$]') as a        

and the openrowset() part is just like any table - e.g. you can select individual columns - they should be the first row.

It is a little picky on how it will read data, and there is always "excel" knows best when it comes down to deciding what type of column the spreadsheet really is. So, if you have mixed mode columns, then you might end up with a few unexpected NULLs.

The ACE driver is the one to use. It is the latest version of the JET driver.
0
 
LVL 15

Expert Comment

by:MohammedU
Comment Utility
How to import data from Excel to SQL Server
http://support.microsoft.com/kb/321686
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 333 total points
Comment Utility
If looking for the ACE drivers, can get from : http://www.microsoft.com/downloads/en/details.aspx?FamilyID=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en  Office has both 64 and 32 bit, but use the provider 'Microsoft.ACE.OLEDB.12.0'

And just to show a few little options, have attached a spreadsheet, and note the behavior of COL1, COL2 and COL3 (the last three) in terms on "excel knows best" Col1 is a straight mostly numeric column, Col2 is a replicant but with numbers as text, Col3 is the variable behaviour based on the imex setting.

It is control by a registery setting as to how far excel scans a column before deciding what type it is and there is also an import export setting you can add...

e.g. with IMEX=1

Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\order_worksheet.xlsx;HDR=Yes;IMEX=1', 'SELECT * FROM [order_worksheet$]') as a

e.g. without IMEX=1

Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:\order_worksheet.xlsx;HDR=Yes', 'SELECT * FROM [order_worksheet$]') as a

and notice mainly the last column COL3 where it shows up NULL values much like col1 because "excel knows" it is meant to be numeric and those "characters" clearly arent numeric (now why on earth it doesnt allow a normal varchar type is beyond me).


order-worksheet.xlsx
0
 

Author Closing Comment

by:eitrax
Comment Utility
thanks for help again
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now