May 18, 2012 at 2:06 pm
Hello All,
I wouldl ike to know how to export the data from excel sheet to database. I have already a table structure in the database and now I would like to Export the data from excel sheet. I can do by Import and export wizard.
But the problem is with the data types conversion. I am receiving as nvarchar. The other option I found is by copy n paste onto database. But the real problem is I have around 100 tables to work on, I can't keep on copy and paste onto database. Do we have any solution where we can export the data from excel sheet to database, where the data types won't get changed. Please, suggest me some solution. Thank you.
May 20, 2012 at 11:31 pm
If the problem occurs again, Just save as the Excel sheet as Tab Delimiter Text file then u start to load as Flat File source, That wont give u error,
May 21, 2012 at 6:46 am
NOTE: Important - This example is for an exclusively x64 environment without any Office x86 parts and with this
http://www.microsoft.com/download/en/details.aspx?id=13255
installed
********************
INSERT INTO ... Your_Table
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
********************
See more here :
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
May 21, 2012 at 9:10 am
This query looks good. But, if we have 100 tables in a folder, do we need to keep change the table name, pathname for all 100 tables manually?
May 21, 2012 at 9:37 am
DBA_SQL (5/21/2012)
This query looks good. But, if we have 100 tables in a folder, do we need to keep change the table name, pathname for all 100 tables manually?
That's kinda up to you. I would build SPs and call those through nightly SQLAgent runs, or something like that.
It's up to your own creativity. 🙂
May 21, 2012 at 9:41 am
Tried this script, but didn't worked..can u help out plz..
Declare @Tabname nvarchar(100)
set @Tabname = 'C:\..\...\packages\*.xls' --Given path of the folder
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\..\...\packages\@Tabname.xls', '@Tabname')
May 21, 2012 at 9:56 am
DBA_SQL (5/21/2012)
Tried this script, but didn't worked..can u help out plz..Declare @Tabname nvarchar(100)
set @Tabname = 'C:\..\...\packages\*.xls' --Given path of the folder
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\..\...\packages\@Tabname.xls', '@Tabname')
What error messages are you getting?
What environment are you working in? x64/x86/OS/SQLVersion
May 21, 2012 at 10:04 am
Error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.".
Msg 7321, Level 16, State 2, Line 5
An error occurred while preparing the query "@Tabname" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Environment:
SQL Server 2008 R2
Windowx xp.
May 21, 2012 at 11:19 am
I tried this..but receiving the following errors:
create proc up_exportdata
as
begin
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='Files')
DROP TABLE Files
CREATE TABLE Files(FileID INT IDENTITY NOT NULL, FileName VARCHAR(max))
DECLARE @PathExec VARCHAR(1000)
SET @PathExec = 'C:\..\..\..\packages\"+@PathFolder+".csv /B'
INSERT INTO Files(FileName) EXEC master..xp_cmdshell @PathExec
DELETE Files WHERE FileName IS NULL;
--DECLARE @PathExec VARCHAR(1000)
--SET @PathExec = 'C:\Documents and Settings\vpasnur\Desktop\packages\"+@PathFolder+".csv /B'
DECLARE @RowCount INT, @I INT
SET @RowCount = (SELECT COUNT(FileName) FROM Files)
SET @I = 1
--Step 3: Loop through the rows of a table and execute sp_ResultsDump for each file
WHILE (@I <= @RowCount)
BEGIN
DECLARE @FileName VARCHAR(1000)
SELECT @FileName = FileName FROM Files WHERE FileID = @I
-- SELECT @FileName = @PathFolder+@FileName
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\..\..\..\packages\*.@FileName', '@FileName')
SET @I = @I + 1;
end
end
Error:
Msg 7399, Level 16, State 1, Procedure up_exportdata, Line 28
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Procedure up_exportdata, Line 28
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
May 21, 2012 at 11:48 am
--Tried this atleast to enter the table name, but receiving an error, of incorrect syntax,...
create proc Up_Export
(
@Tablename as varchar(20) = null
)
AS
SET NOCOUNT ON
begin
INSERT INTO @Tablename --Receiving error over here, informs incorrect syntax near @tablename
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\..\..\..\packages\@Tablename.xls', @Tablename)
set nocount off
end
May 22, 2012 at 12:46 am
DBA_SQL (5/21/2012)
Error:OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.".
Msg 7321, Level 16, State 2, Line 5
An error occurred while preparing the query "@Tabname" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Environment:
SQL Server 2008 R2
Windowx xp.
1. I assume both (OS and SQLServer) are x86 ???
2. Don't try to pack it all into one package. While it's a bunch of work, set up one SP per table/file. You can later call the sp easily and maintain it just as easily should changes occur.
3. You can pack more tables/files into one package if you use SSIS. That would be the other approach.
4. Have you ever successfully executed a 'select * from openrowset' in your environment?
May 22, 2012 at 12:00 pm
I tried to run these script individually and able to load the data. But, have to see how to create a variable, where it takes table name. But, if we have any identity column exists on table, we need to manually insert columns and set identity property ON.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply