CODEWITHSUNDEEP
c#parsingcsvoledb
Albert Bori
Albert Bori

Reputation: 10012

C# Importing CSV using OleDb

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.

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.

Upvotes: 0

Views: 696

Answers (2)

Giuliano Piergentili
Giuliano Piergentili

Reputation: 1

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;
    }

Upvotes: 0

Alex
Alex

Reputation: 13244

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;
            }
        }
    }
}

Upvotes: 1

Related Questions