?
Solved

import xlsx file contents into a table in sql

Posted on 2010-09-23
5
Medium Priority
?
1,005 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

801 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