XLS Formatting

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hello,

We're having a problem when reading in an XLS file. If the column is
formatted as a % and/or uses ( ) for negatives, these values are coming in
as strings with the formatting.

Is there an option for the connection string telling it to read the raw
values and not the formatted value?

Thanks,
Joe
 
Hello Joe

What's the provider you are using to read the XLS file? ODBC or OLEDB?
http://www.connectionstrings.com/?carrier=excel

I cannot reproduce the symptom with OLEDB provider. Would you please check
whether you have the property IMEX set to 1 in the connection string? If
IMEX = 1, the driver will read "intermixed" (numbers, dates, strings etc)
data columns as text and may possibly cause the problem you are faced with.
Removing IMEX=1 can fix the issue. If it's not helpful to you, do you mind
sending a small reproducible project to my mailbox? I will test to see what
makes it different. My mailbox is: (e-mail address removed)

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Hello,

I tried setting IMEX = 0 but then we loose the 1st row which contains the
field names. We tried setting HDR=YES but this doesn't seem to make a
difference.

We came up with a workaround for the ( ) and % but now the problem we have
are #VALUE! errors or other bad data in a row which would normally be a
numeric field but due to formatting we end up with some weird values.

Any other ideas?

Thanks,
Joe
 
Hello Joe,

I still cannot reproduce the symptom on my side. At the bottom of this
message, you can find my test steps. Joe, do you mind sending a
reproducible project to me?

By the way, you mentioned the #VALUE! Errors. Are there UDFs in your xls?

===== MY TEST STEPS =====
1. Create a XLS in Excel 2003. In Sheet1, I format ColumnA as () and
ColumnB as %

2. Input values to the cells as:
A1 = ID
A2 = -123
A3 = 123
B1 = Name
B2 = 1
B3 = 2

3. Create a C# console project in Visual Studio with the code:

static void Main(string[] args)
{
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\\Book1.xls;Extended Properties=\"Excel 8.0;HDR=Yes; \"";
OleDbConnection conn = new OleDbConnection(connStr);
conn.Open();

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT ID, NAME FROM [Sheet1$]";
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Debug.Print(reader[0].ToString());
Debug.Print(reader[1].ToString());
}

reader.Close();
conn.Close()
}

4. Debug the project, and see the debug info in the Output window:

-123
1
123
2

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Hello Joe

I do not hear from you after my last post. If you need further assistance,
please let me know. If the xls content is confidential, may I suggest that
you remove the confidential info and send the xls to me for reproducing the
problem?

Have a great day!

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
 
Back
Top