Effective way to load huge data into datatable

Question

0.00/5 (No votes)

See more:

Hi I am loading DBF file into existing sql table in c# console application. First of all my dbf file having 8 ,50,000+ records i had load the whole DBF file into datatable, It's lead to out of memory exception when processing 400000+ records. So i have read a record at a time and add into the datatable as a row. It's worked fine untill 6,00,000+ records. After this record i am facing System.OutofMemoryException. Please anyone help me to load DBF file into sql table in c# console application. My code is here, I have tried lots of code optimizations but no use

C#

 public void ConvertDBFtoSQL() {
     string connStr = @"Provider=VFPOLEDB.1;Data Source=dbf file path;Persist              Security Info=False; User ID=Admin;Password=;";
     using (OleDbConnection oleDbConnection = new OleDbConnection(connStr))
     {
         oleDbConnection.Open();
         string query = "select * from dbftable";
         OleDbCommand oleDbCommand = new OleDbCommand(query, oleDbConnection);
         oleDbCommand.CommandTimeout = 600000;
         OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader();
         int fieldCount = oleDbDataReader.FieldCount;
         DataTable dataTable= CreateDataTable();
         string[] rows = new string[fieldCount];
         while (oleDbDataReader.Read())
         {                     
             using (dataTable)
             {
                 Array.Clear(rows, 0, rows.Length);
                 for (int i = 0; i < fieldCount; i++)
                 {
                     try
                     {
      rows[i] = oleDbDataReader.GetFieldType(i).Name.ToLower() == "datetime"?
"\"" + Convert.ToDateTime(oleDbDataReader.GetValue(i)).ToString("dd/MM/yyyy").Substring(0, 10) + "\"":
                         
                             (oleDbDataReader.GetValue(i).ToString().Contains(",") ? "" :"\"" + oleDbDataReader.GetValue(i).ToString().Trim() + "\"");                                 
                     }
                     catch (Exception ex) { }
                 }
                 string row = string.Join(",", rows);
                 dataTable.Rows.Add(new object[] { row });
             }
         }
     }
}

What I have tried:

First i tried dataTable.Load(oledbcommand.executereader()) it's fail with out of memory exception then i have tried with reading a row and add into a datatable.

Posted 10-Nov-16 20:21pm

sankarisiva

Updated 11-Nov-16 1:13am

Tomas Takac

v3

Add a Solution

Comments

Michael_Davies 11-Nov-16 2:37am

Why do you load them all? No one can possibly read all of them or need to see them all!

Tomas Takac 11-Nov-16 2:38am

The data is just too big, you cannot load it all into memory. Why do you need to do that in the first place?

sankarisiva 11-Nov-16 2:43am

Because I need to load this whole data into a sql table. If i datatable has whole data then i can use bulkinsert option to load into the sql table

Tomas Takac 11-Nov-16 2:50am

No, you don't need to load it all into memory. SqlBulkCopy can use a data reader.[^]

3 solutions

Solution 3

Have a look at this:
VFP: Importing VFP data into SQL Server[^]
How to convert Visual Foxpro database into SQL Server database - Stack Overflow[^]
Importing DBF files into SQL Server[^]

Posted 11-Nov-16 1:13am

Patrice T

Add a Solution

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

Tomas Takac · Accepted Answer · 2016-11-10T20:58:00

Solution 2

You can use data reader with SqlBulkCopy:

C#

const string sourceConnectionString = "abc";
const string targetConnectionString = "xyz";

using (var sourceConnection = new OleDbConnection(sourceConnectionString))
using (var targetConnection = new SqlConnection(targetConnectionString))
using (var sourceCommand = new OleDbCommand("select * from dbftable", sourceConnection))
{
	sourceConnection.Open();
	targetConnection.Open();
	
	using (var reader = sourceCommand.ExecuteReader())
	using (var bulkCopy = new SqlBulkCopy(targetConnection))
	{
		bulkCopy.DestinationTableName = "aTableName";
		bulkCopy.BatchSize = 1000;
		bulkCopy.WriteToServer(reader);
	}
}

Posted 10-Nov-16 20:58pm

Tomas Takac

Updated 11-Nov-16 0:23am

v2

Comments

sankarisiva 11-Nov-16 4:13am

Sorry for the delay and thanks for your reply. I have tried this method but it produces timeout exception and no records are inserted into the destination table.

F-ES Sitecore 11-Nov-16 5:46am

Just process the data in chunks of a thousand or whatever instead of all at once. If you had to move 10 boulders from A to B and you're not strong enough to carry them all at once you have to do them 1 at a time, it's the same concept here.

Tomas Takac 11-Nov-16 6:24am

As F-ES said, set the batch size to an appropriate value. See my edit.

sankarisiva 11-Nov-16 8:50am

Thanks. This method works fine

Mehdi Gholam · Accepted Answer · 2016-11-10T20:48:00

Solution 1

Read the following : foxpro - How to import a DBF file in SQL Server - Stack Overflow[^]

Posted 10-Nov-16 20:48pm

Mehdi Gholam

Comments

sankarisiva 11-Nov-16 4:22am

I have already those methods, Those produce exception as syntax error

Mehdi Gholam 11-Nov-16 4:37am

1) Select less data by using a filter, and do it in chunks
2) Run on a 64bit system.

sankarisiva 11-Nov-16 4:44am

I am running on 86bit

Mehdi Gholam 11-Nov-16 4:46am

86bit??

sankarisiva 11-Nov-16 4:50am

I have set my build platform as x86. Then only i could the dbf file

Mehdi Gholam 11-Nov-16 4:51am

Then filter your data and import in logical chunks.

sankarisiva 11-Nov-16 4:54am

Are you saying like insert into sql table by chunks of dbf records. i am right?

Mehdi Gholam 11-Nov-16 4:56am

"select * from dbftable where ... " -> fill in the where part.

sankarisiva 11-Nov-16 5:03am

I think in my situation filtering data is not possible. I need to load all those data available in the dbf file. Can you suggest me a different solution?

Mehdi Gholam 11-Nov-16 5:04am

Why not? filter your data to make it smaller and change your filter until you have covered all the rows.

sankarisiva 11-Nov-16 5:08am

Sorry I can't get your thought. I am having a columns like a,b,c,d,... totally 74 columns and 850000+ records, How can I filter this. There is no constraint with the datas

Mehdi Gholam 11-Nov-16 5:09am

Try adding a row number, and filter on that.

sankarisiva 11-Nov-16 5:15am

You are correct. But i can't change the source dbf file.This is the problem i am facing

sankarisiva 11-Nov-16 8:50am

Thanks this method works fine Mehdi Gholam...

Effective way to load huge data into datatable

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Effective way to load huge data into datatable

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us