Click here to Skip to main content
Email Password   helpLost your password?

FileHelpers Home Page (Project Summary)

FileHelpers v 2.0

Browse the Forums Development Blog Support the project

Introduction

The FileHelpers are an easy to use library to import/export data from fixed length or delimited flat files (like the CSV). FileHelpers also has support to import/export data from different data storages (Excel, Acces, SqlServer).

The library has a set of converters for the basic types and can be easily extended to provide custom converters.

The main idea is pretty simple:

You can strong type your flat file (fixed or delimited) simply describing a class that maps to each record of the file and work with your file like a strong typed .NET array.

Basic Uses

Directly between the files and the .NET source code:

Use 1

It also can be used as an intermediate between files and MS Access or MS SqlServer tables:

Use 2

Quick Start Guide (easy steps)

To start using the FileHelpers Library you only need to add a reference in your project to the file: FileHelpers.dll. You can find it in the Release directory of the distribution. Tip: remember to leave the FileHelpers.xml file to get Intellisense support.

Next you need to define a class that maps to the record in the source/detination file. For this example we use a file with this format delimited by a |:

10248|VINET|04071996|32.38
10249|TOMSP|05071996|11.61
10250|HANAR|08071996|65.83
10251|VICTE|08071996|41.34
...............

The class that we refer can be like this:

[DelimitedRecord("|")]
public class Orders
{
   public int OrderID;

   public string CustomerID;

   [FieldConverter(ConverterKind.Date, "ddMMyyyy")]
   public DateTime OrderDate;

   public decimal Freight;
}

Later you must instantiate a FileHelperEngine and Read/Write files:

FileHelperEngine engine = new FileHelperEngine(typeof(Orders));

/// to Read use:

Orders[] res = (Orders[]) engine.ReadFile("input.txt");

/// to Write use:

engine.WriteFile("output.txt", res);

Finally you can use the res array to access each item in the file, for example:

 foreach (Orders order in res)
 {
    Console.WriteLine("Order Info:");
    Console.WriteLine(order.CustomerID + " - " +
                      order.OrderDate.ToString("dd/MM/yy"));
 }

Working with FixedLength files is exactly the same but you need to use the [FixedLengthRecord] and [FieldFixedLength] attributes.

Record Class Wizard

Since version 1.3.5 the library has had a Record Class Wizard to generate the record class. The Record Class Wizard allows you to generate your record mapping class, save the definition, generate code based on snippets (Read File, Read with Generics, ReadAsync and so on).

The wizard uses the excellent FireBall CodeEditor


Click to see how it works

Who needs the File Helpers Library?

In almost every project there is a need to read/write data from/to a flat file of a specified format. Writing the code to process these files is not hard, you can use String.Split or String.SubString, but if you do that the code sometimes becomes hard to read and change. If you want to add support later for quoted string, multiline, convert types, etc. these things tend to get complicated.

So with the FileHelpers you don't need to worry about changes in the file structure because you can change the Type, Converters, Triming, Length in seconds.

Another place where you can find the FileHelpers useful is for log parsing, data warehouse and OLAP applications, communication between systems, file format transformations (for example from a fixed length to an CSV file), load test data into your NUnit tests and a lot more!!!

This library aims to provide an easy and reliable way to accomplish all these tasks.

Main Features

Easy to use: The FileHelpers Lib is straight forward to learn and use. (see EasyExample)

Auto Converters: The library has a set of converters for the basic types and can be easy extended to provide custom converters. (see ConverterBase and Converter Example)

RunTime Classes since version 1.6.0 you have been able to create your record class at run time, and load them from files with source code or an XML description (check the example)

Master-Detail: You can read and write records with a master/detail pattern. (see Example1 and Example2)

Multiple record format support: With the MultirecordEngine you can read files with different record layout, you can also read files with some delimited and some fixed length records. (see Example)

Event Support: The engines of the library contain some events to allow you to easily extend the behavior of the library (see Example)

MS Excel Storage: Are a way to extract / insert records between any source and an excel file. (see ExcelDataStorage and ExcelDataLinkExample)

DataLinks: Are a way to extract / insert records between a database and a file. (see DataStorage and FileDataLinkExample)

GenericDataLink: Now you can to copy records between two Data Storages (see GenericDataLink and DataStorage)

Asynchronous Mode: You can use the library to read line by line and not the whole file. (see Async Methods)

.NET Compact Framework Support: From the version 1.1 you can use the FileHelpers library for you PocketPC and WindowsCE developments. (thanks Pierre)

File Transform Engine: To convert files in one format to another (for example a file with CSV to a FixedLength record format) (check the example)

Progress Notification: To get notified of the progress in each operation in the library (check the example)

.NET 2.0 Generics: the cast less and strong typed version of the engines (check the example)

FileDiffEngine to allow compare files with the same record layout (check the example)

Others Features

Files, Stream and String Support: You can use the library to read/write any stream or string, not only files. (see FileHelperEngine)

Different Error Behaviors: You can set the behavior of the library when an error is found (throw and exception, ignore, save and continue, etc) (see the examples)

Quoted String Support: It allows to indicates that the field must be read and written as Quoted String, like Excel CSV. (see FieldQuotedAttribute )

CommonEngine a easy way to access to common operations (check the example)

Encoding Aware: You can define the encoding used to read and write files or streams. (see BaseEngine.Encoding)

NullValues: The library identifies the null values in the files and assigns an adequate value. (see FieldNullValueAttribute)

Good Documentation: The library is fully documented (at least that's the intention) with a lot of Examples of Use.

Align and Trimming: You can set the processing mode of the in/out strings with a lot of attributes like the FieldAlignAttribute and the FieldTrimAttribute.

Progress Notification

Here is a screenshot of the progress notification of the demo app.

Since versi�n 1.4.0 you can get notified of the progress of each operation of the library to show feedback to the user or whatever you want.

Class Diagram

Detailed Diagram

Detailed Diagram

Testing

The library contains more than 250 NUnit tests to ensure correctness:

Sample image

History

Version 2.0 (April-2007)

The library has passed his 1st birthday!!! Thank you all for your work and contributions (partial list of them)

A lot of things were changed (mostly internally), now we have a performance gain of more than 60% for .NET 2.0

I keep releasing the .NET 1.1 version because I know that a lot of people use it in some corportaions, but it could be that new features in the versions to come will be .NET 2.0 only. It is monumental work to maintain and optimize both versions.

There are also a lot of major changes, a lot of refactoring in the code, and a big core rewrite that allows the engines to use less memory and temporary strings (we are using more buffers to aviod this)

I keep working hard to keep the library updated and give support to the users. You can check for examples in the Forums which shows a lot of problems that have been solved. I also ask for a lot of mail with problems from the users.

We have our own domain www.filehelpers.com thanks to (Antoine) and a Development Blog

Breaking Changes

Performance Related

Shining new features

API Changes and Extensions

Small Changes

Browse the complete history at SourceForge.

RoadMap (the future of the lib)

Check it out at SourceForge FileHelpers Roadmap.

Contact and Ideas

The main site of contact of the library are the FileHelpers Forums

Quick Feedback FileHelpers Forums

If you find that there is a feature that I must include, or you have a new idea (for the API, Source Code or Examples), only let me know, entering the forums or sending an e-mail to marcos[at]filehelpers.com

Licence (LGPL)

FileHelpers Library is @Copyright 2005-2006 to Marcos Meli but it's source code and the binaries are free for commercial and non commercial use.

Vote for this article if you like the library.

You must Sign In to use this message board.
 
 
Per page   
 FirstPrevNext
GeneralMy vote of 5
jarimba
0:35 5 Feb '10  
Excellent!

Best regards,
Rui Jarimba
GeneralGreat!
Polymorpher
14:36 20 Jan '10  
Thanks a lot, this is a great tool, and very easy to use!

--
"Keyboard not found. Press < F1 > to RESUME. "
Source unknown (appears in many common BIOSes as a real error message)

GeneralReading the results of a Microoft Access Compact & Repair
mtohamy
7:46 15 Jul '09  
We managed to perform Compact & Repair form VB cosde. I would like to know how to read the results of the completed compact and repair. Wher does Access erites it (if any)? How can i access it and read it to interpret the results and take actions baed on these results?

While on the subject, How can I decide whether an Access file is corrupted or contains tables with corrupted records?

Thanks in advance.
QuestionWhy dpn't you use OLEDB for the Excel files?
Robson Félix
17:33 15 Dec '08  
Dude,

Question for ya: why don't you use Jet/OleDB to insert/read data from Excel files? This way we:

a) don't need to have Excel installed on the machines running the code
b) have this code running much faster

Let me know what you think.

Best,
Robson
GeneralCSV line terminators?
drwbns
10:00 10 Dec '08  
Is there a way to specify a line terminator after the last record in myrecordclass?
GeneralParsing Flat file without Newline to separate the records
Rezaul
11:46 17 Sep '08  
Hi, FileHelpers is really an excellent tool. Great job!
While testing I have felt that following feature would make it more versatile:
1. Currenly FileHelpers separate records by NewLine. So it uses ReadLine() to read lines from Stream.
2. It would be really excellent if you could add the provision to read fixed and variable length records that are not separated by Newline.
a. Fixed Length:
i. All records are of same size. We define the size of the Record and specify size of each Field.
Example Attribute:
[RecordSize=504]


ii. Records are of variable size. Certain field in the record specify the type of the Record which dictates the size of the record.
Example Attribute:

[Recordsize=variable]
[When RecordType='{ID}=="00"']
List of fields and size
Define Filed ID
List of fields and size
[When RecordType='{ID}=="01"]
List of fields and size
Define Filed ID
List of fields and size


Ok, There goes my wish list.


----------
Rezaul Kabir
GeneralCould I add header and trailer to the CSV?
_Thurein_
0:07 6 Aug '08  
Could I add header and trailer to the CSV?
Thanks a lot ....
GeneralIs it possible to write a csv file with variable structue.
MAP Tiger
13:22 24 Jul '08  
Hi

Well, library looks great. I have a little question that if I want to open a csv file whose structure is not known to me but it has the header rows and is tab separated. I want to load it into a dataset and later to bindingsource.

Basic question is that is it possible to open a file without knowing the structue?

Thanks in advance

MAP Tiger
Tiger Softwares

Software Designer and Developer
VB.NET, ASP.NET, VFP

General[Message Removed]
Kartal
3:09 31 May '08  
Spam message removed
GeneralExport to excel from SQL 2005
jimmag
9:33 29 May '08  
Can this library handle exporting tables from a SQL server 2005 database into a Excel spreadsheet?


Thanks for the help and for contributing a great product.
GeneralRe: Export to excel from SQL 2005
gg4237
0:44 13 Nov '09  
Hi!

You that task you could try GemBox.Spreadsheet .NET Excel component.

First export tables from SQL server 2005 database to DataSet and then export DataSet to Excel file.
GeneralDynamic Generation question
Member 495468
18:41 19 May '08  
Marco, this is an amazing library. Thanks very much for this; I only hope to be able to one day provide something as valuable as this as well.
I have a question. I'm building an application that allows the user to define their own "lists." The user specifies the columns, datatypes, etc... I want to give them the ability to export data from one of their custom list using your library, but this means that I need to be able to generate a mapping class "on the fly" based on the column layout they have defined. Is there an automated way of doing this? Or is the Wizard the only way to generate a mapping library? Please let me know if my question doesn't make sense.

Many Thanks,

Steve
GeneralRe: Dynamic Generation question
Marcos Meli
6:15 20 May '08  
Hi Steve

Thanks a lot for your kind of words =)

You can create dynamic classes for the engines using runtime records

Here do u have the docs:

Run Time Records[^]

Let me known if that help in ur problem.

Cheers and thanks again for the motivation


GeneralRe: Dynamic Generation question
Member 495468
6:14 2 Jun '08  
Again...amazing.
That is perfect.

Thanks very much.

Steve
Generalcsv to msaccess
scalpa98
11:37 31 Oct '07  
hello
i try to import csv data into a table of a database without success.
here is the format of the csv file :
IdEleve;Nom;Prenom;Niveau_Classe;Sexe;Ddn
1;ANNE;Tanguy;CE2;G;25/05/1998
2;BAISNÉE;Laurine;CM2;F;08/07/1997
3;BAISNÉE;Leslie;CM2;F;08/07/1997
I tried to follow the example, but my knowledge is not enough to understand all the steps. Here is the code i put in a class , but i get few errors i couldn't fix.
Imports FileHelpers
Imports FileHelpers.DataLink.AccessStorage

Public Class Class1
      <DelimitedRecord(";")> _
      Public Class Orders
            Public OrderID As Integer
            Public Nom As String
            Public Prenom As String
            Public Niveau_Classe As String
            Public Sexe As String
            <FieldConverter(ConverterKind.[Date], "ddMMyyyy")> _
            Public Ddn As DateTime

      End Class
      Dim storage As New DataLink.AccessStorage(GetType(Orders), "TestData.mdb")

storage.SelectSql = "SELECT * FROM Orders"

storage.FillRecordCallback = New FillRecordHandler(FillRecordOrders)

FileDataLink.EasyExtractToFile(storage, "out.txt")

      Protected Sub FillRecordOrders(ByVal rec As Object, ByVal fields As Object())
            Dim record As Orders = DirectCast(rec, Orders)

            record.OrderID = CInt(fields(0))
            record.Nom = DirectCast(fields(1), String)
            record.Prenom = DirectCast(fields(2), String)
            record.Niveau_Classe = DirectCast(fields(3), String)
            record.Sexe = DirectCast(fields(4), String)
            record.Ddn = DirectCast(fields(5), DateTime)

      End Sub
End Class

In fact i don't know how to use this, i am a real beginner.... But i feel, it can make the job.
I hope you can help me
Thanks
pascal


scalpa
http://www.scalpa.info
GeneralBug Report... or is it a feature?
martin_hughes
1:37 5 Oct '07  
I'm dealing with some very large fixed width files (> 100 fields per record) - when I try to add more than 100 fields in the Wizard I get the following exception:

See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
FileHelpers.FileHelpersException: The string '' not is a valid .NET identifier.
at FileHelpers.RunTime.FieldBuilder..ctor(String fieldName, String fieldType)
at FileHelpers.RunTime.FixedLengthClassBuilder.AddField(String fieldName, Int32 length, String fieldType)
at FileHelpers.WizardApp.frmWizard.CreateFieldControl()
at FileHelpers.WizardApp.frmWizard.cmdAddField_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
FileHelpersWizard
Assembly Version: 1.5.0.0
Win32 Version: 1.5.0.0
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/FileHelpersWizard.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
FileHelpers
Assembly Version: 2.0.0.0
Win32 Version: 2.0.0.0
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/FileHelpers.DLL
----------------------------------------
Fireball.CodeEditor
Assembly Version: 1.0.0.1
Win32 Version: 1.0.0.1
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/Fireball.CodeEditor.DLL
----------------------------------------
Fireball.Windows.Forms
Assembly Version: 1.9.0.0
Win32 Version: 1.9.0.0
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/Fireball.Windows.Forms.DLL
----------------------------------------
Fireball.SyntaxDocument
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/Fireball.SyntaxDocument.DLL
----------------------------------------
Fireball.Win32
Assembly Version: 1.0.0.1
Win32 Version: 1.0.0.1
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/Fireball.Win32.DLL
----------------------------------------
Fireball.Core
Assembly Version: 1.1.0.2
Win32 Version: 1.1.0.2
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/Fireball.Core.DLL
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Web
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Web/2.0.0.0__b03f5f7f11d50a3a/System.Web.dll
----------------------------------------
Fireball.CodeEditor.SyntaxFiles
Assembly Version: 1.0.0.2
Win32 Version: 1.0.0.2
CodeBase: file:///C:/Documents%20and%20Settings/Martin%20Hughes/Desktop/FileHelpers_2_0_0_bin_docs_wizard/FileHelpers/Wizard/Fireball.CodeEditor.SyntaxFiles.DLL
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.832 (QFE.050727-8300)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------



Me: Can you see the "up" arrow?
User:Errr...ummm....no.
Me: Can you see an arrow that points upwards?
User: Oh yes, I see it now!

-Excerpt from a support call taken by me, 08/31/2007

GeneralRe: Maybe a Bug Report
Marcos Meli
3:36 5 Oct '07  
Hi There

Thanks for your feedback

This sure is a problem with the wizard, I never checked it with so many fields, I will check the problem and post here the solution.

Could you send me a sanmple file with all that fields, so I can add an NUnit test to ensure that this never happend again ?

Best Regards


GeneralRe: Maybe a Bug Report
martin_hughes
3:44 5 Oct '07  
Hi Marcos,

Email sent.

Cheers,

Martin.


Me: Can you see the "up" arrow?
User:Errr...ummm....no.
Me: Can you see an arrow that points upwards?
User: Oh yes, I see it now!

-Excerpt from a support call taken by me, 08/31/2007

GeneralRe: Only the NumericUpDown Maximum
Marcos Meli
19:18 7 Oct '07  
Hi there

The problem was the default Maximum of the NumericUpDown Laugh

I just sent to you the last build of the library and commit the changes

Thanks for the report Smile

Marcos





GeneralHow to create excel spreadsheet
StevieGen
11:31 29 Aug '07  
I have this small sample:

ExcelStorage provider = new ExcelStorage(typeof(Person));
Person p = new Person();
p.FirstName = "Steve";


provider.FileName = "Test.xls";

provider.InsertRecords(new Person[] { p });


There is no WriteToFile equivalent for ExcelStorage ?

I need to both create and read an excel spreadsheet
GeneralRe: How to create excel spreadsheet
blogging developer
23:43 31 Aug '07  
Nice post.
Thanks.

Cheers,
Blogging Developer
http://www.bloggingdeveloper/com

GeneralReturn List for generic readers
Marc Scheuner
23:37 28 Aug '07  
Hi Marcos,

Great stuff you have here! I was just wondering why you return a static array of e.g. "Customer" (Customer[]) from the generic engine's .ReadFile method, instead of a List ?

I think using the generic lists would be a lot easier than a clunky array...

Cheers!
Marc

=============================
Marc Scheuner, Berne, Switzerland
mscheuner - at - gmail.com
May The Source Be With You!

GeneralReading MS CSV files
udikantz
18:33 8 Aug '07  
hello could anyone please help me figure out how i can make this wonderful
library read the MS Excel CSV files , thank you all for your time , im a newbie to c# and i couldnt understand for the samples how to do it , help me make it work and i'll donate 20$ Poke tongue thanks
i got a file that looks like this:



Site Name Title Site Auction ID Quantity Order SubTotal Buyer E-mail Address Seller Left Feedback SKU Winning Bidder Unit Price Invoice Quantity Invoice Date Class Order ID Bidder Status E-mail Status Payment Status Shipping Status Transaction Status Checkout Status Payment Type Shipping Addr 1 Shipping Addr 2 Shipping City Shipping Region Shipping Postal Code Shipping Country Shipping Carrier Code Shipping Carrier Shipping Class Code Shipping Class Second Chance Offer Promotion Code Buyer First Name Buyer Last Name Buyer Company Buyer Day Phone Buyer Evening Phone Estimated Ship Date Deliver By Date External Payment Transaction ID Seller Cost Flag Description Order Total Promotion Value Tax Total Tracking Number Shipping Cost Transaction Notes Shipping Date Warehouse Location
"some secret data" "some more secret data" "3242346543634" 1 9.89 "sensord data that is" False "3243" "secretgirl" 9.89 1 8/8/2007 5:35:10 PM "someitem" "234233" "some secret e-mail thing" "Payment cleared" "Items shipped" "Transaction valid" "Checkout completed" "PayPal" "secret again" "secret adress" "secret place" "LOL" "1232" "BB" "Secret POST" "SECRET POST" "Air Mail" "Air Mail" False "Claire" "Amos" "LONGNUMBER123212" 3.10 18.89 0.00 0.00 7.00 8/8/2007 6:11:19 PM "MEH4"


Net

GeneralRe: Reading MS CSV files
Marcos Meli
4:34 9 Aug '07  
Hi udikantz

I just post an answer to your question in the FileHelpers forums you need to follow these instructions to parse the CSV files, I will be adding it to the docs in the next version =)

http://www.filehelpers.com/forums/viewtopic.php?p=1224#1224[^]

Let me know if that solve your problem, you can answer here or in the forums.

Cheers

A copy of the Answer:

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:



We first create this delimited class:



[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



[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


FileHelperEngine engine = new FileHelperEngine(typeof(CustomersExcelCSV));

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



Or in .NET 2.0



FileHelperEngine engine = new FileHelperEngine();

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



GeneralRe: Reading MS CSV files
udikantz
9:37 9 Aug '07  
hey marcos , thanks for the fast replay ,
well , it seems like my excel csv files are delimited by a tab and not by a "|" ....
and when i tried to use the [FieldOptional] attribute i would get

BadUsageException:
Null Value found for the field 'flag_Description' in the class 'CA_Sales'. You must specify a FieldNullValueAttribute because this is a ValueType and can´t be null.

i have tried to change the [fieldoptional] attribute to the [FieldNullValueAttribute] , and it also didnt work , also i didnt know what kind of object to put for [FieldNullValueAttribute(Object)] , so i tried some diffrent types of object but it turned to be a failure Big Grin

if you want i can send you a sample of the CSV im working with , or whatever you want that can assist for a solution , thank you very much for your time , you guys are the best.

Net


Last Updated 10 Jul 2007 | Advertise | Privacy | Terms of Use | Copyright © CodeProject, 1999-2010