Manupulating Columns of datatable
-
Hi experts I have designed my page to allow users to upload excel/csv files and I am interested to capture the data from only few columns of these files. The column names of the files varies from user to user hence i created the form to obtain the column names of user's file for mapping to my database. Now my requirement is that I want to first read the column names of uploaded file and retain only columns and data which are matching to the user entered column names for mapping. clues or guidance solicited preferably in vb.net thanks in advance kris
-
Hi experts I have designed my page to allow users to upload excel/csv files and I am interested to capture the data from only few columns of these files. The column names of the files varies from user to user hence i created the form to obtain the column names of user's file for mapping to my database. Now my requirement is that I want to first read the column names of uploaded file and retain only columns and data which are matching to the user entered column names for mapping. clues or guidance solicited preferably in vb.net thanks in advance kris
Do following code you can read all data in datatable which easily fill you requirement FileInfo fileinfo = new FileInfo(filePath); string strSql = "SELECT * FROM [" + fileinfo.Name + "]"; string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileinfo.Directory.FullName + ";" + "Extended Properties='text;HDR=No;'"; OleDbDataAdapter oleda = new OleDbDataAdapter(strSql, strCSVConnString); DataTable dtbCSV = new DataTable(); oleda.Fill(dtbCSV);
-
Do following code you can read all data in datatable which easily fill you requirement FileInfo fileinfo = new FileInfo(filePath); string strSql = "SELECT * FROM [" + fileinfo.Name + "]"; string strCSVConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileinfo.Directory.FullName + ";" + "Extended Properties='text;HDR=No;'"; OleDbDataAdapter oleda = new OleDbDataAdapter(strSql, strCSVConnString); DataTable dtbCSV = new DataTable(); oleda.Fill(dtbCSV);
Hi pranay thanks for response. what you explained is all about connection and fetching data to data table which is ok. My actual requirement starts there after. I would like to explain the requirements stepwise here 1.Establishing connection to db and populating data table 2.Reading the column names of the data table and retaining only columns and data which user has specified as mapping fields in page, which too may not be in sequence. 3.Adding few more columns dynamically to data table with default values 4.finally binding data to grid for user editing and final saving to db thanks kris
-
Hi pranay thanks for response. what you explained is all about connection and fetching data to data table which is ok. My actual requirement starts there after. I would like to explain the requirements stepwise here 1.Establishing connection to db and populating data table 2.Reading the column names of the data table and retaining only columns and data which user has specified as mapping fields in page, which too may not be in sequence. 3.Adding few more columns dynamically to data table with default values 4.finally binding data to grid for user editing and final saving to db thanks kris
In that case I think first row in csv file must be name of columns. So for that read csv file in the data table. Than create you dynamic table by looping through fist row of your data table and create one dynamic datatable in between that by comparing selected row. Than loop through other rows similar fashion and read whole file and insert data row in datatable created by you. I hope that this will work for you.