May 15, 2013 at 9:02 am
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.
May 15, 2013 at 9:28 am
import first to access then now import to from access to sql server using your import/export wizard
Tim
May 16, 2013 at 1:10 am
You can try bulk insert or bcp in command and specify the datatype of the column as varchar(max)
May 16, 2013 at 2:45 pm
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!
May 20, 2013 at 12:22 pm
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
May 20, 2013 at 12:24 pm
Thank you guys..I was able to load the data...
May 20, 2013 at 12:25 pm
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 DTS2) 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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply