Solved

The file format and extension dont match

Posted on 2014-09-24
22
1,267 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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
 
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 46

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

791 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