Solved

Importing Excel Data To SQL Server

Posted on 2009-04-14
15
409 Views
Last Modified: 2012-06-27
Hello Experts,
I have an Excel document. But I could not import this Excel document to SQL Server. First 38 rows start with 0.And my database table cannot accept these values as char.
When I replaced the number which start with 0 to '0 in Excel then I can import the full data..
Is there any short way to succeed this?

You can easily see my problem when you try to import the attached xls. file

Regards
Test.xlsx
0
Comment
Question by:innocent1973
[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
  • 8
  • 4
  • 2
  • +1
15 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135640
you can make linked server and it should work.  example in below link shows access linked server but you can make same for excel and can directly fire select....into query to populate your sql table.

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html
0
 
LVL 4

Expert Comment

by:barryhiggins3
ID: 24135656
You can modify the table's column in your SQL Servers database to "allow nulls" (there's a tick box) and this should let you import the 0 value.
0
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24135671
export your data to a tab delimited text file and use the BCP tool to import the data

From the command prompt:
BCP in filename.txt -c -S .\InstanceName -T

in says your importing
-c specifies that it's a character type file
-S specifies the server\instance.  If it's a default instance you can omit this.
-T means you're using your login credentials.  If you need to use -U and -P instead if necessary
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135693
you can have this script, just put your test.xlsx file in c: drive.


EXECUTE sp_addlinkedserver
@server = 'ExcelLink',
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'C:\Test.xlsx',
@provstr = 'Excel 12.0'
GO
 
 
EXEC sp_addlinkedsrvlogin 'ExcelLink', 'false'
go
 
--list all tables
exec sp_tables_ex 'ExcelLink'
go
 
--see table in sheet1
SELECT * FROM [ExcelLink]...sheet1$
go
 
--create table in SQL
select * into tempTable FROM [ExcelLink]...sheet1$
go
 
--alter column to varchar
alter table tempTable
alter column department varchar(10)
go
 
--update
update tempTable set department='0'+department where len(department)<4
 
--check your SQL table
select * from tempTable

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135714
BTW, once you make linked server of your xlsx file, you will have lots of power in your SQL Server to access your Excel data. You can create temporary or permanent table, you can join excel table with SQL table right from your SQL Server. You can use insert, update and delete query directly.

Actually you don't need all this things but this is just for your kind reference.
0
 

Author Comment

by:innocent1973
ID: 24135762
When I try to use your script then I get the following error:

Msg 7403, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135773
you have to have provider installed in your server, check your server for that. look at attached screen shot. or try using jet.oledb.4.0 by saving your file XLS from XLSX
jpg.JPG
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135782
or you can download driver and install it from

http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

don't forget to check server property after installation.
0
 

Author Comment

by:innocent1973
ID: 24135790
Dear experts,
When I convert this xlsx file to Office 2003 format then I get the same results..I could not import the all data.
I think this really something about the filed data type not with the linked servers
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135810
I just imported your file and tried I got temporary table with float datatype, i converted it to varchar and update the records. have you done that as I have explained in my script?
0
 

Author Comment

by:innocent1973
ID: 24135813
I have changed first 38 rows manually in Excel. I have changed records such as:

0101  ---->  '0101

and it worked.. But the problem is if I have more than 1000 rows how can I import this data?

 
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24135838
you don't need to do it manually, I have done it with script. you will not get 0101 in your imported table from linked server, instead you will get 101. So, I changed the datatype of tempTable to varchar and update all 38 records with T-Sql script.
0
 
LVL 4

Expert Comment

by:barryhiggins3
ID: 24135839
It sounds like the easiest solution would be to change the data type of the colum to nvarchar or similar.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24135856
yes, but not directly in excel but in SQL as I did in my post # 24135693
0
 

Author Closing Comment

by:innocent1973
ID: 31569800
your previos script worked perfect..Thank you ..
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 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