Hi there all, after some time we are hard working in v3.0:
You can download the latest stable build here

If you want to check the current activity: Latest Commits

How to read a CSV file generated with MS Excel

FileHelpers FAQ

Site Admin
Posts: 417
Joined: 07 Mar 2006, 15:48
Location: Bahía Blanca - Argentina
PostPosted: 09 Aug 2007, 14:27
How to read a CSV file generated with MS Excel

There are two main problems handling this kind of files.

    1. The quoted fields: Excel can add " (quotes) at the fields at random basis not only at the fields that contains the delimieter.
    2. The missing fields: Excel after certain number of rows can not generate enough delimiters (comma or tab) if the last fields are empty.


To solve these problems we must use the FieldQuoted and FieldOptional attributes.

For example for a Excel File like this one:

Image

We first create this delimited class:


Code: Select all
[DelimitedRecord("|")]
public class CustomersExcelCSV
{
   public string CustomerID;

   public string CompanyName;

   public string ContactName;

   public string ContactTitle;

   public string Address;

   public string City;

   public string Country;
}


Later we need to add the [FieldQuoted] to all fields and the [FieldOptional] from the second field to the last

Code: Select all
[DelimitedRecord("|")]
public class CustomersExcelCSV
{
  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
   public string CustomerID;

  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  [FieldOptional]
   public string CompanyName;

  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  [FieldOptional]
   public string ContactName;

  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  [FieldOptional]
   public string ContactTitle;

  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  [FieldOptional]
   public string Address;

  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  [FieldOptional]
   public string City;

  [FieldQuoted('"', QuoteMode.OptionalForBoth)]
  [FieldOptional]
   public string Country;
}


So now to read the Excel CSV Files

Code: Select all
FileHelperEngine engine = new FileHelperEngine(typeof(CustomersExcelCSV));

// To Read Use:
CustomersExcelCSV[] res = engine.ReadFile("Customers.csv") as CustomersExcelCSV[];


Or in .NET 2.0

Code: Select all
FileHelperEngine<CustomersExcelCSV> engine = new FileHelperEngine<CustomersExcelCSV>();

// To Read Use:
CustomersExcelCSV[] res = engine.ReadFile("Customers.csv");
Happy Coding :wink:
Marcos ( http://www.devoo.net )
If you like the library support us with some books
Vote FileHelpers in Codeproject or Bookmark us in Delicious or here, Thanks !

Posts: 2
Joined: 22 May 2008, 00:07
PostPosted: 22 May 2008, 00:12
Hi there,

i encounter a weird issue using the optional field. For some reason, "true" is trimmed down to "tru"???

Here is my row definition class file:
-----------------------------
Imports FileHelpers

<DelimitedRecord(",")> _
Public Class TestClass
<FieldQuoted(QuoteMode.OptionalForBoth), FieldOptional()> _
Public s1 As String

<FieldQuoted(QuoteMode.OptionalForBoth), FieldOptional()> _
Public s2 As String
End Class
-----------------------------


Here is my data file:
-----------------------------
true,2
false,
,1
true,
true,
-----------------------------


Here are the output resulting from the import:
-----------------------------
s1: true
s2: 2

s1: false
s2:

s1:
s2: 1

s1: true
s2:

s1: tru
s2:
-----------------------------

Do you know what this issue is about?

Thanks,
Oliver Wong

Site Admin
Posts: 417
Joined: 07 Mar 2006, 15:48
Location: Bahía Blanca - Argentina
PostPosted: 22 May 2008, 02:54
Hi there

Yes that is a issue with optional fields that is solved in the last version

You can download it here:

http://www.devoo.net/FileHelpers_2.2_RC1.rar

Best Regards
Marcos
Happy Coding :wink:
Marcos ( http://www.devoo.net )
If you like the library support us with some books
Vote FileHelpers in Codeproject or Bookmark us in Delicious or here, Thanks !

Posts: 2
Joined: 22 May 2008, 00:07
PostPosted: 22 May 2008, 03:58
Thanks for acknowledging Marcos! :D

I didn't use the latest version FileHelpers_2.2_RC1 'cos i thought it might be unstable due to the fact that it's not "official" yet!

I will try the latest RC version then.

Thanks again mate!!!

Regards,

Oliver Wong
http://www.pcsmart.co.nz

Return to FAQ

cron