Copy CSV records into MS Access table

Question

0.00/5 (No votes)

See more:

Hi I'm trying to save the contents of a CSV file into a Table in my Access DB, so far I have been able to select the records of the CSV file and put them into a DataTable. I'm not sure how to manipulate the DataTable to do the actual INSERT into the Table can anyone point me in the right direction?

C#

public static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)//here Path is root of file and IsFirstRowHeader is header is there or not
        {
            string sql = string.Empty;
            DataTable dataTable = null;
            string databaseName = string.Empty;
            string pathOnly = string.Empty;
            string fileName = string.Empty;

            try
            {
                databaseName = Path.GetDirectoryName(ConfigurationManager.AppSettings["DatabaseName"]);
                pathOnly = Path.GetDirectoryName(ConfigurationManager.AppSettings["QuantumOutputFilesLocation"]);
                fileName = Path.GetFileName(ConfigurationManager.AppSettings["CSVFilename"]);

                sql = @"SELECT * FROM [" + fileName + "]";

                using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName + ""))
                {
                    using (OleDbCommand command = new OleDbCommand(sql, connection))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            dataTable = new DataTable();
                            dataTable.Locale = CultureInfo.CurrentCulture;
                            adapter.Fill(dataTable);
                            //this is what I'm trying
                            adapter.InsertCommand = new OleDbCommand("INSERT INTO tblQuantum (DateEntered, SerialNumber, ModelNumber, BatchNumber, DeviceType, RatedPower, EnergyStorageCapacity " +
                                                                     "MaxEnergyStorageCapacity, User_IF_FWRevNo, Charge_Controller_FWRevNo, RF_Module_FWRevNo, SSEGroupNumber, TariffSetting) " +
                                                                     "VALUES (?, ?)"); //how do i read each row in the dataTable to retrieve the necessary values for each column??
                            adapter.Update(dataTable);
                        }
                    }
                }
            }
            finally
            {

            }
            return dataTable;
        }

Posted 16-Jul-12 23:36pm

pmcm

Add a Solution

Comments

Herman<T>.Instance 17-Jul-12 6:21am

instead of using VALUES do an insert based on a select * from your file

pmcm 17-Jul-12 6:27am

can you show me the syntax to perform this?
Plus if I'm already doing a select * from my file to fill a datatable can I not just select the values from the datatable?

Herman<T>.Instance 18-Jul-12 5:55am

of course

2 solutions

Solution 1

I think you should look into DoCmd.TransferText for this because that would probably make it way easier. Check this link:
http://msdn.microsoft.com/en-us/library/aa220768%28v=office.11%29.aspx[^]

Good luck!

Posted 17-Jul-12 4:44am

E.F. Nijboer

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pmcm · Accepted Answer · 2012-07-18T00:23:00

here's my final working code:

C#

public static DataTable CsvFileToDatatable(string path, bool IsFirstRowHeader)//here Path is root of file and IsFirstRowHeader is header is there or not
        {
            string header = "Yes"; //"No" if 1st row is not header cols
            string query = string.Empty;
            DataTable dataTable = null;
            string filePath = string.Empty;
            string fileName = string.Empty;

            try
            {
                //csv file directory
                filePath = Path.GetDirectoryName(ConfigurationManager.AppSettings["QuantumOutputFilesLocation"]);
                //csv file name
                fileName = Path.GetFileName(ConfigurationManager.AppSettings["CSVFilename"]);

                query = @"SELECT * FROM [" + fileName + "]";

                if (IsFirstRowHeader) header = "Yes";

                using (OleDbConnection connection = new OleDbConnection((@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Text;HDR=" + header + "\"")))
                {
                    using (OleDbCommand command = new OleDbCommand(query, connection))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            dataTable = new DataTable();
                            adapter.Fill(dataTable);
                            
                            try
                            {
                                //create connection to Access DB
                                OleDbConnection DBconn = new OleDbConnection(ConfigurationManager.ConnectionStrings["Seagoe_QuantumConnectionString"].ConnectionString);
                                OleDbCommand cmd = new OleDbCommand();
                                //set cmd settings
                                cmd.Connection = DBconn;
                                cmd.CommandType = CommandType.Text;
                                //open DB connection
                                DBconn.Open();
                                //read each row in the Datatable and insert that record into the DB
                                for (int i = 0; i < dataTable.Rows.Count; i++)
                                {
                                    cmd.CommandText = "INSERT INTO tblQuantum (DateEntered, Series, SerialNumber, YearCode, ModelNumber, BatchNumber, DeviceType, RatedPower, EnergyStorageCapacity," +
                                                                              "MaxEnergyStorageCapacity, User_IF_FWRevNo, Charge_Controller_FWRevNo, RF_Module_FWRevNo, SSEGroupNumber, TariffSetting)" +
                                                     " VALUES ('" + dataTable.Rows[i].ItemArray.GetValue(0) + "','" + dataTable.Rows[i].ItemArray.GetValue(1) + "','" + dataTable.Rows[i].ItemArray.GetValue(2) +
                                                     "','" + dataTable.Rows[i].ItemArray.GetValue(3) + "','" + dataTable.Rows[i].ItemArray.GetValue(4) + "','" + dataTable.Rows[i].ItemArray.GetValue(5) +
                                                     "','" + dataTable.Rows[i].ItemArray.GetValue(6) + "','" + dataTable.Rows[i].ItemArray.GetValue(7) + "','" + dataTable.Rows[i].ItemArray.GetValue(8) +
                                                     "','" + dataTable.Rows[i].ItemArray.GetValue(9) + "','" + dataTable.Rows[i].ItemArray.GetValue(10) + "','" + dataTable.Rows[i].ItemArray.GetValue(11) +
                                                     "','" + dataTable.Rows[i].ItemArray.GetValue(12) + "','" + dataTable.Rows[i].ItemArray.GetValue(13) + "','" + dataTable.Rows[i].ItemArray.GetValue(14) + "')";

                                    cmd.ExecuteNonQuery();
                                }
                                //close DB.connection
                                DBconn.Close();
                            }
                            catch (Exception ex)
                            {
                                sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message);
                            }                            
                        }
                    }
                }
                //checks folder exists
                if (Directory.Exists(filePath))
                {
                    //deletes all folder contents and recreates an empty folder
                    Directory.Delete(filePath, true);
                    Directory.CreateDirectory(filePath);
                }
            }
            catch (Exception ex)
            {
                sendEmail(ConfigurationManager.AppSettings["QuantumEmailFrom"], ConfigurationManager.AppSettings["QuantumEmailTo"], "Quantum CSV Import To SMS Database FAILED", ex.Message);
            }

            return dataTable;