Solved

import xlsx file contents into a table in sql

Posted on 2010-09-23
5
1,001 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

726 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