The CSV file format in Effort

This post describes the file format that is compatible with the CsvDataLoader component of the Effort library.

The component accepts files that follow the traditional CSV format:

  • The first row contains the header names
  • Comma ( , ) is the separator character
  • Double quote ( ” ) is the delimiter character
  • Double double quote ( “” ) is used to express a single double quote between delimiters

There are some additional requirements that need to be taken into consideration.

  • Numbers and dates are parsed with invariant culture setting
  • Binaries are encoded in base64 format
  • Null values are represented with empty fields without delimiters
  • Empty strings are represented with empty fields with delimiter
  • Backslash serves as escape character for backslash and newline characters

These are all the rules that are need to be followed. The next example demonstrates the rules by representing a compatible CSV file.

id,name,birthdate,reportto,storages,photo
"JD","John Doe",01/23/1982,"MHS","\\\\server1\\share8\r\n\\\\server2\share3",
"MHS","Michael ""h4x0r"" Smith",05/12/1975,,"","ZzVlKyszZjQ5M2YzNA=="

The content of each database table is represented by a dedicated csv file that has to be named as {table name}.csv.

Advertisements

8 Responses to The CSV file format in Effort

  1. tpancoast says:

    Does schema_name factor into the table name for the CSV file? I have a table named Prospect.ReferralType. I used the Effort.CsvTool to create the file, and it creates ReferralType.csv. When I hit the code “context.ReferralTypes.ToList();”, I get a “Object reference not set to an instance of an object.” exception at “Effort.Internal.DbManagement.DbExtensions.GetTable(Database database, String name)”.

    • tflamich says:

      The schema name should not be taken into account. I will add logging capability to the data loaders to find out easier the cause of these kind of problems.

  2. tpancoast says:

    FYI, I downloaded the Effort source and did a little digging. My context was calling “Database.SetInitializer(null)” in a static constructor. (Technically, this is code from the “EntityFramework Reverse POCO Generator”, but my manually created contexts do the same thing.) Anyhow, it appears that is what was causing the error. Your “public Database Internal” property in DbContainer.cs is never initialized. For some reason your ExceptionMessages.DatabaseNotInitialized exception message isn’t making it all the way out, but I have found my problem and know what to do.

    • tflamich says:

      Good to hear you managed to solve the issue on your own. I did some improvements that might be related to this problem. They will be included in the next release.

  3. Tim Schmidt says:

    I need to read in a tab-delimited file and I’m not seeing a terribly easy way to override the delimiter. It’s configured on CsvReader which is created by CsvTableDataLoader, which is created by CsvTableDataLoaderFactory, which is created by CsvDataLoader. Is there an easier way that I’m missing?

  4. sam says:

    I cant seem to get transactions working correctly with Effort. No matter which technique I try the transaction doesnt seem to get rolled back correctly or at all.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: