Search Results

Search found 4783 results on 192 pages for 'excel vba'.

Page 74/192 | < Previous Page | 70 71 72 73 74 75 76 77 78 79 80 81  | Next Page >

• Problem with update sql with excel

  - by phenevo

  Hi, I have a problem with this query: Update Provinces Set Provinces.DefaultName=T2.Defaultname from Provinces inner join OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\provinces.xlsx;HDR=YES', 'SELECT Code, Defaultname FROM [Arkusz1$]') T2 On Provinces.Code = t2.Code where Provinces.Code = T2.Code I get error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)". What is a reason of this unpleasent situation ?

  Read the article

• excel number format - varying decimal digits

  - by Alexxx

  I'm trying to set a special cell number format with theses rules: display percentage display at max 3 digits (decimal + integer part) So I can display 100% or 99.3% or 1.27% but not 100.9% or 100.27% or 99.27%. Of course, I can have negative number (-27.3%) and it does not affect my rules. I've try with the cell formating option without success: [<1]0.00%;[<10]0.0%;0% Because it seemed that excel (2010) does not support more than 2 conditions in cell formating (and so I can't expand it to manage negative number...) It there anyway to do what I want? Thanks

  Read the article

• Excel - check if row contains ANY value *more than once*

  - by user2536778

  I am doing data analysis and sometimes I need to check and to make sure each row in excel does not have any repeated value. I hope that there is a formula where each time if there's a repeated value in the same row, the value will be highlighted and it doesn't matter what value it is, as long as it's repeated in the same row. I try to search it everywhere but the closest I can find is below question & answer ( which couldn't apply to me as I need a formula that can highlight any value that's repeated and not only zero): I have rows which contain grades for students (numerical values), where the number 0 means they missed a class. I want the row to be highlighted in one color if they have "0" 3 or 4 times, and in another color if they have "0" 5 times or more. =COUNTIF(1:1,0)=5 Anyone can help me? Thanks in advance!

  Read the article

• Import de-normalized relational data from Excel into SQL Server

  - by roryf

  I need to import data from an Excel spreadsheet into SQL Server, but the data isn't in a relational/normalized format so the import wizard isn't going to cut it (as far as I know). The data is in this format: Category SubCategory Name Description Category#1 SubCategory#1 Product#1 Description#1 Category#1 SubCategory#1 Product#2 Description#2 Category#1 SubCategory#2 Product#3 Description#3 Category#1 SubCategory#2 Product#4 Description#4 Category#2 SubCategory#3 Product#5 Description#5 (apologies I'm lacking the inventiveness to come up with 'real' data at this time in the morning...) Each row contains a unique product, but the cateogry structure is duplicated. I want to import this data into three tables: Category SubCategory Product (I know SubCategory should really be contained within Category, DB was not my design) I need a way to import unique rows based on the Category and then SubCategory columns, and then when importing the other columns into Product, obtain a reference to the SubCategory based on name. Short of scripting this, is there any way to do it using the import wizard or some other tool?

  Read the article

• EXCEL import to sql returning NULL for decimals when in VARCHAR data type

  - by Daniel

  Hi, I am working on a peice of software which has expodentially grown over the last few years and the database needs to be regularly updated. Customers are providing us with data now on large spreadsheets which we format and will start importing into the database. I am using the Import and Export Data (32-bit) Wizard. One column in the database contains values like '1.1.1.2' etc and i am importing them in as a Varchar as that is the data type in the database. However, for values like '8.5', 'NULL' is getting imported insead. It only occurs when there is one decimal point. Is this a formatting error with excel or is it the wrong datatype?

  Read the article

• Calculating percentiles in Excel with "buckets" data instead of the data list itself

  - by G B

  I have a bunch of data in Excel that I need to get certain percentile information from. The problem is that instead of having the data set made up of each value, I instead have info on the number of or "bucket" data. For example, imagine that my actual data set looks like this: 1,1,2,2,2,2,3,3,4,4,4 The data set that I have is this: Value No. of occurrences 1 2 2 4 3 2 4 3 Is there an easy way for me to calculate percentile information (as well as the median) without having to explode the summary data out to full data set? (Once I did that, I know that I could just use the Percentile(A1:A5, p) function) This is important because my data set is very large. If I exploded the data out, I would have hundreds of thousands of rows and I would have to do it for a couple of hundred data sets. Help!

  Read the article

• Force column order, Excel data table

  - by Chris

  I have a Excel Workbook that I use as a report template. I change the datasource on each pivot and datatable in a C# app. When I change the datatable datasource it tweeks the columns. Is there a way to force the column order? private void RefreshRawData(string dataSource, string connection) { xl._Worksheet ws = (xl._Worksheet)xlTemplate.Worksheets["Raw Data"]; xl.ListObject table = ws.ListObjects["Table_ExternalData_1"]; xl.QueryTable qt = table.QueryTable; qt.CommandText = dataSource; qt.Connection = GetExcelConnectionString((string)qt.Connection); qt.BackgroundQuery = false; qt.Refresh(m); Marshal.ReleaseComObject(ws); Marshal.ReleaseComObject(table); Marshal.ReleaseComObject(qt); ws = null; table = null; qt = null; }

  Read the article

• Excel PivotTable : Calculated Field / Item for Period Comparison

  - by dino76

  HI All, If I have a PivotTable in Excel 2007 with a date field. I understand that I can group the date by day, month or even year using Group Field (Years & Months). If I combine with product perspective, the PivotTable may look like this Sum of Sales_Total | Column Labels Row Labels | PRODUCT-001 | PRODUCT-002 | Grand Total - 2006 | 2000 | 1500 | 3500 Jan | 1700 | 800 | 2500 Feb | 300 | 700 | 1000 - 2007 | 1000 | 1500 | 2500 Jan | 700 | 800 | 1500 Feb | 300 | 700 | 1000 - 2008 | 600 | 700 | 1300 Jan | 600 | 700 | 1300 Now, what I want to do is to compare Jan 2008 - Jan 2006 and Jan 2007 - Jan 2006. Something like this : | Column Labels | PRODUCT-001 | | ... Row Labels | Sum of Sales | Sum of Last Sales | - 2006 | 2000 | | Jan | 1700 | | Feb | 300 | | - 2007 | 1000 | 2000 | Jan | 700 | 1700 | Feb | 300 | 300 | - 2008 | 600 | 1000 | Jan | 600 | 700 | Is it possible ? If so, how to do that ? Thanks, D. Chopins

  Read the article

• Find similar or "like" text and replace it with other in excel

  - by andreas

  Does anyone know how i can find similar descriptions in excel and replace them with 1 other description is there a wild card? i am. trying to make a pivot chart with a list of transactions and their descriptions and i want to group all my ATM withdrwls but i cant. On the pivot chart they appear as ATM Withdrwal-REF-1234 and each of these "withdrwls" have different reference and as a result they show up as individual items on the chart...how can i group say all my ATM withdrwals as 1 ATM Withrdawl item so that it shows a 1 atm withdrwl item on my pivot chart?

  Read the article

• Saving Excel Spreadsheet using Interop C#

  - by Wesley

  static void Main() { Application excelapp = new Application(); Workbook book = excelapp.Workbooks.Open(@"C:\HWYFAB.xlsx", 0, false, 5, "", "", false, XlPlatform.xlWindows , "", true, false, 0, true, false, false); Worksheet sheet = (Worksheet)book.Sheets[1]; Range cell = (Range)sheet.Cells[3, 2]; Console.WriteLine(cell.Text); cell.ClearContents(); book.Close(true, "HWYFAB.xlsx", false); excelapp.Quit(); } This program runs and exits as expected. It does print the correct value that's in cell B3 to the console. When closing it asks if I want to replace the existing file. I click yes. When I open the spreadsheet in Excel, the value is still in cell B3 despite the cell.ClearContents(). Any thoughts?

  Read the article

• excel 2003 can`t edit comments (user)

  - by Dezigo

  I have a .xls file of excel 2003. There are a lot of comments. I can`t edit it. right click -edit comments for example: I have comment: Ludmila: comment goes here Then Ludmila: comment goes here Dezigo:new comment..! I tryed to do: Tools-options-general (change my name to Ludmila),but it`s not work.. Like it Ludmila: comment goes here Ludmila:new comment.. and comment goes here -can`t edit it. file is not protected.

  Read the article

• Count Clicks in excel

  - by rockbala

  Hi, Can some one recommend any free program which counts the number of clicks Clicked inside a cell. For Example Imagine something like Spreadsheet I click on A1 cell the value shows 1 Then I click A1 cell again the value shows 2 and so on If I click A3 cell somewhere in between the click count on Cell A3 shows 1 and so on If something like this can be achieved as a macro with in excel (2003 please) please suggest or any other free program that you might be aware about, please do let me know. I appreciate all your help and thank you in advance. rockbala

  Read the article

• How to turn off Excel "Header Row" without losing data in it?

  - by Ken

  I've been sent an Excel spreadsheet with a weird first row. Some of the cells say "Column1", "Column2", etc., but I can't delete their contents. If I select the cell and hit backspace, it goes blank, but when I press return, it goes right back to saying "Column1". I found another answer here that suggested this could be caused by "Cell validation", but the validation window says "Any value", and also "show alert" (and I'm not seeing an alert), so I don't think that's it. The first row is white text on a blue background, if that means anything. The spreadsheet was sent to me in XLSX format, but I tried resaving as XLS and opening that, and it seems to make no difference. This is with the "ribbon" version of Excel (they got rid of the Help menu so I don't know how to see what version number it is!). Thanks! Update: The Excel online help says to use ribbon Home tab - Cells - Delete - ... to delete cells. When I select anything on the first row, this pop-up menu is dimmed. So maybe Excel doesn't think row 1 consists of "cells"? Though I don't know what else it would call them. Update 2: I found it, kind of. If I click the "Design" tab in the ribbon, then uncheck "Header Row", then first row becomes a normal row of cells again. Unfortunately, the contents disappear entirely. I want to delete a few cells, not all 50+! And if I copy the first row before turning off "Header Row", it disappears from the clipboard when I uncheck that. So I kind of know what mode it's stuck in, but not a good way out of it.

  Read the article

• Excel UDF calculation should return 'original' value

  - by LeChe

  Hi all, I've been struggling with a VBA problem for a while now and I'll try to explain it as thoroughly as possible. I have created a VSTO plugin with my own RTD implementation that I am calling from my Excel sheets. To avoid having to use the full-fledged RTD syntax in the cells, I have created a UDF that hides that API from the sheet. The RTD server I created can be enabled and disabled through a button in a custom Ribbon component. The behavior I want to achieve is as follows: If the server is disabled and a reference to my function is entered in a cell, I want the cell to display Disabled If the server is disabled, but the function had been entered in a cell when it was enabled (and the cell thus displays a value), I want the cell to keep displaying that value If the server is enabled, I want the cell to display Loading Sounds easy enough. Here is an example of the - non functional - code: Public Function RetrieveData(id as Long) Dim result as String // This returns either 'Disabled' or 'Loading' result = Application.Worksheet.Function.RTD("SERVERNAME", "", id) RetrieveData = result If(result = "Disabled") Then // Obviously, this recurses (and fails), so that's not an option If(Not IsEmpty(Application.Caller.Value2)) Then // So does this RetrieveData = Application.Caller.Value2 End If End If End Function The function will be called in thousands of cells, so storing the 'original' values in another data structure would be a major overhead and I would like to avoid it. Also, the RTD server does not know the values, since it also does not keep a history of it, more or less for the same reason. I was thinking that there might be some way to exit the function which would force it to not change the displayed value, but so far I have been unable to find anything like that. Any ideas on how to solve this are greatly appreciated! Thanks, Che EDIT: By popular demand, some additional info on why I want to do all this: As I said, the function will be called in thousands of cells and the RTD server needs to retrieve quite a bit of information. This can be quite hard on both network and CPU. To allow the user to decide for himself whether he wants this load on his machine, he or she can disable the updates from the server. In that case, he or she should still be able to calculate the sheets with the values currently in the fields, yet no updates are pushed into them. Once new data is required, the server can be enabled and the fields will be updated. Again, since we are talking about quite a bit of data here, I would rather not store it somewhere in the sheet. Plus, the data should be usable even if the workbook is closed and loaded again.

  Read the article

• How can I turn off calculated columns in an Excel table from a macro using VBA?

  - by user41293

  I am working on a macro that inserts formulas into a cell in an Excel table. The Excel table does the automatic filling of columns and fills all the cells in that column with the formula, but all I want is one cell to have the formula. I cannot just turn off automatic formula for tables as I need to have other people use this worksheet on their systems. Is there a way to turn off the automatic filling of formulas in a table using VBA in a macro? It just needs to be temporary: I just want to turn it off, put in my formulas, then turn it back on.

  Read the article

• Excel 2007 - "The macro may not be available in this workbook" Error

  - by Psycho Bob

  We use an Excel sheet that has been protected to prevent modification of it from end users. All in all they are only able to edit certain tabs to add information that will then be used to generate information on other tabs using equations and such. On the tab with the equations, a button is present called "Prep for Internal Hard Copy Print." This button runs a macro that selects the information on the tab, unprotects it, then sends a print job to the user's default printer that contains the unprotected content. Normally this works like a champ. This time around, however, the macro is throwing the following error: Cannot run the macro "FILENAME.xlsx'!MacroName'. The macro may not be available in this workbook or all macros may be disabled. As far as I can tell, the macros are still present within the workbook. This sheet is normally a .xlsm though the user saved it with a different filename as a .xlsx. Also, the macros appear only as MacroName in the .xlsm file and not "FILENAME.xlsx'!MacroName' as it does in the .xlsx. Finally, when I open the .xlsm it asks if I want to enable the macro content while the .xlsx does not prompt for this. Can anyone tell me what's going on with this sheet or know of a way that I can get the macros working in the .xlsx without having to start over with a different sheet?

  Read the article

• Excel export displaying '#####...'

  - by Cypher

  I'm trying to export an Excel database into .txt (Tab Delimited), but some of my cells are quite large. When I export into a txt some of the cells are exported as '#######....' which is surprisingly useless. Has this happened to anyone else? Do you know an easy fix? Data from one cell of my column: Accounting, African Studies, Agricultural/Bioresource Engineering, Agricultural Economics, Agricultural Science, Anatomy/Cell Biology, Animal Biology, Animal Science, Anthropology, Applied Zoology, Architecture, Art History, Atmospheric/Oceanic Science, Biochemistry, Biology, Botanical Sciences, Canadian Studies, Chemical Engineering, Chemistry/Bio-Organic/Environmental/Materials,ChurchMusicPerformance, Civil Engineering/Applied Mechanics, Classics, Composition, Computer Engineering,ComputerScience, ContemporaryGerman Studies, Dietetics, Early Music Performance, Earth/Planetary Sciences, East Asian Studies, Economics, Electrical Engineering, English Literature/ Drama/Theatre/Cultural Studies, Entrepreneurship, Environment, Environmental Biology, Finance, Food Science, Foundations of Computing, French Language/Linguistics/Literature/Translation, Geography, Geography/ Urban Systems, German, German Language/Literature/Culture, Hispanic Languages/Literature/Culture,History,Humanistic Studies, Industrial Relations, Information Systems, International Business, International Development Studies, Italian Studies/Medieval/Renaissance, Jazz Performance, Jewish Studies, Keyboard Studies, Kindergarten/Elementary Education, Kindergarten/Elementary Education/Jewish Studies,Kinesiology, Labor/Management Relations, Latin American/Caribbean Studies, Linguistics, Literature/Translation, Management Science, Marketing, Materials Engineering,Mathematics,Mathematics/Statistics,Mechanical Engineering, Microbiology, Microbiology/Immunology, Middle Eastern Studies, Mining Engineering, Music, Music Education, MusicHistory,Music Technology,Music Theory,North American Studies, Nutrition,OperationsManagement,OrganizationalBehavior/Human Resources Management, Performing Arts, Philosophy, Physical Education, Physics, Physiology, Plant Sciences, Political Science, Psychology, Quebec Studies, Religious Studies/Scriptures/Interpretations/World Religions,ResourceConservation,Russian, Science for Teachers,Secondary Education, Secondary Education/Music, Secondary Education/Science, SocialWork, Sociology, Software Engineering, Soil Science, Strategic Management, Teaching of French/English as a Second Language, Theology, Wildlife Biology, Wildlife Resources, Women’s Studies.

  Read the article

• Apache POI Comment Excel

  - by Marquinio

  I need to add a comment to an HSSF Cell in Excel. Everything works fine the very first time but if I open the same file and run the code again it corrupts the file. I've also noticed that I need to create a Drawing object on a Sheet only once: _sheet.createDrawingPatriarch(); If the line above gets executed more than once comments will not work. So has anyone tried adding comments to Cells, closing the file, opening the file again and trying to add more comments to different cells? The below code works but if I open the file again then comments are not added, plus the file gets corrupted!!! Is there a way to get the existing Drawing object from a Sheet? Any ideas appreciated. Thanks!! _drawing = (HSSFPatriarch) _sheet.createDrawingPatriarch(); Row row = _sheet.getRow(rowIndex_); Cell cell = row.getCell(0); CreationHelper factory = _workbook.getCreationHelper(); HSSFAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5); org.apache.poi.ss.usermodel.Comment comment = _drawing.createComment(anchor); RichTextString str = factory.createRichTextString("Hello, World "+rowIndex_); comment.setString(str); cell.setCellComment(comment);

  Read the article

• mysql to excel generation using php

  - by pmms

  <?php // DB Connection here mysql_connect("localhost","root",""); mysql_select_db("hitnrunf_db"); $select = "SELECT * FROM jos_users "; $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) ); $fields = mysql_num_fields ( $export ); for ( $i = 0; $i < $fields; $i++ ) { $header .= mysql_field_name( $export , $i ) . "\t"; } while( $row = mysql_fetch_row( $export ) ) { $line = ''; foreach( $row as $value ) { if ( ( !isset( $value ) ) || ( $value == "" ) ) { $value = "\t"; } else { $value = str_replace( '"' , '""' , $value ); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim( $line ) . "\n"; } $data = str_replace( "\r" , "" , $data ); if ( $data == "" ) { $data = "\n(0) Records Found!\n"; } header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=your_desired_name.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data"; ?> The code above is used for generating an Excel spreadsheet from a MySQL database, but we are getting following error: The file you are trying to open, 'users.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now? What is the problem and how do we fix it?

  Read the article

• Excel and SQL, order by help

  - by perlnoob

  Im stuck in Excel 2007, running a query, it worked until I wanted to add a 2nd row containing "field 2". Select "Site Updates"."Posted By", "Site Uploaded"."Site Upload Date" From site_info.dbo."Site Updates" Where ("Site Updates"."Posted By") AND "Site Uploaded"."Site Upload Date">={ts '2010-05-01 00:00:00'}), ("Site Location"='Chicago') Union all Select "Site Updates"."Posted By", "Site Uploaded"."Site Upload Date" From site_info.dbo."Site Updates" Where ("Site Updates"."Posted By") AND "Site Uploaded"."Site Upload Date">={ts '2010-05-01 00:00:00'}), ("Site Location"='Denver') Order By "Site Location" ASC; Basically I want 2 different cells for the locations, example name - Chicago - denver user1 - 100 - 20 user2 - 34 - 1002 Right now for some odd reason, its combining it like: name - chicago user1 - 120 user2 - 1036 Please note updating to 2010 beta is not a viable option for me at this point. Any and all input that will help me is greatly apprecaited. I have read over http://www.techonthenet.com/sql/order_by.php however its not gotten me very far in this question. If you have another SQL resource you recomend for people trying to get their feet wet, I'd greatly apprecaite it. If it helps all the info is on the same table.

  Read the article

• Excel process not ending in Cluster environment

  - by Vasanth

  When we try to close excel object, it fails to close to cluster environment. The same is working fine in QA and UAT environment. public bool KillExcelProcess() { try { object misValue = System.Reflection.Missing.Value; wbObj.Save(); wbObj.Close(true, misValue, misValue); appC.Workbooks.Close(); appC.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(wbObj); System.Runtime.InteropServices.Marshal.ReleaseComObject(appC); wbObj = null; appC = null; } catch (Exception ex) { //throw ex; } finally { System.Threading.Thread.Sleep(5000); GC.Collect(); } return true; Calling function #endregion try { log.Info("CloseExcelService (MeasureSavingsComputeBO) Starts ..."); exConverter.KillExcelProcess(); while (true) { try { File.Delete(strFilename); break; } catch (Exception ex) { } }

  Read the article

• Excel automation: Close event missing

  - by chiccodoro

  Another hi all, I am doing Excel automation via Interop in C#, and I want to be informed when a workbook is closed. However, there is no Close event on the workbook nor a Quit event on the application. Has anybody done that before? How can I write a piece of code which reacts to the workbook being closed (which is only executed if the workbook is really closed)? Ideally that should happen after closing the workbook, so I can rely on the file to reflect all changes. Details about what I found so far: There is a BeforeClose() event, but if there are unsaved changes this event is raised before the user being asked whether to save them, so at the moment I can process the event, I don't have the final file and I cannot release the COM objects, both things that I need to have/do. I do not even know whether the workbook will actually be closed, since the user might choose to abort closing. Then there is a BeforeSave() event. So, if the user chooses "Yes" to save unsaved changes, then BeforeSave() is executed after BeforeClose(). However, if the user chooses to "Abort", then hits "file-save", the exact same order of events is executed. Further, if the user chooses "No", the BeforeSave() isn't executed at all. The same holds as long as the user doesn't click any of these options.

  Read the article

• Issue reading in a cell from Excel with Apache POI

  - by Nick

  I am trying to use Apache POI to read in old (pre-2007 and XLS) Excel files. My program goes to the end of the rows and iterates back up until it finds something that's not either null or empty. Then it iterates back up a few times and grabs those cells. This program works just fine reading in XLSX and XLS files made in Office 2010. I get the following error message: Exception in thread "main" java.lang.NumberFormatException: empty String at sun.misc.FloatingDecimal.readJavaFormatString(Unknown Source) at java.lang.Double.parseDouble(Unknown Source) at the line: num = Double.parseDouble(str); from the code: str = cell.toString(); if (str != "" || str != null) { System.out.println("Cell is a string"); num = Double.parseDouble(str); } else { System.out.println("Cell is numeric."); num = cell.getNumericCellValue(); } where the cell is the last cell in the document that's not empty or null. When I try to print the first cell that's not empty or null, it prints nothing, so I think I'm not accessing it correctly.

  Read the article

• mysql to excel genration using php

  - by pmms

  <?php // DB Connection here mysql_connect("localhost","root",""); mysql_select_db("hitnrunf_db"); $select = "SELECT * FROM jos_users "; $export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) ); $fields = mysql_num_fields ( $export ); for ( $i = 0; $i < $fields; $i++ ) { $header .= mysql_field_name( $export , $i ) . "\t"; } while( $row = mysql_fetch_row( $export ) ) { $line = ''; foreach( $row as $value ) { if ( ( !isset( $value ) ) || ( $value == "" ) ) { $value = "\t"; } else { $value = str_replace( '"' , '""' , $value ); $value = '"' . $value . '"' . "\t"; } $line .= $value; } $data .= trim( $line ) . "\n"; } $data = str_replace( "\r" , "" , $data ); if ( $data == "" ) { $data = "\n(0) Records Found!\n"; } header("Content-type: application/octet-stream"); header("Content-Disposition: attachment; filename=your_desired_name.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data"; ? the above code is used for genrating mysql to excel sheet but we are getting following error the file youare trying to open, 'users.xls',is in a different format than specified by the file extension. verify that the file is not corrupted and is from a trusted source before opening the file. do you want to open the file now?

  Read the article

• Parsing a Multi-Index Excel File in Pandas

  - by rhaskett

  I have a time series excel file with a tri-level column MultiIndex that I would like to successfully parse if possible. There are some results on how to do this for an index on stack overflow but not the columns and the parse function has a header that does not seem to take a list of rows. The ExcelFile looks like is like the following: Column A is all the time series dates starting on A4 Column B has top_level1 (B1) mid_level1 (B2) low_level1 (B3) data (B4-B100+) Column C has null (C1) null (C2) low_level2 (C3) data (C4-C100+) Column D has null (D1) mid_level2 (D2) low_level1 (D3) data (D4-D100+) Column E has null (E1) null (E2) low_level2 (E3) data (E4-E100+) ... So there are two low_level values many mid_level values and a few top_level values but the trick is the top and mid level values are null and are assumed to be the values to the left. So, for instance all the columns above would have top_level1 as the top multi-index value. My best idea so far is to use transpose, but the it fills Unnamed: # everywhere and doesn't seem to work. In Pandas 0.13 read_csv seems to have a header parameter that can take a list, but this doesn't seem to work with parse.

  Read the article

< Previous Page | 70 71 72 73 74 75 76 77 78 79 80 81  | Next Page >