ToString().Trim() issue with Excel data

[SusanHAllen]

My code reads an Excel file and outputs XML. For one value I need to evaluate 2 spreadsheet values to set the XML as in (dr being a datarow):

if (String.IsNullOrEmpty(dr[5].ToString().Trim()) == true) 
                       strPrimeTaxid = dr[6].ToString().Trim();
                   else
                       strPrimeTaxid = dr[5].ToString().Trim();

This works fine for data such as "123456" but not for "56-12345". If I remove the hyphen, everything's fine. The spreadsheet column is unformatted (ie General datatype), and the Excel connection string includes"IMAX=1", which should evaluate all Excel data as string (Text). BTW, the hyphenated value is in dr[5].

Please help, I'm stumped and the file is due to the client tomorrow.

Thanks!

[SusanHAllen]

Guess what, if I remove Trim() from the if condition, it works. So I solved my immediate problem, but as a c# newbie, can anyone tell me why???

My code reads an Excel file and outputs XML. For one value I need to evaluate 2 spreadsheet values to set the XML as in (dr being a datarow):

if (String.IsNullOrEmpty(dr[5].ToString().Trim()) == true) 
                       strPrimeTaxid = dr[6].ToString().Trim();
                   else
                       strPrimeTaxid = dr[5].ToString().Trim();

This works fine for data such as "123456" but not for "56-12345". If I remove the hyphen, everything's fine. The spreadsheet column is unformatted (ie General datatype), and the Excel connection string includes"IMAX=1", which should evaluate all Excel data as string (Text). BTW, the hyphenated value is in dr[5].

Please help, I'm stumped and the file is due to the client tomorrow.

Thanks!

[Ramy Mahrous]

You didn't solve your problem by removing trim from if, to format it you should String.IsNullOrEmpty(dr[5].ToString().Trim(new char[]{'-'}/*and your special characters */)

[SusanHAllen]

Thanks, Ramy; I want to retain the hyphen, will your code remove it?

You didn't solve your problem by removing trim from if, to format it you should String.IsNullOrEmpty(dr[5].ToString().Trim(new char[]{'-'}/*and your special characters */)

[sknake]

Yes, it will. You can add it back between elements of the array though. string s = string.Format("{0}-{1}", array[0], array[1]); or something like

private void simpleButton1_Click(object sender, EventArgs e)
    {
      string[] array = new string[0];
      string result = string.Empty;
      foreach (string s in array)
      {
        result += (string.IsNullOrEmpty(result) ? string.Empty : "-") + s;
      }
      System.Diagnostics.Debugger.Break();
    }

[Ramy Mahrous]

Look, I don't know why you used Trim with String.IsNullOrEmpty. But yes my code will remove it. I expected you need to remove it because the string not formatted.

[SusanHAllen]

oy vey. So I have to remove the hyphen in order to make it a string, the put the string back in to write the value?

Yes, it will. You can add it back between elements of the array though. string s = string.Format("{0}-{1}", array[0], array[1]); or something like

private void simpleButton1_Click(object sender, EventArgs e)
    {
      string[] array = new string[0];
      string result = string.Empty;
      foreach (string s in array)
      {
        result += (string.IsNullOrEmpty(result) ? string.Empty : "-") + s;
      }
      System.Diagnostics.Debugger.Break();
    }

[Ramy Mahrous]

I have to remove the hyphen in order to make it a string

Everything is string, with\out hyphen it's string but if you don't need the hyphen, you can replace it with null str = str.Replace("-",null); or if you've set of characters you need to remove

char[] UnneededCharacters = new char[] {'-','*','@','&'}; 
string str = "123-456*789@abc&def";
str = str.Trim(UnneededCharacters);
//str = 123456789abcdef

But, there no need to your string.IsNullOrEmpty(...) EXCEPT you may have returned string like that "-"

[SusanHAllen]

All the process is trying to do is read in a value as a trimmed string from Excel and output to XML, no matter what the data is.

If you're interested, the solution has to do with how Jet interprets mixed-type data within an Excel column. Solution is to set Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows to 0 and
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes to Text. Then you put IMEX=1 in the connection string, which forces Jet to abide by those settings.

In other words, there is no need to remove the hyphen to make the value a string, then reinsert it.

Look, I don't know why you used Trim with String.IsNullOrEmpty. But yes my code will remove it. I expected you need to remove it because the string not formatted.

[Ramy Mahrous]

Thank you very much, Susan, for this valuable information :)