Solved

CSV to Access DB

Posted on 2004-04-18
4
958 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connecting database in Xamarin? 6 52
Anyway to make "All" the default in the dropdown? 6 34
Not showing JavaScript in the list 5 39
how to read json value 2 31
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now