| Author |
Topic |
|
llee
Starting Member
2 Posts |
 Posted - 2009-01-12 : 10:32:04
|
| I got an error message stated that "the external data is not in the expected format" while I tried to import the .xls file into my sql server table. I did use SAS to massage this data. Is anything I can do to avoid this problem. Thank you. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-12 : 10:47:13
|
| declare @excelFileName varchar(100)declare @SQLRead varchar(1000)--works for *.xls or *.xlsxset @excelFileName= 'C:\temp\book1.xls'set @SQLRead = 'SELECT * INTO excelTemp FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+ @excelFileName + '; Extended Properties=''''Excel 12.0'''''')...[Sheet1$]'exec(@SQLRead)Select * from excelTempdrop table excelTemp |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2009-01-12 : 11:01:06
|
| moved from script library.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
|
|
|
llee
Starting Member
2 Posts |
Posted - 2009-01-12 : 11:17:51
|
Thank you for your quick reply.Should I run the listed code you have on SAS program?Could you please help me understand what's the purpose of the select query for? (select * into excelTEMP from opendatasource...)quote:
Originally posted by llee
I got an error message stated that "the external data is not in the expected format" while I tried to import the .xls file into my sql server table. I did use SAS to massage this data. Is anything I can do to avoid this problem. Thank you.
|
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-13 : 04:31:06
|
| This query is an SQL query to read from an Excel file and import it into a new table. The Select * Into will create a new table to match the structure of FROM source. If the table you wish to import into does not exist then just change excelTemp to a name of your choice and remove the drop table statement.If your table already exists then INSERT into it from the contents of excelTemp, before the drop table statement.I am not familiar with SAS. It may be easier to run the SQL inside a stored procedure and call procedure from SAS. |
|
|
|
lshearer
Starting Member
2 Posts |
Posted - 2009-02-05 : 15:52:22
|
quote:
Originally posted by darkdusky
This query is an SQL query to read from an Excel file and import it into a new table. The Select * Into will create a new table to match the structure of FROM source. If the table you wish to import into does not exist then just change excelTemp to a name of your choice and remove the drop table statement.If your table already exists then INSERT into it from the contents of excelTemp, before the drop table statement.I am not familiar with SAS. It may be easier to run the SQL inside a stored procedure and call procedure from SAS.
|
|
|
|
lshearer
Starting Member
2 Posts |
Posted - 2009-02-05 : 15:56:15
|
quote:
Originally posted by lshearerI have a similar issue. I'm reading an excel file created by a vendor application that allows the user to save their reports in excel. When I try to connect to the file using ADO in C#, I get the 'Not in expected format' error. But if I open the excel file, click on any cell, and save it, I will not get the 'Not in expected format' error the next time I run the c# code. Is it possible the vendor is not properly closing the file after exporting it. If so, How do I fix this? This process will run every day; I don't want to have to manually open and save each daily file.Thanks.
quote:
Originally posted by darkdusky
This query is an SQL query to read from an Excel file and import it into a new table. The Select * Into will create a new table to match the structure of FROM source. If the table you wish to import into does not exist then just change excelTemp to a name of your choice and remove the drop table statement.If your table already exists then INSERT into it from the contents of excelTemp, before the drop table statement.I am not familiar with SAS. It may be easier to run the SQL inside a stored procedure and call procedure from SAS.
|
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-02-06 : 11:24:17
|
| you could try to copy files programmatically to see if that fixes your problem. e.g using DOS command "copy C:\temp1\*.* C:\temp2If this works you could run inside query or on a schedule etc. |
|
|
|
|
external data is not the expected format