Import Excel File to SQL Server

Question

Post reply

Import Excel File to SQL Server

monilps

SSCommitted

Points: 1686
More actions
May 15, 2013 at 9:02 am

#277735

I am having trouble while importing excel file to SQL Server.
Excel File:
ColumnA
--------
b2
2
a2a
2aa
When I import...it comes as
ColumnA
--------
b2
a2a
2aa
Just number "2" doesn't comes.
I tried datatype mapping as varchar(512)
and if I change datatype to float then only numeric value comes and rest of data is comes as blank. I need to all the data.
Can you please tell me which datatype should I use.
Thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

timotech SSCrazy Points: 2698 More actions · Answer 1

import first to access then now import to from access to sql server using your import/export wizard

Tim

subhajeetsur SSC-Addicted Points: 453 More actions · Answer 2

You can try bulk insert or bcp in command and specify the datatype of the column as varchar(max)

Miles Neale SSChampion Points: 13147 More actions · Answer 3

The first question is what did youtry in the first attempt? Did you load through Access, SSIS, or the upload functionailty of SQL Server itself?

Not all gray hairs are Dinosaurs!

dineshvishe SSCrazy Points: 2308 More actions · Answer 4

1) use DTS

2) use linked server make linked server using following query.

EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL', @srvproduct=N'E', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'c:\Employee.xls', @provstr=N'Excel 8.0'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXCEL',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCEL', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

select file sheet =>SELECT *

FROM [EXCEL]...[Sheet1$]

GO

monilps SSCommitted Points: 1686 More actions · Answer 5

monilps

SSCommitted

Points: 1686

May 20, 2013 at 12:24 pm

#1616741

Thank you guys..I was able to load the data...

Lowell SSC Guru Points: 323504 More actions · Answer 6

Very doubtful the below suggestion will work, as any 64 bit SQL machine requires the new ACE drivers.

There are no 64 bit JET drivers, so unless you explicitly installed a 32 bit SQL 2008 version, you need a different provider.

dineshvishe (5/20/2013)
1) use DTS
2) use linked server make linked server using following query.
EXEC master.dbo.sp_addlinkedserver @server = N'EXCEL', @srvproduct=N'E', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'c:\Employee.xls', @provstr=N'Excel 8.0'
{snip}

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!