innocent1973
asked on
Importing Excel Data To SQL Server
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
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
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.
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
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
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
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.
Actually you don't need all this things but this is just for your kind reference.
ASKER
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.
Msg 7403, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.ACE.OLEDB.12.0"
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
jpg.JPG
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.
http://www.microsoft.com/downloads/details.aspx?familyid=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en
don't forget to check server property after installation.
ASKER
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
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
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?
ASKER
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?
0101 ----> '0101
and it worked.. But the problem is if I have more than 1000 rows how can I import this data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It sounds like the easiest solution would be to change the data type of the colum to nvarchar or similar.
yes, but not directly in excel but in SQL as I did in my post # 24135693
ASKER
your previos script worked perfect..Thank you ..
http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html