How to read pipe delimeted file and export the data to Oracle?

Posted by Nisha03 under Visual Studio on 4/22/2011 | Points: 10 | Views : 2724 | Status : [Member] | Replies : 3
I have one flat file which has information delimited by pipe. I need to read the file and insert data to Oracle.

Can someone please help me?

Thanks a lot in advance!!




Responses

Posted by: Ndebata on: 4/22/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Nisha,

What you can do read the file and store in a datatable.

The following function read all the lines to a collection of string array.
private IEnumerable<string[]> GetAllLines()

{
string str;
using (StreamReader rd=new StreamReader(<Flat File Path>))
{
while ((str = rd.ReadLine()) != null)
{
yield return str.Split('|');
}
}
}


Now lets create a datatable out of these collection,
Here I assume that first line of flat file contains the Column Name
var values = GetAllLines();

DataTable dt = new DataTable();
foreach (var item in values.FirstOrDefault())
{
if (!dt.Columns.Contains(item))
dt.Columns.Add(item);
}
var allvalues=values.Skip(1).ToList();
for (int i = 0; i < allvalues.Count; i++)
{
dt.Rows.Add(allvalues[i].Take(dt.Columns.Count).ToArray());
}


Then you can use SQLBulkCopy class to save the datatable to an actual table in database.

SqlConnection dbconnection = new SqlConnection(<Connection String>);
SqlBulkCopy sqlblkcpy=new SqlBulkCopy(dbconnection);
try
{
dbconnection.Open();
sqlblkcpy.DestinationTableName = "<Your destination table name in database>";

// I assume column name in flat file is same as table column names in database
// else use explicit column mapping.

foreach (var item in dt.Columns)
{
sqlblkcpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item, item));
}

//this statement writes the datatable data in to database in destination table.
if(dt.Rows.Count>0)
sqlblkcpy.WriteToServer(dt);
}


Thanks,
Debata

Nisha03, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nisha03 on: 4/25/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks Debata,

Could you please help the same for Oracle database?

Nisha03, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nisha03 on: 4/25/2011 [Member] Starter | Points: 25

Up
0
Down
I am new to vb.net.

I would really appreciate if you or someone can help me with sample code. Also, I was looking for inserting data in Oracle database.

Thanks in advance.

Nisha03, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response