How to read excel file in document library and store excel content in sql table

Advertisement

Hello,
Can anyone help me how to read the excel file present in document library and store the content inside excel into sql table?
Please let me know the ways to acheive this. Feel free to give your suggestions.
Thanks,
Cool Developer

Advertisement

Hi!
this code i have written becuase i donot find any soltions on net for this , u can try with this . :)
System.Data.OleDb.
OleDbConnection ExcelConnection = null;
FileMode fileMode;
string filePath = ConfigurationManager.AppSettings["TempLoaction"] + "\\" + fileName;
using (SPSite _site = new SPSite(SPContext.Current.Web.Url))
using (SPWeb _web = _site.OpenWeb())
string docLibrary = ConfigurationManager.AppSettings["DocumentLibrary"];
SPFile _file = _web.GetFile("/" + docLibrary + "/" + fileName);
fileMode =
FileMode.Create;
byte[] byteArray = _file.OpenBinary();
MemoryStream dataStream = new MemoryStream(byteArray);
Stream stream = dataStream;
using (FileStream fs = File.Open(filePath, fileMode))
byte[] buffer = new byte[4096];
int bytesRead;
while ((bytesRead = stream.Read(buffer, 0, buffer.Length)) != 0)
fs.Write(buffer, 0, bytesRead);
fs.Close();
//Create the Connection String
try
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source ='"
+ filePath + "'; Extended Properties=Excel 5.0";
//Create the connection
ExcelConnection =
new System.Data.OleDb.OleDbConnection(ConnectionString);
//create a string for the query
string ExcelQuery;
//Sheet1 is the sheet name
//create the query:
//read columns from the Excel file
ExcelQuery =
"Select * from [Sheet1$]"; // from Sheet1";
//use "Select * ... " to select the entire sheet
//create the command
System.Data.OleDb.
OleDbCommand ExcelCommand = new System.Data.OleDb.OleDbCommand(ExcelQuery, ExcelConnection);
//Open the connection
ExcelConnection.Open();
//Create a reader
System.Data.OleDb.
OleDbDataReader ExcelReader;
ExcelReader = ExcelCommand.ExecuteReader();
//For each row after the first
while (ExcelReader.Read())
thanks,
kshitij

October 11, 2015

How to read excel file in document library and store excel content in sql table

Hello, Can anyone help me how to read the excel file present in document library and store the content inside excel into sql table? Please let me know the ways to acheive this. Feel free to give your suggestions. Thanks, Cool DeveloperHi! this code i

Create excel file in document library from the data in datatable

Hi , I want to generate files dynamically and have then saved to my documetn library instead of  some location on the  harddisk. I want to generate and excel file  which will read data from the sharepoint list. Form that data excel will be genereated

How to read a file which has header and without header

Hi, I have a file to file scenario wherein there are txt files in the source system, some of which has header and some without header. How does the sender adapter dynamically identify a file with header and without header. I knew that File content Co

How to open and read many files from a directory and store contents in 2D array?

I want to make a VI that opens and reads the data from various files contained in a directory (200 files each with 2 columns) and store these in a single 2D array. For file number 1 I want to store the data from both columns in the 2D array, but for

How to get the value from a label and store it in an sql database?

I have tried all kinds of different ways and I always get the error that it is not a member of string or whatever data type I try. Try scoringlbl.Text.Validate() scoreinglbl.Text.HighScoresBindingSource.EndEdit() scoringlbl.Text.HighScoresTableAdapte

How to read multi files?

Hi !! Can anyone tell me the syntax of reading mulit txt files? Thanks. I only know how to read single file. try //Search file System.out.println(" "); System.out.println("Search file :"); File file = null; String fileloc = "c:\\j

How to read HTML files using UTL_FILE

Hello Friends, How to read HTML files using UTL_FILE package ? According to Oracle documentation UTL_FILE can read or write OS Text Files. Thanx in advance.. AdiHI Hareesh, i have gone through that blog. i tried it...but i am getting mapping error  n

How to read PDF files on your iphone in book format

Hey everyone, I just posted a tutorial on how to read pdf files like ebooks on Stanza. Let me know if you have any questions http://budurl.com/u4tz That used to drive me crazy until I figured it out. Enjoyan easier way would be to wait until iOS 4 an

How to read the files in different directories

how to read the files in different directories here directory names will change dynamically under root node. let me say rootnode---->subnode1 >subnode2 >subnode3 >subnode n how to specify these in file adapter.Hi look at this thread your probl

Step by Step"How JSP read text file :

Hi , Any one know or have a good site to show step by step how JSP read text file. TQ.There is no difference Between reading a text file from JSP and reading a text file from Java. Just follow the same steps for JSP also.Read other 2 answers

How to read flat file and convert to xml throught OSB

Hi , Can somebody help how to read flat file and convert to xml in OSB. appreciate ur help. Thanks & Regards , Siva K Diviif you're using the oepe with osb plugin (will be installed when you install the osb locally) and then in your osb project > r

Excel file(.xls) as attachment and Body(Signature) via Receiver Mail Adapter

Hi PI Experts, I have a requirement File(.txt) - PI(7.4) - mail (.xls attachment and signature in the body). Can anyone pls help me with the best approach to send excel file as mail attachment and signature in the body. Thanks, SriHi You can try this

Detect what the default edit form is for a document library and list using jquery

Hi, So what I have done is with some jquery made it that when a files are dragged and dropped into a document library and there is a required field such as "Metadata" the EditForm.aspx opens up for the user to fill in the required column. I am b

I am a long time iTunes user but a new MacBook owner! :) I keep the majority of my music files on an external hard drive.   I'm trying to view all those files on my MacBook through iTunes.   With windows you would go to : File/Add To Library, and iTunes w

I am a long time iTunes user but a new MacBook owner! I keep the majority of my music files on an external hard drive.   I'm trying to view all those files on my MacBook through iTunes.   With windows you would go to : File/Add To Library, and iTunes

Information Screen Library and Store SDK or Reading SDK within a folio

There are conflicting materials in regards to new r30 Information Screen functionality: adobeDPS.dialogService.displayCustomWebview(url) From Adobe TV's Information Screen & Scheduled Text Notifications by Colin Fleming, it states: Similar to digital

Hi, i purchased a 2 dvd digital set. i cant just download it straight to my ipad. they said i have to down load it to my i tunes, then can transfer to i pad. im not seeing how to click the files from my email, and get them into the i tunes acct... ugh.

hi, i purchased a 2 dvd digital set. i cant just download it straight to my ipad. they said i have to down load it to my i tunes, then can transfer to i pad. im not seeing how to click the files from my email, and get them into the i tunes acct... ug

Reading a file line by line and executing the code

I need to read a file line by line and execute them simultaneously. I use BufferedReader for reading , but for executing i tried a lot , but could not find a solution. Kindly help and try to brief me.If you're hoping to execute lines of java source c

Edit CSV files present in document library using Microsoft Excel

Hi all I have a document library with CSV files uploaded. I have used the below mapping in DOCICON.xml present in \14\TEMPLATE\XML folder. <Mapping Key="csv" Value="csv16.gif" EditText="Microsoft Excel" OpenControl="S

Can't open Acrobat pdf files in document library - SBS 2011

I've uploaded some Acrobat PDF files to a document library in the companyweb (SharePoint 2010 Foundation website) on SBS 2011. But if I click on one of them in the standard view, acrobat opens and displays a message "The URL you have provided could n

How to read DefaultTrace files, What are other useful trace files

Hi, I was trying to debug some issue using Default Trace file and it is painful. Can anyone point out some tips and tricks to do debugging. And how to read Default Trace file. Also what are the other useful trace files available? Please point out. Th