External table is not in the expected format- C#,excel

[still1]

Hi,

I am facing a strange issue while processing an excel file using C# code. We have a scheduler that has a file watcher.

The file watcher looks for the specified file in a shared directory and when found runs a C# code(console app). The user manually place the file at that location.

This feature works fine when I(and all IT folks) put the file at this location but when a business user do it we are getting this error.

"External table is not in the expected format."

The scheduler have complete access to the path and run as an admin.

It definitely is not a format issue since i used the same file to process from my login and it worked. below is the code for excel read

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=" + args[0].ToString() + @";Extended Properties=""Excel 12.0;HDR=YES;""";

string strSQL = "SELECT * FROM [" + ConfigurationSettings.AppSettings["ExcelSheetName"] + "$]";

OleDbConnection excelConnection = new OleDbConnection(connectionString);

excelConnection.Open();

OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);

OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

// Create data table

DataTable dTable = new DataTable();

dataAdapter.Fill(dTable);

the excel drivers used: http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

[BGM]

sorry to be a dick here.. but why are you using c# for this?

integration services man!!

hell, even DTS is better than c# for this ;)

[still1]

  On 22/07/2010 at 17:27, BGM said:

sorry to be a dick here.. but why are you using c# for this?

integration services man!!

hell, even DTS is better than c# for this ;)

Its not just reading the excel file to a DB. there is more to it and this is just the initial process.

The code i pasted here is just the piece where the failure occurs.

[nub]

Are they using office 2007?

[still1]

  On 22/07/2010 at 20:29, nub said:

Are they using office 2007?

I am using the office 2007 drivers to read the excel file

They get the file via email from a vendor and the file is not modified by the user. All they have to do is save the file to the path and the file watcher picks it automatically.

I used the same file to run the app and it works fine. it only errors out when the users do it.

[BGM]

  On 22/07/2010 at 20:23, still1 said:

Its not just reading the excel file to a DB. there is more to it and this is just the initial process.

The code i pasted here is just the piece where the failure occurs.

oh ok. could you elaborate a bit.. flesh out the situation a bit?

  On 22/07/2010 at 20:29, nub said:

Are they using office 2007?

oledb connection will only work with excel 2007 / 2010

  On 22/07/2010 at 20:49, still1 said:

I am using the office 2007 drivers to read the excel file

They get the file via email from a vendor and the file is not modified by the user. All they have to do is save the file to the path and the file watcher picks it automatically.

I used the same file to run the app and it works fine. it only errors out when the users do it.

i would be willing to to put money on this being a permissions issue..

but i'm at a loss as to what exactly.. :(

[still1]

  On 22/07/2010 at 20:50, BGM said:

oh ok. could you elaborate a bit.. flesh out the situation a bit?

Sure. we have a file watcher application(custom app) which can be configured to look at a particular shared path for a particular file(lets say F:\application\Input\ and file name like InputFile*.xls).

If the filewatcher sees a file created there it can be configured to run any exe for processing. so i wrote a console app to which will read the file created at the path and process the data.

The console app reads the file without any issues if i copy paste the file to that path(F:\application\Input\ and file name like InputFile*.xls).

but if user copy paste the file to the same path the console app fails with this error "External table is not in the expected format."

[BGM]

you are doubly sure it works with .xls files?

the OLEDB connector can only read .xlsx file (or so i thought!)

maybe that could be a reason, it's trying to connect to an .xls file using an incompatible connector and not finding the 'expected table structure'

hmm...

[still1]

  On 22/07/2010 at 21:04, BGM said:

you are doubly sure it works with .xls files?

the OLEDB connector can only read .xlsx file (or so i thought!)

maybe that could be a reason, it's trying to connect to an .xls file using an incompatible connector and not finding the 'expected table structure'

hmm...

I am 200% sure because i am placing the xls file for the users in that path and it works like charm.

it just dont work if they do it from their login.

whats weired is the application runs from a server and the login credential it runs as has complete access to the path.

[Soulsiphon]

  On 22/07/2010 at 21:08, still1 said:

I am 200% sure because i am placing the xls file for the users in that path and it works like charm.

it just dont work if they do it from their login.

whats weired is the application runs from a server and the login credential it runs as has complete access to the path.

I know I know, they *should* have perms but I have to tell you, as another poster said this really smells like a permission prob. Especially since the code absolutely works with the format - the same format those users are pasting/copying it to the working folder in. So a couple of possibilties, the users are somehow munging the xls format by who-knows what means? like editing an xls sheet with notepad and saving it - i dunno, lots of ways to munge that file, or perms which are causing the code to fail but the message bubbling up is a bad file format.

So, you really need to drop some breakpoints in your code where that file handling is occurring and make sure it's even getting a readable file for input from that user drop folder.

[BGM]

i have just tested the code and it works fine with xlsx or xls so thats cool.. red herring there :)