Load data from Excel which has columns in multiple rows
-
Hi All, I have an Excel file in which there are multiple sheets and its columns are spread in multiple rows like a parent has multiple children and those children have their children. How can I load this data in to a table sheet by sheet, means for each load if it loads from one sheet that's fine but important thing here is columns are spread into multiple rows. I am also searching, but any link, any suggestion or code snippet is going to be very very helpful. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I have an Excel file in which there are multiple sheets and its columns are spread in multiple rows like a parent has multiple children and those children have their children. How can I load this data in to a table sheet by sheet, means for each load if it loads from one sheet that's fine but important thing here is columns are spread into multiple rows. I am also searching, but any link, any suggestion or code snippet is going to be very very helpful. Thanks in advance.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Sorry I didn't mention it may be but I want to do it SQL Server script. So I am trying in the following way
SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=D:\Servxxx.xls',
[Servxxx First Sheet$]);It is giving me the following error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".It is saying file is not there, but file is there and it is erroring out with link server etc.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Sorry I didn't mention it may be but I want to do it SQL Server script. So I am trying in the following way
SELECT * INTO #Servxxx
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml; HDR=YES; IMEX=1;
Database=D:\Servxxx.xls',
[Servxxx First Sheet$]);It is giving me the following error
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'D:\Servxxx.xls'. Make sure the object exists and that you spell its name and the path name correctly. If 'D:\Servxxx.xls' is not a local object, check your network connection or contact the server administrator.".
Msg 7303, Level 16, State 1, Line 10
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".It is saying file is not there, but file is there and it is erroring out with link server etc.
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Is
D:a local drive, or a mapped network drive? Does the SQL service account have permission to access the file?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Is
D:a local drive, or a mapped network drive? Does the SQL service account have permission to access the file?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Hey Rick, thanks I got it sort out my friend here is the sample one may be somebody has the same issue. SELECT * INTO #xxxxxxs FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=YES; IMEX=1; Database=C:\xxxxxxx\Dont Remove ThisFolder\xxxxxxs.xlsx', 'SELECT * FROM [Back xxxxx$]'); I have a question here, do we have any way to copy the existing schema structure into a table variable, along with an identity column?
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
