The file format and extension dont match

I get a data dump evey night from an outside source that I have little to no control over. They tell me that they are exporting the excel files as 2003. But when I try and open it in excel 2013 I get the attached error. I've tried everything I can think of and the only way I can get it to open without issue and subsequently get imported into a SQL table is to manually open the file , save it as 2003, and then manually import it into SQL. I'm looking to automate this process at 2am in the morning. Is there somewhere a log file exists that tells me what it "thinks" it is?
excelError.jpg
BrownRJAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jerry MillerCommented:
That happens when the file is being created from code and it isn't actually an Excel file. It could be a text (csv, txt, etc.) that they are naming .xls so that Excel will open it. Here is a way to get around it if you have access to the registry on that computer, but pay attention to the warnings as it does open that computer up to possible vulnerabilities.

http://support.microsoft.com/kb/948615
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> It could be a text (csv, txt, etc.) that they are naming .xls so that Excel will open it.
Building on the above statement, if this is the case it would be far better to keep the file as a .csv, .txt, etc. if that's what it truly is.  This way you avoid all of the Excel 32-bit vs. 64-bit handling issues in SSIS.
0
BrownRJAuthor Commented:
Thank you for the replies. What is the most accurate way of knowing how they are exporting it without depending on the exporter?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Jerry MillerCommented:
They are exporting it as Excel, but probably not actually building an Excel file using the correct object libraries for Excel. I have seen all too often where a programmer can't depend on (or doesn't know) the version of Excel on the server where the application resides. Then they build something that is creating some kind of HTML file using tables and simply renaming it .xls so their users will open it in Excel. This worked fine until the newer versions of Office (2007 and later). Now it is creating this error message.

I agree with Jim, if they would simply export it with the actual extension, it would be easier to work with.

If you can't get them to update their process, your only choice may to not display that message. It is an easy registry fix, but again that PC would open to possible issues in Excel. It could be an acceptable risk if it only one computer and a limited number of people use it.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>What is the most accurate way of knowing how they are exporting it without depending on the exporter?
I'm not aware of any process that can inspect a file and tell what kind of file/version it is, while ignoring the extension.   I suppose if you post that question in the Excel zone maybe one of the experts there have some code that pulls that off.
0
BrownRJAuthor Commented:
Jerry,

The registry works as it should on opening the Excel file but its still causing Sql to have fits when trying to import it. I do appreciate you help.

Jim,

I thought I was in the Excel zone.. LOL Is that spate from the Excel question area? Thank you as well.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
< oops my bad >
0
Jerry MillerCommented:
What kind of errors are you getting when importing?

I would Google 'SQL fits', but I am scared it would just be some DBAs or programmers throwing tantrums.  LOL
0
BrownRJAuthor Commented:
I've noticed that if I open the file in notepad that is at the top.

<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">ExcelError2.jpg  
<HEAD>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<style>


So it looks like they are doing a bulk export. But now I need to figure out how to import the bulk. I've attached the error.
0
Jerry MillerCommented:
It seems that they may be sending it as a text/html document with the .xls extension and not a true Excel file.

<meta http-equiv=Content-Type content="text/html; charset=windows-1252">

If you look at an Excel file that you have saved in 2003 or 2013 format in notepad, you can see the difference.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jerry MillerCommented:
How are you importing the file, SSIS?
0
BrownRJAuthor Commented:
I did notice that. I'm waiting to hear back from my provider at this time. But yes I'm trying to use SSIS to get the data in. But I haven't found anything that's happy with importing it the way it sits now.
0
Jerry MillerCommented:
When you have it working manually, what are the steps that work for you?
0
Jerry MillerCommented:
Can you post a sample of the file? If you need to do a find and replace to mask confidential data, please do so.  For example replace account numbers, dollar amounts, or any other identifying data that you don't want the world to see.

Also does your error message display anything relevant when you click on the detail icons in the left hand corner of the message box?
0
Jerry MillerCommented:
You could also automatically open the file and save it in the proper format in Script Task. There is a vbScript example in this question, but you can find similar code in other languages if you Google 'script rename Excel files'.

http://www.experts-exchange.com/Database/MS-SQL-Server/Q_27252393.html

Dim sSourceFile
sSourceFile="c:\BDE\BDE.xls"
sTargetFile="c:\BDE\BDE_newExcel.xls"
Dim oExcel
Set oExcel = CreateObject("Excel.Application")
Dim oBook
Set oBook = oExcel.Workbooks.Open(sSourceFile)
oBook.SaveAs sTargetFile
oBook.Close False
oExcel.Quit

C# & VB.Net examples here:
http://vijirajkumar.blogspot.com/2010/04/rename-excel-sheet-net.html
0
BrownRJAuthor Commented:
Jerry,

1. Open the Excel sheet.
2. Save the file as an Excel 2003 with the same name.
3. Open SMS
4. Click import into the database that I need it to import to.
5. Run the generic process to do an import and select the option of keeping the table but purging the data and NOT append.  

It would be great if I could post the excel sheet but all of the data has personal information.

 I may have to use the custom scripting to save it in the format I need it to. I will take a look at what you posted and let you know. I really appreciate your help with this.
0
Jerry MillerCommented:
Sorry, I thought that you were using SSIS and not the SMS import process. It could be a 32-64bit issue. I have found a lot of references that seem to address it. If your computer is 64bit, this link may point you in the right direction.

http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx
0
BrownRJAuthor Commented:
Jerry,

To get my data in right now I'm using SMS. But Id like to move it to a scheduled job within sql. My apologies as I think I mudded the waters on how I'm getting the data in as opposed to how I want to get the data in.
0
Jerry MillerCommented:
It is not a problem that is part of the process, getting everyone on (or near) the same page.

Once you figure out how to get the new version in using your current process, we can get it working via SSIS. I use SSIS because our DBAs won't allow us to directly import using SMS, so I naturally assume that SSIS is being used.
0
Martin LissOlder than dirtCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
BrownRJAuthor Commented:
I was able to get them to change the export to CSV. Not what i really wanted but it works. Thanks for all your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.