C# Importing CSV using OleDb

Question

I'm trying to set up code to import .CSV files into .NET.

I've tried both Microsoft.Jet.OLEDB.4.0 and Microsoft.ACE.OLEDB.12.0 providers, including modifying the Extended Properties and even modifying corresponding registry keys for each. I have yet to come up with a solution for what I am attempting to do:

I would like to import each field as text, but leave fields longer than 255 characters un-truncated.

What I've found so far is that I can have one or the other, but not both.

If I set the ImportMixedTypes registry value to Majority Type, it leaves 255+ character text fields un-truncated, but converts other fields to unwanted types.
If I set the ImportMixedTypes registry value to Text, it truncates 255+ character text fields, but leaves the other field types as text.

How do I accomplish this using OleDb?

Additional info:

I have a "notes" column, which can contain very lengthy text. I also have a "zip code" column, which contains mixed zip-code formats (5-digit and 9-digit with a dash). Typically, the 5-digit zip-code format is more popular, so the importer thinks that the column should be integer type, leaving the 9-digit zip-codes as null values after import.

Alex · Accepted Answer · 2013-08-23 23:34:33Z

1

Have you considered using something as versatile as the FileHelpers library (http://filehelpers.sourceforge.net/) instead?

Or alternatively if your requirements are no more than you state (read csv file, get string fields), use something really simple such as:

public static class SimpleCsvImport
{
    public static IEnumerable<List<string>> Import(string csvFileName)
    {
        using (var reader = File.OpenText(csvFileName))
        {
            while (!reader.EndOfStream)
            {
                var fields = reader.ReadLine().Split(new[] { ',' }, StringSplitOptions.None).Select(f => f.Trim()).ToList();
                if (fields.Count > 0)
                    yield return fields;
            }
        }
    }
}

answered Aug 23, 2013 at 23:34

Alex

13.3k36 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Giuliano Piergentili · Accepted Answer · 2015-08-24 17:04:17Z

i have implemented this code to read memo field (Microsoft Access):

  private string GetMemoField(string TableName, string FieldName, string IdentityFieldName, string IdentityFieldValue, OleDbConnection conn)
    {
        string ret = "";

        OleDbCommand cmd1 = new OleDbCommand("SELECT " + FieldName + " FROM “ + TableName + “ WHERE " + IdentityFieldName + "=" + IdentityFieldValue, conn);

                var reader = cmd1.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);  // Create the DataReader that will get the memo field one buffer at a time

        if (reader.Read())
        {
            long numberOfChars = reader.GetChars(/*Field pos*/ 0, 0, null, 0, 0);   // Total number of memo field's chars

            if (numberOfChars > 0)
            {
                int bufferSize = 1024;
                char[] totalBuffer = new char[64*bufferSize];    // Array to hold memo field content

                long dataIndex = 0;

                do
                {

                    char[] buffer = new char[bufferSize];   // Buffer to hold single read
                    long numberOfCharsReaded = reader.GetChars(0, dataIndex, buffer, 0, bufferSize);

                    if (numberOfCharsReaded == 0)
                    {
                        ret = new string(totalBuffer,0, (int)numberOfChars);
                        break;
                    }

                    Array.Copy(buffer, 0, totalBuffer, dataIndex, numberOfCharsReaded);     // Add temporary buffer to main buffer
                    dataIndex += numberOfCharsReaded;

                } while (true);
            }
        }

        return ret;
    }

Collectives™ on Stack Overflow

C# Importing CSV using OleDb

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related