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.Conne ctionStrin gs["connec tionstring "].Connect ionString;
string connectionOleDbString = "";
DataTable dt = new DataTable();
if (AsyncFileUpload.HasFile)
{
string fileName = Path.GetFileName(AsyncFile Upload.Pos tedFile.Fi leName);
string fileExtension = Path.GetExtension(AsyncFil eUpload.Po stedFile.F ileName);
string fileLocation = Server.MapPath("~/temp-fil es/" + fileName);
AsyncFileUpload.SaveAs(fil eLocation) ;
if (fileExtension == ".xls")
{
connectionOleDbString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionOleDbString = "Provider=Microsoft.ACE.OL EDB.12.0;D ata Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=2\"";
}
OleDbConnection con = new OleDbConnection(connection OleDbStrin g);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Te xt;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(Ol eDbSchemaG uid.Tables , null);
string getExcelSheetName = dtExcelSheetName.Rows[0][" Table_Name "].ToStrin g().Replac e("'", "");
cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H]; SELECT * FROM [" + getExcelSheetName + "A1:A1]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dt);
con.Close();
SqlConnection objConnection = new SqlConnection(connectionSt ring);
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
objConnection.Open();
SqlBulkCopy objBlkCp = new SqlBulkCopy(objConnection) ;
objBlkCp.ColumnMappings.Ad d("city", "city");
objBlkCp.ColumnMappings.Ad d("Deal End", "deal_end");
objBlkCp.ColumnMappings.Ad d("merchan t_id", "merchant_id");
objBlkCp.ColumnMappings.Ad d("merchan t_name", "merchant_name");
objBlkCp.ColumnMappings.Ad d("deal_id ", "deal_id");
objBlkCp.ColumnMappings.Ad d("voucher _code", "voucher_code");
objBlkCp.ColumnMappings.Ad d("u_first _name", "u_first_name");
objBlkCp.ColumnMappings.Ad d("u_last_ name", "u_last_name");
objBlkCp.DestinationTableN ame = "groupon";
objBlkCp.WriteToServer(dt) ;
objConnection.Close();
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.Conne
string connectionOleDbString = "";
DataTable dt = new DataTable();
if (AsyncFileUpload.HasFile)
{
string fileName = Path.GetFileName(AsyncFile
string fileExtension = Path.GetExtension(AsyncFil
string fileLocation = Server.MapPath("~/temp-fil
AsyncFileUpload.SaveAs(fil
if (fileExtension == ".xls")
{
connectionOleDbString = "Provider=Microsoft.Jet.OL
}
else if (fileExtension == ".xlsx")
{
connectionOleDbString = "Provider=Microsoft.ACE.OL
}
OleDbConnection con = new OleDbConnection(connection
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Te
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(Ol
string getExcelSheetName = dtExcelSheetName.Rows[0]["
cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H]; SELECT * FROM [" + getExcelSheetName + "A1:A1]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dt);
con.Close();
SqlConnection objConnection = new SqlConnection(connectionSt
if (objConnection.State == ConnectionState.Open)
{
objConnection.Close();
}
objConnection.Open();
SqlBulkCopy objBlkCp = new SqlBulkCopy(objConnection)
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.ColumnMappings.Ad
objBlkCp.DestinationTableN
objBlkCp.WriteToServer(dt)
objConnection.Close();
ps.
This looks odd, is it correct (no number after the H) ? [" + getExcelSheetName + "A2:H]
This looks odd, is it correct (no number after the H) ? [" + getExcelSheetName + "A2:H]
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.
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.
ASKER
is they a way around it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok
cmd.CommandText = "SELECT *FROM [" + getExcelSheetName + "A2:H] UNION SELECT * FROM [" + getExcelSheetName + "A1:A1]";