• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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();
0
JCWEBHOST
Asked:
JCWEBHOST
  • 4
  • 3
1 Solution
 
AndyAinscowCommented:
Does this work?

cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H] UNION SELECT * FROM [" + getExcelSheetName + "A1:A1]";
0
 
AndyAinscowCommented:
ps.
This looks odd, is it correct (no number after the H) ?   [" + getExcelSheetName + "A2:H]
0
 
JCWEBHOSTAuthor Commented:
h is a coloum
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
AndyAinscowCommented:
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.
0
 
JCWEBHOSTAuthor Commented:
is they a way around it?
0
 
AndyAinscowCommented:
Thinking about your question.

Why don't you want to get the complete sheet, then remove (I assume you don't want the first row apart from the first cell A1)  the unwanted values in B1, C1 ....  eg. overwrite with nulls (empty strings or whatever)
0
 
JCWEBHOSTAuthor Commented:
ok
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now