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

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

Importing xls file, looking up shortcodes and then exporting to xls or cvs with columns in certain o

Posted By:      Posted Date: October 08, 2010    Points: 0   Category :ASP.Net


I am looking to import an xls file and then for 2 columns in this document I need to look up the shortcodes which are in another document eg London Heathrow is LHR in the lookup table. I need to do that for data in 2 columns and then export the data into xls format or can be cvs, but the column order in the outputted document need to be different to when they were imported how can I go about doing this?

Hope this makes sense

Thanks in advance


View Complete Post

More Related Resource Links

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




importing .csv file into MS SQL 2005

Greetings :-) I need to import data from my excel .csv file into a database in MS SQL server 2005. Can someone show me the steps? Is there a way to do this with a wizard since I'm not a DBA? Thanks in advance for your assistance   .

Importing a .txt file

Hi, I'm currently struggling with this problem: - I currently using Microsoft Visual Studio 2008 and the extension for my database in my web application is .mdf - My supervisor gave me two files, student.txt and staff.txt - I needed to import both of these files into my web application and I am new to this language and I have no idea on how to do this. Please provide some codes sample regarding this problem. Thank you.

Adaptive Flat File Destination Columns

I'm sure it must possible to adjust the columns for a flat file output based on the input, but so far have not found the right tools. I have to convert quite a few tables to flat files, where the input columns have not been safely specified yet. The tables are still being changed.   e.g. I have a table with columns A,B,C. Columns D,E,F... could be added later, maybe B or C will even be removed or renamed. I have to transfer all data from the table except for column A to a flat file.   I find it annoying having to adjust the columns manually every time. Would be extreemly thankful for your advice.

How to read an Excel file and show data from 2 columns

Hi,I have an Excel file called Products.xls .I have Columns A and B, with the titles NAME and QUANTITY.The name of the sheet is SHEET1.The file has about 40 lines.How do i show these data on a Gridview or Listview ?Thank you.

Strange characters exporting a blob file using bcp

Hi, I am trying to insert and export a binary file into SQL 2005. I need that word, pdf and txt file work. I can't use file path and this is the only choice I have. I am using this way: 1) Create a file test.txt and write "hello" into it. 2) CREATE TABLE [dbo].[myfiles]( [file] [varbinary](max) NOT NULL) ON Primary 3) INSERT INTO [myfiles]([file]) SELECT BulkColumn FROM OPENROWSET ( BULK 'C:\test.txt', SINGLE_BLOB) AS BinFile 4) declare @SQLcommand nvarchar(4000) set @SQLcommand =       'bcp "SELECT top 1 [file] FROM myfiles" queryout "c: \temp\test.txt" -T -n -SMYPC' exec xp_cmdshell @SQLcommand When I open file c:\temp\test.txt I can see strange characters before "hello" Trying to insert a word file says me that the file is corrupted. How can I solve this ? Thanks.

importing excel file into sql server 2005 in asp .net using c#

Dear friends i want to import excel file into sql server 2005 in asp .net using c# can anyone help me? rgds,RK

Getting error while importing xml file under my SSP

Using Application Definition Designer,I connected to a database and added a table. Then i exported the xml file from Application Definition Designer to my local folder. After that, in Central Administration under my SSP, i tried to import the xml file from my local folder.During the importing process,i got the following error. Application definition was successfully imported. 2 warning(s) found. Consider fixing the warnings and updating the application definition. Warnings  No method instance of type SpecificFinder defined for for application 'BDC ', entity 'GetInfoDetails'. Profile page creation skipped.  No method instance of type SpecificFinder defined for for application 'BDC ', entity 'GetQualification'. Profile page creation skipped.   Please help me to find a solution for this asap.  

Arrange columns from nested table in a dataset that writes into XML file



I have 3 related data tables. I added these tables into a dataset and finally write it to a XML file. But the related columns from table 2 and 3 is appended after all the columns from table 1. How can I have something like:
in a dataset that I write to XML instead of

Importing XML file getting an error


I'm trying to import an XML file using XML Source but I'm getting an error when I try to generate XSD.

"Unable to infer the XSD from the XML file. The XML contains multiple namespaces."

I have been researching on using an XML Task  instead but I couldn't figure out how to be able to strip the

xmlns:dt="urn:schemas-microsoft-com:datatypes" & dt:dt="xxxxxx"

from the XML source. Without it, I have no issues. Any ideas would be appreciated. Thanks!


  <?xml version="1.0" encoding="ISO-8859-1" ?>

SharePoint 2010 Load Testing Kit - a csv file contain at least three columns


I'm following this page.
Load Testing Kit (SharePoint Foundation 2010)


I've created a csv file in the format:

I run this command
C:\Program Files\Microsoft\SharePoint 2010 Administration Toolkit\Load Testing K
it>ltk.exe -source C:\inetpub\logs\LogFiles -output visualstudiosolution -userli
st c:\temp\users.csv

And received this message

[10/1/2010 3:04:50 PM] [Error] :
Exception Type [System.InvalidOperationException]: Message[The user list should
be a csv file contain at least three columns, which are username, password and p
stack trace:    at Microsoft.Office.Server.AdministrationToolkit.LoadTestingKit.
   at Microsoft.Office.Server.AdministrationToolkit.LoadTestingKit.Engine.Framew
orkEngine..ctor(CommandLineArguments arguments)
   at Microsoft.Office.Server.AdministrationToolkit.LoadTestingKit.Common.Comman
dLineUtility.HandleCommandLine(String[] args)
   at Microsoft.Office.Server.AdministrationToolkit.LoadTestingKit.Program.Main(
String[] args)\n

Here's a snippet from loadtestkingkit.log


Importing large dat file into sql server database

I have a dat file which consists of thousands of columns which i have to insert into a database. I have to insert the data into multiple datatables. I am looking for the best possible way to do that. I have looked into bulk insert also but is there a better way? something like normalizing the data in the data access layer and inserting into the tables or creating ssis packages? Any help is highly appreciated.

Issues while exporting & importing. Changes don't reflect after import.


Hi, we have a SharePoint 2010 site collection.

We have made changes to one of the subsite where the welcome page is pointing to a different file and this change reflects fine in the original site.

Now I export this site collection and import into a new site collection. After importing into new site collection, I see that the welcome page of the subsite still points to the old file and not to the new file, eventhough the new file is present under site pages. Also the changes are checked in to the original site before exporting.

stsadm.exe -o export -url http://server/sites/site1 -filename "D:\Sharepoint Deployment\Site1.cab" -includeusersecurity -versions 4 -overwrite 

stsadm.exe -o createsite -url http://chnmct117100d/sites/site2 -ownerlogin domain\user -owneremail user@domain.com

stsadm.exe -o import -url http://server/sites/site2 -filename "D:\Sharepoint Deployment\Site1.cab" -includeusersecurity 

Has anybody faced this issue before? Any idea of a possible solution ?


Kannan Balasubramanian


Importing text file datetime string into SQL datetime column



Using SSIS 2008 R2 to import text files.  Two of the fields contained are datetime values in the format "2010-09-24 18:58:29.220".  Ultimately both the date and time need to be extracted with precision to the second, at the very least.  When I import the text file, however, the time value seems to get truncated before the date value's being imported, for an imported result of "2010-09-24 00:00:00.000".

Is my problem with the Flat File Connection Manager?  Regardless of which DataType choice I use for the properties of my datetime values--I've tried every form of date datatype--the imported column values contain the correct date but the wrong time value.

Is there an easy way to import a text file datetime value straight into a datetime column?  Is it possible, or am I trying to shortcut what should be a proper process?


Importing text file datetime string into SQL datetime column



I'm importing a text file into SQL Server 2008 R2 using SSIS 2008 R2.  Included in the text file is a datetime column with values like "2010-09-24 18:58:29.220".  However, when I import the file, the stored value has the time portion replaced with what I assume is the default, such as "2010-09-24 00:00:00.000".

Is there an easy way to import a datetime value from a text file into a datetime column in my table?  I've tried every form of date datatype in the Flat File Connection Manager, but I still keep getting the correct date portion with the incorrect time portion.

Am I trying to do something that wasn't intended?  I see Todd McDermid's page (http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html) and am starting to wonder if perhaps I'm trying to shortcut something that I shouldn't.


Problem with List Columns in Foundation 2010 when Importing from Excel


This is a pretty detailed issue. I will give you all the steps I am doing and hopefully there is a solution.

I recently updated our intranet site from WSS 3.0 to SharePoint Foundation. In WSS 3.0 I added a few of the application templates to use, including Asset Tracking. When I moved to Foundation I decided not to use those (and not sure if I can anyway) and exported the Asset Tracking data to Excel, then imported it to Sharepoint Foundation as a list.

Here is the issue I am having: when I imported from Excel all but one of the columns was created as "multiple lines of text". This means I cannot sort by these columns. I thought I could create similar columns as single lines of text, export back to Excel, copy the data, and import back. This changes the columns again to multiple lines. I even tried just typing in the data as a test and same problem. As long as there is no data, the columns will remain as single line of text; but as soon as I put data in I get this issue.

I can cofirm that these columns do not have more text than should be available in a single line. Any ideas of what I can do to make this work? I am at my wits end on this.

Thanks for any and all help.

Urgent:Create/Update Winforms grid columns and rows with config file values


I have legacy winforms datagrid , I have to add/replace columns with new values, My new column names and row values are changes as per new requirements , so I am looking to update datagrid form any config file , so in future for any new requirements , If I update my config file , it hast update existing datagrid.

Please suggest me the code; I am trying from 4 days and its urgent now.



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