.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Excel/SQL - Column Name Length

Posted By:      Posted Date: September 13, 2010    Points: 0   Category :Sql Server
Hi, I believe while importing/exporting data from Excel into SQL, the column name should not be more than 64 characters long. However, I have a situation where my field name could be more than 64 characters. Can someone suggest how to increase the column name length? Thank you for your quick response. Best Regards, Harikrishnan R

View Complete Post

More Related Resource Links

In export, i want to disable column in excel sheet

Hello, I am doing Import/Export. While export i want to disable some column in excel sheet, so during upload or import same primary key I can use, instead of user modify such column. Regards, Sandeep    

Trouble importing strange column format report from Excel 2007 to SQL 08

Hi, I'm not sure whether this belongs in this section or the SSIS one so hopefully I've got it right!  Hoping someone will be able to help with a problem I'm having importing a report with header and detail rows from our antiquated POS system into SQL 2008 tables.  The reports export in Excel 2007 format and for each header row there can be one or more detail rows starting from column B.  As such I've tried several things such as openrowset, an Access 12.0 OLE DB source in SSIS and even COM automation of Excel to try and write a conditional split which will hold the header details in variables then write them to rows alongside the detail rows.  I've pasted a small sample of the data here as I wasn't able to attach it: 01/07/2010 @ 11:18 Page: 1 RECEIVING: Voucher Journal Sort: VC|Str|Vou Date|Vou #|Document SID Filter: Voucher Date: 01/06/2010@12:00a..30/06/2010@11:59p Include Item detail: DCS|Item#|Desc1|Attr|Material|Size|Qty VC Str Vou Date Vou # Qty ABC 001 15/06/2010 12345 38 E NN 66 200148 XXXXXXXX RED SHAD E NN 66 200149 YYYYYYYYY BLACK GO E PP 60 200154 ZZZZZZZZZ BLACK CDE 002 16/06/2010 13839 8 F SA 35 217500 XXXXXXXX Natural F SA FL 218674 YYYYYYYYY Chalk F SA WE 221462 ZZZZZZZZZ White FGH 001 21/06/2010 13905 3 F SH 85 126260 XXXXXXXX Navy IJK 001 23/06/2010 13914 3 E AA 61 250005 YYYYYY

merging every 3 column in excel using macros

I would like to merge every 3 columns and so forth in excel. It will be as follow: Column A,B,C as one column continued with  D,E,F, and G,H,I, and so forth until say KU,KV,KW. please let me know how to create the macros to select and then merge it. Thank you

Dynamic Column in Excel Source

Hi,I am having Excel Source Which needs to be imported into Sql Server Table using SSIS.In the Excel Source I dont have Month and Year Column.But in Table I have Month and year column and both the columns are Primary Key columns.So i am not able to Import data from Excel to Table.So is there any possiblities to add Columns Dynamically in Excel source inorder to  get the Year and Month

read excel column value

Hi All,             i am uploading excel file data to database.Excel column value is combination of text and time,i need to extract only time.Each column will have different value. One column value is   _immediate 17:00 - 9:00 another column value is EMAIL 09:30 - 18:00,another column value is CKA 09:30 - 18:30 ans so on. i need to extract only time and store it 2 different variables like this. string Intime - 17:00 string OutTime - 9:00   Please send me code to acheive this task.            

How to have dynamic column names in an Excel Destination?

I have a situation where both the column names in the source (SQL) and destination (Excel) are being created dynamically. The number of columns, their positions, and datatypes remain the same. How do I change the OLE DB Source and Excel Destination meta-data to handle these changes at run-time?

Saving Document With Column items applied via excel macro

I have an excel macro that pulls data down from columns within worksheets that will take an excel spreadsheet and save it as a PDF file into seperate folders based off criteria set in the files. Getting the files into the proper directory is not the problem i'm having. When the script executes it saves the documents directly to our sharepoint site, but the custom columns A B C D etc are all blank and some are 'required' items which sets the document as checked out until i go into the list and enter the required data. Is there anyway to make this macro export as PDF and set the required data for me in the sharepoint column so it checks the documents in for me? I'm figuring this is a simple set of meta datatags but I don't know the syntax for them and I didn't find anything like this on the forum anywhere else. ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "http://DirectoryStructureHere/corporate%20BoFi/pdfs/" & NewFileName & ".pdf" _ , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _ :=False, OpenAfterPublish:=False 'Exports the print area as a PDF document named the new filename "Modified: to sharepoint"

ASP.NET-Gridview - Export to excel- column format

Hi All, I am exporting gridview data into excel sheet. I have a column  which is a code for which data is like 012,013 etc. But when this data is being exported , in the excel sheet I can see the data as 12,13,14. but actually it should be 012.... Is there any way to specify the format of such columns in my datatable so that they are in the same manner as in the database? Please provide some information on this.Thanks in advance.

Uploading Excel 2007 table into WSS 3.0 - How does SharePoint decide the column that is used to edit

Hi, In Excel 2007, there's an option to upload (export) an Excel table into SharePoint.  The range of cells have to be identified as a table.  In the Table ribbon, there's an Export button.  When I upload the Excel 2007 table, SharePoint (WSS 3.0) decides which column in the table will be linked to the properties dialog box for each line in the table.  It seems to be a sporadic decision on the part of SharePoint. Does anyone know how WSS 3.0 decides which column will be linked to properties?  And, is there any way to control this when uploading the file? Thanks!Thanks! Patti N.

Convert excel rows into column


 this is the data into excel sheet. i have excel sheet like following way. i want to upload and when it download so it should be following way.

REG_NO   2222    
Name   xyz
ADDRESS xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx      
PHONE   3333333333      
Description asasdfasfasfasdfasdf      

Convert excel rows wise data into column wise


public static string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

               OleDbDataAdapter adapter = new OleDbDataAdapter();

            OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]");

            OleDbConnection connection = new OleDbConnection(connectionString);

            selectCommand.Connection = connection;

            adapter.SelectCommand = selectCommand;

            DataSet cities = new DataSet();


Define Column Length dynamically

Does anyone know how to dynamically change flat file's column length in SSIS?(e.g from size of 10 to 0)

Analysis Services 2008 e Excel pivot table: different filters for each column


I have to create a report in excel 2010 based on a  analysis services 2008 cube.

I created a pivot table that contains four columns containing the elements of a hierarchy. My problem is that each column should be filtered with different values of the same field.

For example: given a hierarchy that describes me a set of products, I have to filter the product for customer  1 and 3, Product B for  customer 2 and the product C for the customer 3.

I think this thing is not possible in Excel (right?)

So I decided to use a mdx script to filter each product 

I tried so (for example, product A):
scope (descendants([Products].[products].[liv0].&[A], 1, leaves));
[Measures].Quantity = sum({[Customer].[Code].&[01], [Customer].[Code].&[03]}, [Measures].quantity);
end scope;

I still get an error of infinite recursion. How can I solve my problem? Is there a better system than what I am following?

SSIS package to import excel 2007 with different column numbers



I have an already developed SSIS 2005 package which can import maximum of 50 columns of *.xlsx file. The file format is same except with the column numbers. I have many components in SSIS package (e.g. unpivot data, derrived column etc) which are developed for 50 columns. The package works fine for importing 50 columns. But sometimes it fails for excel sheet having 12 columns (columns < 50) it gives error for the source columns cannot be found. Surprisingly, if the contents are copied to another file it may work but not surely. Please let me know if I can do anything in existing package.

Thanks in advance,

Excel column issue reading


Hi Experts,

We have excel with one column which is actually Custom column and type is h:mm:ss (when I right click and seleting format cells).

So when I'm selecting any of the cell into the excel file, it has values in the for of h:mm:ss but when I look above the value "fx" it is different some thing like date + time + AM/PM ( for the same seleted cell ) ---- which means two different values for the same selected cell.

Now we need to load this excel file using SSIS to our DB table, I tried but it is loading upper value ( date + time + AM/PM ) and not the value which the selected cell is having h:mm:ss.

Suppose for an example I have selected on cell whose value is 41:48:57 but when I see above near by to fx it is having 1/1/1900 4:14:40 AM and SSIS is loading this value and not the actual value ( 41:48:57 "h:mm:ss" format).

So how to tell SSIS to load value in the form of h:mm:ss and not the date+time+AM/PM value? Tried my best to explain you guys.

Please let me know if you have any specific quesiton.



SSIS import - Missing 1st column from Excel


Hi ,

I have gone through some of the materials posted on this topic. but didnot work out for me. I am trying to import the excel file using ssis. I have got the header details , I have to skip the header  while importing. I am using following sql commad: to import . I am able to skip the header row, but missing the 1st coumn from excel.I am not able to add the F1 column in the query . Can any know why it is not possible, because of this I am miisng the 1st column to import.

 SELECT F2, F3,F4, F5
from [Sheet1$]
where (f2is not null) and (f2<>'status')

Please help me out




Extract XML data fro Excel Column

  • I have the following xml data in excel column, how do i extract data from it to a table using ssis.

    Column name XT_BODY

    <SendData><Guid>cPKNWB6XUkyIcLctQdsNkg==</Guid><Dbg>3308:1.2.3960.15716:frmDPSReportProduction:RWC_PromptControl1:Qty:OnValid:6</Dbg><_IP></_IP><_MAC>00:26:2D:26:6C:11</_MAC><_DN>KYBWS421</_DN><MSGID>t_DPS_Prod_Qty</MSGID><SEQN>000001</SEQN><CONTRACT>FRK</CONTRACT><PROD_LINE>MA11</PROD_LINE><QTY>1200</QTY><DT>20101116114300</DT><STAMP>40498.4881944444</STAMP><DEVID>WS421 </DEVID><SOURCEID>RPCC</SOURCEID></SendData>

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend