locked
How to remove characters from a dataset RRS feed

  • Question

  • User-718146471 posted

    I'm trying to remove extraneous characters like quotes, commas, etc from my dataset. I get the fact in the standard way of doing things in your code behind you simply go

    string data = data.replace(",",""); or something like this. However, a datasource doesn't seem to give me that capability. What can I do to make it do this? I'm importing data from an excel sheet into a gridview. The commas are goofing up my view. I'd like to replace any commas in the dataset with spaces. Here is the code I have.

        {
            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\ExcelFile.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""";
            // Create the connection object    
            OleDbConnection oledbConn = new OleDbConnection(connectionString);
            // Open connection   
    
            // if you don't want to show the header row (first row)
            // use 'HDR=NO' in the string
    
            string strSQL = "SELECT * FROM [Sheet1$]";
    
            OleDbConnection excelConnection = new OleDbConnection(connectionString);
            excelConnection.Open(); // This code will open excel file.
    
            OleDbCommand cmd = new OleDbCommand(strSQL, excelConnection);
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmd);
            dataAdapter.SelectCommand = cmd;
    
            // Create new OleDbDataAdapter    
            OleDbDataAdapter oleda = new OleDbDataAdapter();
            oleda.SelectCommand = cmd;
            // Create a DataSet which will hold the data extracted from the worksheet.   
            DataSet ds = new DataSet();
            // Fill the DataSet from the data extracted from the worksheet.   
            oleda.Fill(ds, "Mystuff");
            // Bind the data to the GridView   
            GridView1.DataSource = ds;
            
            GridView1.DataBind();
        }


     

    Monday, February 14, 2011 2:18 PM

Answers

  • User1974023959 posted

     I think it is best to do it in the query itself rather than to loop all the rows in the dataset and format.

    Like Select replace(replace(column1,'&','&'),'''', ''') as Name from [Sheet1$]";   

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 15, 2011 12:22 AM

All replies

  • User1974023959 posted

     I think it is best to do it in the query itself rather than to loop all the rows in the dataset and format.

    Like Select replace(replace(column1,'&','&'),'''', ''') as Name from [Sheet1$]";   

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 15, 2011 12:22 AM
  • User-718146471 posted

    Ok, debugger is yelling about Replace being an undefined function. I should also mention why I want to use the dataset instead because the commas may appear in other fields than just one.

    Tuesday, February 15, 2011 6:32 AM
  • User-718146471 posted

    For my purposes, I decided instead of using comma separated values to use Pipe Delimited. Works just fine for what I'm doing, thanks :) 

    Tuesday, February 15, 2011 6:51 AM