Solved

import xlsx file contents into a table in sql

Posted on 2010-09-23
5
1,002 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 1

Accepted Solution

by:
amarsale earned 167 total points
ID: 33745413
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
ID: 33746715
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
ID: 33749945
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
ID: 33750832
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
ID: 33768889
thanks for help again
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 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