Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

import xlsx file contents into a table in sql

Posted on 2010-09-23
5
Medium Priority
?
1,006 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 668 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 1332 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 1332 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 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