Solved

The file format and extension dont match

Posted on 2014-09-24
22
1,094 Views
Last Modified: 2014-11-19
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
0
Comment
Question by:BrownRJ
  • 10
  • 7
  • 3
  • +1
22 Comments
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40341592
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40341702
> 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
 

Author Comment

by:BrownRJ
ID: 40341712
Thank you for the replies. What is the most accurate way of knowing how they are exporting it without depending on the exporter?
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40341790
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40341798
>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
 

Author Comment

by:BrownRJ
ID: 40341892
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40341937
< oops my bad >
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40341978
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
 

Author Comment

by:BrownRJ
ID: 40342045
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
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 500 total points
ID: 40342201
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40342203
How are you importing the file, SSIS?
0
 

Author Comment

by:BrownRJ
ID: 40342314
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
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40342637
When you have it working manually, what are the steps that work for you?
0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40342645
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
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40342684
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
 

Author Comment

by:BrownRJ
ID: 40349702
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
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40349966
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
 

Author Comment

by:BrownRJ
ID: 40349979
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
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 40350002
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40452140
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 

Author Closing Comment

by:BrownRJ
ID: 40452141
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now