2

I'm using LinqToExcel to retrieve some information and I can get the data from all my columns except from one. This is the Scenario:

I have 3 columns:

Code   Name   LastName
 89    test1   test2
 89-2  test3   test4
 90    test5   test6

and to get the list of values I use:

var excel = new LinqToExcel.ExcelQueryFactory(path)
var excelValues = excel.Worksheet<Sheet>("Sheet1").AsEnumerable().Where(p => !String.IsNullOrWhiteSpace(p.Code));

This returns the following information:

89  |test1|test2
null|test3|test4 //here is my problem
90  |test5|test6

If I change the 89-2 to be 89.2 then it works. Is there any way I can escape the "-" character? or what I'm doing wrong.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Goca
  • 1,743
  • 2
  • 15
  • 36
  • Unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on [so]. See "[Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?](http://meta.stackexchange.com/questions/2950/should-hi-thanks-taglines-and-salutations-be-removed-from-posts). – John Saunders Jan 15 '15 at 19:40
  • In your Excel document, try changing the formatting on that column to text. – Asad Saeeduddin Jan 15 '15 at 19:57
  • It didn't work and if that was the case why it will work with any other letter or number? it just doesn't like the "-" symbol. – Goca Jan 15 '15 at 21:01
  • It was not an issue with the "-" character, the issue was that based on the first element on the column it was inferring the type and because the first element was a number it was thinking that everything was a number. @Haroon answer solve the problem – Goca Jan 15 '15 at 22:02
  • Note: Making the column to be text format doesn't solve the problem. – Goca Jan 15 '15 at 22:05

2 Answers2

3

LinqToExcel uses the 2nd row to determine datatype, 89 is being considered a number and all subsequent data is considered to be a number. Add a single quote (') prior to the number, e.g. '89 to force excel to set the contents as text.

The first row is used to map column names to class properties

Haroon
  • 1,052
  • 13
  • 28
  • Genius!! this was driving me crazy, I added the single quote to the first element of the Code column and everything is working. – Goca Jan 15 '15 at 22:00
0

include this line after the of object excel "excel.DatabaseEngine = DatabaseEngine.Ace;"

var excel = new LinqToExcel.ExcelQueryFactory(path);
excel.DatabaseEngine = DatabaseEngine.Ace; 

var excelValues = excel.Worksheet<Sheet>("Sheet1").AsEnumerable().Where(p =>
!String.IsNullOrWhiteSpace(p.Code));
Alfredo Correa
  • 1