Solved

CSV to Access DB

Posted on 2004-04-18
4
964 Views
Last Modified: 2008-03-03
I'm creating a dataset from a CSV file and would like to put it into an Access DB in the fastest way possible.  
-- I know all of the strucutre and data type information which doesn't change.  
-- I have a table in Access that "mirrors" my CSV file structure.

I've been trying to use an OleDbDataAdaptor connected to my access database to call the Update() method with the CSV dataset and the Access table to simply move the contents without looping, but I haven't been able to make it work.  

Any advise you can give that helps me:
1 (main goal) create a procedure to quickly load my CSV data into Access (I have 40K or less records)
2 (secondary goal) figure out how to my Update() idea to work

Would be greatly appreciated.
0
Comment
Question by:mocs
  • 2
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
tgannetts earned 500 total points
ID: 10857980
Are you thinking of something like this?

The following code takes the schema of the table in the database and loads it into a dataset. The values from the CSV File are then loaded row-by-row into the table, and then updated using the Update command.

using System.Data.OleDb;

...

//Create connection strings
string sCSVConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=""text;HDR=Yes;FMT=Delimited""";      
string sDBConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Test.mdb;";
string sCSVFile = "Test.csv";
      
try
{
     //Create Empty Dataset
     DataSet ds = new DataSet("Test");

     //Create Connection Classes  
     OleDbConnection dbcnn = new OleDbConnection(sDBConnectionString);
     OleDbConnection csvcnn = new OleDbConnection(sCSVConnectionString);

     //Open Data Adapter
     OleDbDataAdapter daDB = new OleDbDataAdapter("SELECT * FROM tbl_Test", dbcnn);

     //Map Schema to Dataset
     daDB.FillSchema(ds, SchemaType.Mapped, "tbl_Test");
                  
     //Open CSV File
     csvcnn.Open();

     OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + sCSVFile + "]", csvcnn);
     OleDbDataReader dr = cmd.ExecuteReader();

     //Read CSV Values into DataTable
     while(dr.Read())
     {
          //Get array of values and add to datatable held in dataset
          object[] values = new object[(ds.Tables[0].Columns.Count)];
          dr.GetValues(values);
          ds.Tables[0].Rows.Add(values);
     }

     //Close connection to CSV File
     csvcnn.Close();
                  
     //Add Rows from CSV File to Database Table
     OleDbCommandBuilder cb = new OleDbCommandBuilder(daDB);
     daDB.Update(ds, "tbl_Test");
                        
}
catch(Exception ex)
{
     MessageBox.Show(ex.Message);
}
0
 

Author Comment

by:mocs
ID: 10859482
Thanks, tgannetts.  This is exactly what I've been playing with, and I have a couple of questions about your implementation:
1.  You're using OleDbCommandBuilder to overide the need for an InsertCommand; is that correct?  Is this faster running than an insertCommand with a parameterized query?
2.  I noticed you're using a dataReader and looping through the rows of the CSV file versus using a second DataApater for the CSV file and doing something like: daCSV.Fill(ds);  Why is that?
3.  I found that with a parameterized InsertCommand (the only way I could get this to work), I had to set all of the ColumnName properties on the CSV ds in order to get the daDB.Update(ds,"tbl"); to work.  Why doesn't your solution need that?
0
 
LVL 5

Expert Comment

by:tgannetts
ID: 10859959
Hi Mocs,

Re your questions:

1. I've found from my experience that using the CommandBuilder as opposed to the InsertCommand makes little difference in time. Having said this, I would definitely use a DeleteCommand and UpdateCommand instead of the CommandBuilder as the CommandBuilder generates a lot of unnecessary SQL code and makes an extra round trip to the database for these actions.
2. The Update method checks the rowstate of each row to determine the correct command action to take, if any. The Fill method using a dataadapter fills the datatable with the rows from the CSV and sets the rowstate to 'Unchanged', which will be ignored by the Update. The new rows added to the datatable whilst reading from the Data Reader are set to 'Added', which will be picked up by the Update method.
3. In this method, the data adapter creates the datatable to store the CSV values using the FillSchema method, which sets all the column properties as per the database table. When the values from the CSV are filled into this table, each one has to match this schema otherwise they will be ignored.

Hope this explains my methods a little...

Tom.

0
 

Author Comment

by:mocs
ID: 10861410
"2. The Update method checks the rowstate of each row to determine the correct command action to take, if any. The Fill method using a dataadapter fills the datatable with the rows from the CSV and sets the rowstate to 'Unchanged', which will be ignored by the Update. The new rows added to the datatable whilst reading from the Data Reader are set to 'Added', which will be picked up by the Update method."

Oh, I see.  Would it work to set daCSV.AcceptChangesDuringFill = false;?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question