FAQ Database Discussion Community


Importing Excel into SQL Server

sql,sql-server,import-from-excel
I want to update a table using data from an excel sheet. My excel file have just two columns, one with a uniqueidentifier and the other with a reference. I tried to use the OPENROWSET command to update my table, but the database have a security property that doesn't allows...

Faster way to read Excel files to pandas dataframe

python,pandas,import-from-excel
I have a 14MB Excel file with five worksheets that I'm reading into a Pandas dataframe, and although the code below works, it takes 9 minutes! Does anyone have suggestions for speeding it up? import pandas as pd def OTT_read(xl,site_name): df = pd.read_excel(xl.io,site_name,skiprows=2,parse_dates=0,index_col=0, usecols=[0,1,2],header=None, names=['date_time','%s_depth'%site_name,'%s_temp'%site_name]) return df def make_OTT_df(FILEDIR,OTT_FILE): xl...

SSIS from Excel to SQL Server : DataType length

ssis,import-from-excel
I 've got an SSIS Package (SQL Server 2008). I have an Excel source file (XLS 97-2003) that I want to import first to a SQL table storing everything as string (numbers and dates are stored as they rae written for instance). Then, I take data from this table to...

Reading Excel file into Tableau skipping first few rows

tableau,import-from-excel
I have a Tableau chart reading from a shared Excel file. Now the Excel file owner has added a few header lines into the file that causes Tableau to not recognise the column headers. I have no control on the Excel (i.e. cannot change the content), and my chart is...

parse part of xlsx file using poi xssf and sax parser

java,parsing,apache-poi,saxparser,import-from-excel
I am trying to read 10 rows out of 1 million records in an excel sheet (.xlsx) : FileInputStream myFile = new FileInputStream(file); OPCPackage pkg = OPCPackage.open(myFile); XSSFReader reader = new XSSFReader(pkg); MySheetContentHandler sheetHandler = new MySheetContentHandler(); StylesTable styles = reader.getStylesTable(); ReadOnlySharedStringsTable sharedStrings = new ReadOnlySharedStringsTable(pkg); ContentHandler handler = new...

Import data to SQL Server table from Excel

sql-server,import-from-excel
I am trying to import data from excel file to SQL Server table. I am getting this error "External table is not in the expected format (Microsoft Office Access Database Engine)" I am using Microsoft SQL Server 2012 and Excel 2007. I tried Microsoft SQL Server Import and Export wizard....

I want to import excel file in php/mysql with some images too. Does anyone has solution?

php,mysql,image,import-from-excel
I want to import excel file in php/mysql with some images too. Does anyone has solution? If Yes, Then plz reply with required software insatallation too.

LinqToExcel returns null

c#,linq,import-from-excel,linq-to-excel
I have an excel sheet of xls format, I am using LinqToExcel to read it and then import it to my DB. This sheet consist of about 3K row and only 6 cols. I am using .addmapping to map my class properties to the column names The problem i have...

Type conversion failure in Access 2013

excel,csv,ms-access,import-from-excel
When importing data from a text file (csv) into MS Access, I get an error "Type conversion failure" for 1 field. The field has data with date format "yyyy-mm-dd hh:nn:ss" and Access simply refuses to recognise it and places #Num! or simply blank data. The csv file is huge with...

Reading Excel file using node.js

javascript,angularjs,node.js,import-from-excel
Okay so i am using the FileUploader module to upload my file from angular to my REST API: var uploader = $scope.uploader = new FileUploader({ url: api.getUrl('uploadCompetence',null) }); This is sent to the following POST function: router.route('/api/uploadCompetence') .post(function (req, res) { // This is where i want to read the...

How to open an excel file from VSTO plugin and get VSTO workbook?

c#,vsto,import-from-excel
I already know how to open file and get Workbook from Microsoft.Office.Interop.Excel namespace. But is there a way to get Workbook from Microsoft.Office.Tools.Excel namespace? Casting between the two does not work. I know this sound simple, but I'm stuck - all answers on SO point to Interop solutions.

Read excel file with blanks to Junit Tests

java,junit,parameterized,import-from-excel
I need help to alter the class I'm using to read an excel for unit Parameterized Test class. Your help is much appreciated. The below class does not read blank cell values. It skips the blank cells, so the number of arguments passed to the constructor is wrong. I need...

How to read empty cells in PHPExcel without skipping values?

php,import-from-excel
I need your help to solve one problem. I tried to find answer, but I found none. I'm using PHPExcel 1.8.0 to read data from excel-file. And I'm using ReadFilter to read specific columns. Main PHP Code. $price = UPLOAD_DIR . $price; $file_type = PHPExcel_IOFactory::identify($price); $filter = new ShipperReadFilter(); $filter->getShipperSettings($shipper);...

Delete null values from c# datatable without loop

c#,datatable,import-from-excel
I'm populating a DataTable object from an Excel worksheet. I'm not counting on the user entering data correctly, and I'd like to delete rows that have a null value in column A. I've searched around quite a bit, and it looks like everyone is doing this with a for loop....

converting a datetime object

python,datetime,import-from-excel
with the help of @JRichardSnape and others I was able to get most of the stuff sorted in my little script. I have an excel sheet which contains date. it the following I convert them to datetime objects: def excel_time_to_string(xltimeinput): try: retVal = xlrd.xldate.xldate_as_datetime(xltimeinput, wb.datemode) except ValueError: print('You passed in...

Aginity for Netezza Create Temp Table from external xlsx file using ODBC

sql,odbc,temp-tables,netezza,import-from-excel
In Aginity Workbench for Netezza, I am trying to create a temp table from a .XLSX file containing 13 columns but only need columns 1 and 5. I can export to tab delimited .TXT with only the two columns needed and it works fine, but I would like to avoid...

How to download an .xlsx file in R and load the data into a dataframe?

r,import-from-excel,xlconnect
I'm trying to download an .xlsx file from the eia and getting the following error. The error is: "Error: ZipException (Java): invalid entry size (expected 2385 but got 2390 bytes)" I have tried the following code: library(XLConnect) tmp = tempfile(fileext = ".xlsx") download.file(url = "http://www.eia.gov/petroleum/drilling/xls/dpr-data.xlsx", destfile = tmp) readWorksheetFromFile(file =...

PHPExcel cannot load spreadsheet with formula

php,composer-php,phpexcel,import-from-excel
When trying to create a reader for a file, I get some strange errors. I was using the dev-master branch, then switched to 1.8.1 but that didn't seem to make any difference therefore I'm thinking it's the spreadsheet that's the problem. There are quite a lot of formulas used in...

Encoding error when opening an Excel file with xlrd

python,encoding,xlrd,import-from-excel
I am trying to open an Excel file (.xls) using xlrd. This is a summary of the code I am using: import xlrd workbook = xlrd.open_workbook('thefile.xls') This works for most files, but fails for files I get from a specific organization. The error I get when I try to open...

Is openpyxl iter_rows() skipping last row?

python,openpyxl,import-from-excel
I have difficulties using openpyx iter_rows. The source excel has rows in the first column (A) : header 1st data row ... 32nd data row The code: start, stop = 0, 1000 for row_index, row in enumerate(source_ws.iter_rows()): if start < row_index < stop: # something meaning here does not visit...

How can you specify from which column position to start the data import from excel to mysql

php,mysql,excel,import-from-excel
Hi guys i have an php excel issue. The problem is that i have an excel file that has several rows and columns, the deal is that i want to import only from specific row position for example A11 or C12. How can i tell this in php? Here is...

Best way to present data from a big excel file on a website

java,javascript,import-from-excel
I have a big excel file and I want to show the data on my website. I have made a matlab and a Java program(script) that can extract the information I want from the excel file. How do I get the data to the website? Should I have the matlab...

How to get information from an xlsx file in Python?

python,import-from-excel
I have to create a mailing list of people belonging to a certain institution. The information is only available in .xlsx file. The columns of xlsx are as follows: institution, DOB, Program, ..., EmailID. How do I do this, instead of reading each entry myself and then typing the email...

2 Dimensional Excel Table to CSV for SQL Insert

mysql,sql,database,csv,import-from-excel
This is probably a dumb question but I'm new to database design and am working to move a convoluted excel spreadsheet into a SQL database. Here is an excerpt of what I have: And here is what I want to turn that into, creating records with the X,Y coordinates of...

PHPExcel Get cells ignoring related sheets

php,phpexcel,import-from-excel
I have a .xlsx file with two sheets and I want to extract to PHP the second sheet, just like it is. The problem is that a column from this sheet is related to the first one, so the results I get when I print the extracted values, are some...

Missing Microsoft Excel as option in SQL Server data import tool

sql-server-2012,64bit,excel-2007,import-from-excel,windows-server-2012-r2
I'm running SQL Server 2012 on a 64bit Windows Server 2012 R2. I don't have MS Office installed. I now notice that when I start Start->Program Files->Microsoft SQL Server 2012->Import and Export Data (64-bit) the MS Excel file option is missing from the datasource dropdown. How can I get that...

Printing contents of xlsx sheet

java,eclipse,docx4j,import-from-excel
I'm using docx4j in eclipse to get the contents of an excel sheet but all what I'm getting is numbers. For simplicity, assume this is my sheet: | asd | sd | | hgn | The code I'm using to load the contents is: public static void load(String outputfilepath) throws...

reading entries from workbook sheet in python

python,matrix,import-from-excel
In the code attached, I have a matrix as below: guyprefers = { 'bob': ['abi', 'dee', 'fay', 'bea', 'jan'], 'gav': ['abi', 'bea', 'dee', 'jan', 'fay'], 'ian': ['bea', 'dee', 'abi', 'fay', 'jan'], 'jon': ['bea', 'abi', 'fay', 'jan', 'dee']} I want to read above matrix from a worksheet preferences.xls preferences.xls looks like...

Android Studio reading data from excel?

android-studio,import-from-excel
i was wondering if there is any effecient way to read data from an Excel file in Android Studio. I have tried aspose - cells but it dosn't seem to work for me...