• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • 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
 
AndyAinscowFreelance programmer / ConsultantCommented:
Does this work?

cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H] UNION SELECT * FROM [" + getExcelSheetName + "A1:A1]";
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
ps.
This looks odd, is it correct (no number after the H) ?   [" + getExcelSheetName + "A2:H]
0
 
JCWEBHOSTAuthor Commented:
h is a coloum
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
AndyAinscowFreelance programmer / ConsultantCommented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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