mapping of column name in excel file with database table

How to mapp Excel column with database table columns

Page 1 of 1

5 Replies - 25466 Views - Last Post: 03 December 2008 - 11:29 PM Rate Topic: -----

#1 sumit_701  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 31-May 06

mapping of column name in excel file with database table

Posted 31 May 2006 - 05:14 AM

hi!!
in asp.net,
I have to fetch data from excel file and store that data in sql server table and i have fetch only those columns of data from excel ,which i mapped with perticular columns in database table.

Pls help!
Thanks in advance.
Is This A Good Question/Topic? 0
  • +

Replies To: mapping of column name in excel file with database table

#2 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 248
  • View blog
  • Posts: 13,506
  • Joined: 12-July 02

Re: mapping of column name in excel file with database table

Posted 31 May 2006 - 07:01 AM

Which portion are you having trouble with? Opening the excel file and retrieving data? Or writing that data to the database?
Was This Post Helpful? 0
  • +
  • -

#3 sumit_701  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 31-May 06

Re: mapping of column name in excel file with database table

Posted 31 May 2006 - 10:02 PM

View PostAmadeus, on 31 May, 2006 - 05:53 AM, said:

Which portion are you having trouble with? Opening the excel file and retrieving data? Or writing that data to the database?


Actually I have sucessfully make connection to the excel file but i dont any idea, how to retrive mapped columns data from excel and how to store that data which are fetched from excel file in to data base table.
Was This Post Helpful? 0
  • +
  • -

#7 aspnetmorocco  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 1
  • Joined: 22-May 07

Re: mapping of column name in excel file with database table

Posted 22 May 2007 - 02:56 AM

Hi,

you said taht you connect to th excel file, good

find below a program that connect to the excel file and read its columns

public String[] GetExcelSheetColums(string filename){

OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{

String cxString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";Extended Properties=Excel 8.0;";

objCx = new OleDbConnection(cxString);

objCx.Open();

String[] restrection = {null,null,"Sheet1$",null}

dt = objCx.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, restrection);

if(dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;


foreach(DataRow row in dt.Rows)
{
excelSheets[i] = row["COLUMN_NAME"].ToString();
i++;
}


for(int j=0; j < excelSheets.Length; j++)
{

// you the column name to deal with your database!!!!
}

return excelSheets;
}
catch(Exception ex)
{
return null;
}
finally
{
Clean up.
if(objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if(dt != null)
{
dt.Dispose();
}
}
}
Was This Post Helpful? 1

#8 CBosco  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 03-December 08

Re: mapping of column name in excel file with database table

Posted 03 December 2008 - 11:35 AM

View Postsumit_701, on 31 May, 2006 - 04:14 AM, said:

hi!!
in asp.net,
I have to fetch data from excel file and store that data in sql server table and i have fetch only those columns of data from excel ,which i mapped with perticular columns in database table.

Pls help!
Thanks in advance.


Hi

I have to do the same from txt file to sql server. I know how to do the datatransfer from txt to sqlserver. But the problem is I don't know how to map txt file columns to sqlserver columns.

Users can upload a txt file with address,city,county,state,zipcode and other fields. other fields might differ from user to user. Now I want to get only the address fields and load it into sqlserver. How cam do the mapping. Someone plz help....

Thanks,
CBosco
Was This Post Helpful? 0
  • +
  • -

#9 wingot  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 52
  • Joined: 13-October 08

Re: mapping of column name in excel file with database table

Posted 03 December 2008 - 11:29 PM

View PostCBosco, on 3 Dec, 2008 - 10:35 AM, said:

View Postsumit_701, on 31 May, 2006 - 04:14 AM, said:

*snip*


*snip*


Hi CBosco,

Please do not respond to old posts. In this case the old post was two and a half years old.

If you have a query (as in this case), please create a new post.

Regards,
Adrian
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1