Link to home
Start Free TrialLog in
Avatar of JCWEBHOST
JCWEBHOST

asked on

sql in excel

hey guys i need to join two select statement but i keep get an erorr.

the select statement is selecting the cell A1

and then A2 right to the last record


i need to join cell A1 to all of the records


here my code

 string connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;
        string connectionOleDbString = "";

        DataTable dt = new DataTable();

        if (AsyncFileUpload.HasFile)
        {
            string fileName = Path.GetFileName(AsyncFileUpload.PostedFile.FileName);
            string fileExtension = Path.GetExtension(AsyncFileUpload.PostedFile.FileName);
            string fileLocation = Server.MapPath("~/temp-files/" + fileName);
            AsyncFileUpload.SaveAs(fileLocation);

            if (fileExtension == ".xls")
            {
                connectionOleDbString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\"";
            }
            else if (fileExtension == ".xlsx")
            {
                connectionOleDbString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\"";
            }

            OleDbConnection con = new OleDbConnection(connectionOleDbString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString().Replace("'", "");
            cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H]; SELECT * FROM [" + getExcelSheetName + "A1:A1]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dt);
            con.Close();

            SqlConnection objConnection = new SqlConnection(connectionString);

            if (objConnection.State == ConnectionState.Open)
            {
                objConnection.Close();
            }

            objConnection.Open();
            SqlBulkCopy objBlkCp = new SqlBulkCopy(objConnection);
     
            objBlkCp.ColumnMappings.Add("city", "city");
            objBlkCp.ColumnMappings.Add("Deal End", "deal_end");
            objBlkCp.ColumnMappings.Add("merchant_id", "merchant_id");
            objBlkCp.ColumnMappings.Add("merchant_name", "merchant_name");
            objBlkCp.ColumnMappings.Add("deal_id", "deal_id");
            objBlkCp.ColumnMappings.Add("voucher_code", "voucher_code");
            objBlkCp.ColumnMappings.Add("u_first_name", "u_first_name");
            objBlkCp.ColumnMappings.Add("u_last_name", "u_last_name");

            objBlkCp.DestinationTableName = "groupon";
            objBlkCp.WriteToServer(dt);
            objConnection.Close();
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Does this work?

cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H] UNION SELECT * FROM [" + getExcelSheetName + "A1:A1]";
ps.
This looks odd, is it correct (no number after the H) ?   [" + getExcelSheetName + "A2:H]
Avatar of JCWEBHOST
JCWEBHOST

ASKER

h is a coloum
OK.  So you want the range A2 (cell) : H (column) - to me that doesn't make sense.

Are you are wanting a single cell A1:A1 and a range of columns?  If yes then you will have further problems, both SELECT statements should return the same number of columns - which you don't have.
is they a way around it?
ASKER CERTIFIED SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok