About reading multiple .dbf files in C#

Question

Currently I'm making a program to import values from multiple dBase files (.dbf). I have already an List full of Strings that have the name of the dBase files. That list is named FicheirosParaAnalisar. The dBase files not always have the same lines but they have all the same columns full of values that I want from one specific column named as sigla_parametro. The dBase filse are in path9 My code by now is something like this:

private void button6_Click(object sender, EventArgs e)
    {
        ///*

        //Console.WriteLine(sigla_parametro);
        try
        {
            parametro = comboBox12.Text; //Console.WriteLine(parametro);

            for (int FA = 0; FA < FicheirosParaAnalisar.Count; FA++)
            {
                //Console.WriteLine(FicheirosParaAnalisar[FA]+".dbf");

                if (importar == true)
                {
                    //........................................................................
                    string connectionStringTotal = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path9 + ";Extended Properties=dBASE III;";

                    OleDbConnection connectionsTotal = new OleDbConnection(connectionStringTotal);
                    connectionsTotal.Open();

                    // Comando de aquisição dos dados da Base de Dados
                    string Comand_Strings_Total = "Select * From [" + FicheirosParaAnalisar[FA] + "]";

                    OleDbDataAdapter DataAdaptersTotal = new OleDbDataAdapter(Comand_Strings_Total, connectionsTotal);
                    DataSet datasetsTotal = new DataSet();
                    DataAdaptersTotal.Fill(datasetsTotal);

                    // Obter os valores dos parâmetros
                    int count = datasetsTotal.Tables[0].Rows.Count;
                    for (int i = 0; i < count; i++)
                    {
                        try
                        {
                            valores_parametro.Add(FicheirosParaAnalisar[FA] + "_" + datasetsTotal.Tables[0].Rows[i][sigla_parametro].ToString());
                        }
                        catch (System.Data.OleDb.OleDbException exe)
                        {
                            string text = "[MPA5 - " + DateTime.Now.ToString() + "] Excepção encontrada: " + exe.Message + "\n";
                            System.IO.File.AppendAllText(log, text);
                        }
                        catch (Exception lolex)
                        {
                            string text = "[MPA5 - " + DateTime.Now.ToString() + "] Excepção encontrada: " + lolex.Message + "\n";
                            System.IO.File.AppendAllText(log, text);
                        }
                    }
                    count = 0;
                }
                Console.WriteLine("Ficheiro nº " + FA + " Importado!");
            }  
        }
        catch (Exception ex)
        {
            string text = "[MPA5 - " + DateTime.Now.ToString() + "] Excepção encontrada: " + ex.Message + "\n";
            System.IO.File.AppendAllText(log, text);
        }

        //*/

        valores_parametro.ForEach(Console.WriteLine);

        button1.Enabled = true;
    }

This code above only compute if I found all the dBase files (if so the variable importar becomes true). In theory I'm doing everything right, because the files exists and I have assigned every variable correctly, but really I have a problem in this code, because when I'm reading all the dBase files some files can't be read and an exception appear as I can see in my LogFile.txt. The message that appears is:

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Now I want to ask you experts from Stackoverflow in C#, what I'm doing wrong? How can I get all the data from the column sigla_parametro to the List fo strings valores_parametro? How can I resolve my exception?

Note: I've also tried to use this Connection String

string connectionStringTotal = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path9 + ";Extended Properties=dBASE IV;";

The string path9 is the path to the folder that contains all my dBase files.

string path9 = "C:\\ProgramData\\CK electronic\\MPA5\\Data";

The List of strings FicheirosParaAnalisar has this strings:

CL-1831-150615-1
CL-1831-150615-2
CL-1831-150615-3
CL-1831-150615-4
CL-1831-150615-5
CL-1832-150611-1
CL-1832-150611-2
CL-1832-150611-3
CL-1832-150611-4
CL-1832-150611-5
CL-1833-150611-1
CL-1833-150611-2
CL-1833-150611-3
CL-1833-150611-4
CL-1833-150611-5
CL-1834-150611-1
CL-1834-150611-2
CL-1834-150611-3
CL-1834-150611-4
CL-1834-150611-5
CL-1835-150611-1
CL-1835-150611-2
CL-1835-150611-3
CL-1835-150611-4
CL-1835-150611-5
CL-1836-150612-1
CL-1836-150612-2
CL-1836-150612-3
CL-1836-150612-4
CL-1836-150612-5
CL-1837-150618-1
CL-1837-150618-2
CL-1837-150618-3
CL-1837-150618-4
CL-1837-150618-5
CL-1838-150611-1
CL-1838-150611-2
CL-1838-150611-3
CL-1838-150611-4
CL-1838-150611-5
CL-1839-150617-1
CL-1839-150617-2
CL-1839-150617-3
CL-1839-150617-4
CL-1839-150617-5
CL-1840-150611-1
CL-1840-150611-2
CL-1840-150611-3
CL-1840-150611-4
CL-1840-150611-5
CL-1841-150611-1
CL-1841-150611-2
CL-1841-150611-3
CL-1841-150611-4
CL-1841-150611-5
CL-1842-150611-1
CL-1842-150611-2
CL-1842-150611-3
CL-1842-150611-4
CL-1842-150611-5
CL-1843-150612-1
CL-1843-150612-2
CL-1843-150612-3
CL-1843-150612-4
CL-1843-150612-5
CL-1844-150616-1
CL-1844-150616-2
CL-1844-150616-3
CL-1844-150616-4
CL-1844-150616-5
CL-1844-150616-6
CL-1845-150615-1
CL-1845-150615-2
CL-1845-150615-3
CL-1845-150615-4
CL-1845-150615-5
CL-1846-150616-1
CL-1846-150616-2
CL-1846-150616-3
CL-1846-150616-4
CL-1847-150612-1
CL-1847-150612-2
CL-1847-150612-3
CL-1847-150612-4
CL-1847-150612-5
CL-1848-150612-1
CL-1848-150612-2
CL-1848-150612-3
CL-1848-150612-4
CL-1848-150612-5
CL-1850-150617-1
CL-1850-150617-2
CL-1850-150617-3
CL-1850-150617-4
CL-1850-150617-5
CL-1851-150616-1
CL-1851-150616-2
CL-1851-150616-3
CL-1851-150616-4
CL-1851-150616-5
CL-1852-150612-1
CL-1852-150612-2
CL-1852-150612-3
CL-1852-150612-4
CL-1852-150612-5
CL-1853-150616-1
CL-1853-150616-2
CL-1853-150616-3
CL-1853-150616-4
CL-1853-150616-5
CL-1854-150615-1
CL-1854-150615-2
CL-1854-150615-3
CL-1854-150615-4
CL-1854-150615-5
CL-1855-150616-1
CL-1855-150616-2
CL-1855-150616-3
CL-1855-150616-4
CL-1855-150616-5
CL-1856-150615-1
CL-1856-150615-2
CL-1856-150615-3
CL-1856-150615-4
CL-1856-150615-5
CL-1857-150616-1
CL-1857-150616-2
CL-1857-150616-3
CL-1857-150616-4
CL-1857-150616-5
CL-1858-150618-1
CL-1858-150618-2
CL-1858-150618-3
CL-1858-150618-4
CL-1858-150618-5
CL-1860-150618-1
CL-1860-150618-2
CL-1860-150618-3
CL-1860-150618-4
CL-1860-150618-5
CL-1861-150618-1
CL-1861-150618-2
CL-1861-150618-3
CL-1861-150618-4
CL-1861-150618-5
CL-1862-150618-1
CL-1862-150618-2
CL-1862-150618-3
CL-1862-150618-4
CL-1862-150618-5
CL-1863-150618-1
CL-1863-150618-2
CL-1863-150618-3
CL-1863-150618-4
CL-1863-150618-5

It is crucial to tell you that I have administrator permition to read and write data in the folder!

DRapp · Accepted Answer · 2015-10-14T15:10:09.333

Without having sample of your actual variables, I would pose this question and think it MIGHT be your answer.

Your OleDb connection string should point to the PATH ONLY where the tables are located and NOT the PATH + TABLE NAME. Once connected to a PATH, you can query ANY table within that path...

ex:

string connectionStringTotal = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomePathToYourData;Extended Properties=dBASE III;";

OleDbConnection connectionsTotal = new OleDbConnection(connectionStringTotal);
connectionsTotal.Open();

Then, you can

string Comand_Strings_Total = "Select * From Table1";
string Comand_Strings_Total = "Select * From Table2";
string Comand_Strings_Total = "Select * From Table3";

(or built as you have it with your for loop)

Hence if you ever needed to JOIN tables, you could such as

select T1.*, T2.*
   from Table1 T1
      JOIN Table2 T2
         on T1.Key = T2.MatchingKey

Both tables reside in same folder would be seen.

Ok, that your path variable IS just to the path... several times in the past, others have had the connection to INCLUDE the dbf by mistake.

As for your table names, all of them having the "-" in them is probably what is choking the OleDb provider. Yes, you have [square-brackets] around the table, but maybe that might not be good enough. You may need to change it to QUOTES, and possibly even add "alias" to your query... Maybe something like..

// This version uses double-quotes around the table name
string Comand_Strings_Total = string.Format( "Select * From \"{0}\" ", FicheirosParaAnalisar[FA] );

or this which adds an ALIAS "tmp" reference for the query.

string Comand_Strings_Total = string.Format( "Select tmp.* From \"{0}\" tmp", FicheirosParaAnalisar[FA] );

If STILL error, are the files specifically dBASE, or just .dbf. You MIGHT be able to get it working by downloading Microsoft's Visual Foxpro OleDb Data Provider. The rest of the OleDb context of connection, commands, data adapter are the same. It just might provide better connection options for you. Just need to change the provider connection string. to

@"Provider=VFPOLEDB.1;Data Source=c:\\YourDataPath\\SomeSubFolder;";

VFP OleDb Provider Download

Please see my answer to your question below as it was too long to comment :) — user3664117, Oct 14 '15 at 14:34
@user3664117, revised my answer... and in the future, instead of posting an answer to your own question for clarification, you can always EDIT your original post and add detail content to it like you posted in your answer. below. — DRapp, Oct 14 '15 at 14:41
I'm sorry for my answer DRapp, I'm new to this community. I'll try your solution. Thanks mate :) — user3664117, Oct 14 '15 at 14:46
Did use your connections strings and did get the same exception as before. Can't see what is the problem here :( — user3664117, Oct 14 '15 at 15:03
@user3664117, see final edit to my post about changing data provider / connection string. — DRapp, Oct 14 '15 at 15:11

About reading multiple .dbf files in C#

1 Answers1