Having trouble opening large XML files in Excel?

My PHP script successfully generates an XML file from a database, but I'm encountering an issue with larger files. The table in the database has 53 columns.

Excel can easily open a file with 7000 rows (20Mb), but once it reaches around 14000 rows (40Mb), Excel refuses to open it as a table and displays an HTML import error. Instead, it suggests opening the file as a text file, which is not what I want.

I am using the latest version of Excel for Mac and cannot figure out where I might be going wrong.

Below is a snippet of the XML code:

<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:x="urn:schemas-microsoft-com:office:excel"
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet ss:Name="XXXX">
<Table>
    <Row>
        <Cell><Data ss:Type="String">Id</Data></Cell>
        <Cell><Data ss:Type="String">Postal Code</Data></Cell>
        .....
    </Row>
    <Row>
        <Cell><Data ss:Type="Number">43653</Data></Cell>
        <Cell><Data ss:Type="Number">347</Data></Cell>
        .....
    </Row>
</Table>

phpxmlexcel

Answer №1

Make sure to verify the version of your Excel software,

Prior to version 7.0, Excel had a restriction on the size of data sets, limiting them to 16K (214 = 16384) rows.

From versions 8.0 to 11.0, Excel was able to manage

64K (216 = 65536) rows and 256 columns (28 represented by 'IV' in labels).

With version 12.0, Excel can support

1M (220 = 1048576) rows and 16384 (214 as 'XFD') columns.

I hope this information proves useful.

Answer №2

I've discovered that my previous method of generating pure XML files is not ideal, as it relies on Excel 2003 standards. Instead, I stumbled upon a PHP library named Spout which boasts impressive speed and the ability to handle large Excel files with ease: Check out the Spout PHP Library on Github

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Enhance Your Website with Audio Recording Using PHP and jQuery

I am currently working on a running project and require audio recording capabilities. Can anyone advise me on how to record only voice and save it effectively? It would be highly appreciated if someone could recommend some jQuery plugins or provide me wit ...

phpjqueryaudioaudio-recording

Using PHP to ascertain the requested dataType or responseType from the client

My ajax request is fairly simple: $.post('server.php',data, function (json) {console.log(json)},'json'); I have configured jQuery to expect json data based on the dataType setting. Question: Is the dataType parameter equivalent to re ...

javascriptphpjqueryajaxhttp-headers

Despite Laravel 4 seed showing successful completion, the database remains empty

Currently, I am using Laravel 4 and attempting to seed the database with some users. Instead of extending from Eloquent, my User model extends ConfideUser as per the instructions due to the use of the Zizaco Confide plugin. I have also made some modificati ...

phpmysqllaravel-4eloquent

Obtaining the first element from an array or JSON object

Can someone help me extract the image value from an array or JSON data? I have a data structure that includes various values, but I am only interested in retrieving the image value. This is an example of what I'm currently getting: {"image_intro":" ...

phparraysjson

What is the best way to retrieve a selected value from one dropdown list and populate it into another dropdown

Can someone assist me with retrieving the selected answer from one drop-down list and populating it into another drop-down list? Below is my code for programming groups A and B: Example: If a user selects an option from group A and group B, I would li ...

javascriptphphtml

Creating PDF files with PHP

When attempting to generate a PDF in PHP using the code below, the file opens with a "corrupted or damaged file" error: $FileName = date("d-m-y") . '.pdf'; $Content = ""; # Title of the CSV $Content = "Name,Address,Age,Phone \n"; # Fill d ...

phppdffpdf

How can I construct an if statement using various database fields?

Hi there, I'm new to posting and coding. In my current role, I handle a variety of tasks related to maintaining our website, mostly involving HTML and basic PHP. However, today I've been tasked with something that goes beyond my current knowledge ...

php

Using AJAX call and jquery each loop to work with JSON syntax

I made an AJAX request that fetches data from PHP. echo json_encode($json_response); The output looks like this: [{"name":"Sprouts.......}] Next, I used JQUERY to iterate through the data using the following code: $.each($.parseJSON(data), function(i, ...

phpjqueryjsonajax

Disabling Javascript in Chrome (-headless) with the help of PHP Webdriver

I am experimenting with using Chrome without JavaScript enabled. I attempted to disable JavaScript by adding the --disable-javascript command line argument. I also tried some experimental options: $options->setExperimentalOption('prefs&a ...

javascriptphpgoogle-chromeseleniumubuntu-16.04

PHP is struggling to interpret the JSON object

I am struggling to pass the username and password to a PHP script and verify them in the database. To achieve this, I utilize the following client-side script to create a JSON object and send it to the PHP file. var myobj = {}; myobj["usrname"]= $( "#cust ...

phpjqueryjsonmysqli

Using CURL to connect to my personal network at home

Hey there, I have set up my home computer to run an Apache server on port 5900 with a Dynamic DNS to keep track of the IP address. I can access my server's index page from a different network using MYURL.com:5900. Now, I want to send a message to my h ...

phpscriptingcurl

What is causing the error message stating that the DateTime class object cannot be converted to a string?

I found this code snippet on a programming forum function increaseDate($date_str, ${ $date = new DateTime($date_str); $start_day = $date->format('j'); $date->modify("+{$months} month"); $end_day = $date->format(&apo ...

phphtmldatedatetimestring-formatting

What is the best way to compare a JSON object with a string in an Android application?

Here is the code snippet that functions correctly by displaying content when the EditText field is left blank or contains a string value present in the MySQL database. However, I am facing an issue where I want to show an error message when the input from ...

phpandroidmysqljson

Display the same data point on a Google Map from two different rows of a database

I'm looking to display multiple values for profFName and profLName on a map with just one point showing this information. If I have (2) pID connected to the same marker, I want it to show both profFName and profLName on a single point. Currently, it ...

phpjavascript

PHP generator is capable of yielding the initial value before looping through the remaining values

I am working on this particular code snippet: <?php function generator() { yield 'First value'; for ($i = 1; $i <= 3; $i++) { yield $i; } } $gen = generator(); $first = $gen->current(); echo $first . '<br ...

phpgeneratoryield

How can I use variables to show the second dropdown list only when a value has been selected in the first dropdown?

Is there any way I can choose a specific value from a dropdown list and based on that selection, show another dropdown list? I understand how to do it with regular words, but what if I use variable names? University Name: <select name="university" id=" ...

phpjavascriptjquery

Tips for identifying a range between two columns in MySQL

If I have a value, let's say 45. What is the best way to determine if this value exists in the database? The complication arises from having two columns like so: -------------------- range_from range_to -------------------- 10 ------------ 20 - ...

phpmysqlsql

Dynamically load images from a database using PHP within a JavaScript AJAX script to display them in a jCarousel

I am encountering some challenges with dynamically loading images into my jCarousel AJAX gallery using PHP. I am unsure of how to integrate PHP code within the JavaScript code, especially when it comes to accessing data from a database. var mycarousel_ite ...

phpsqlmysqlajaxjcarousel

Extract website information, transform into JSON format, input into SQL database?

I have this interesting project where I am seeking to extract data from an external webpage, transform it into a specific structure, and then store it in a database. The purpose of doing this is purely for enjoyment - I'm essentially replicating an e ...

jquerysqlxmljson

Is it acceptable to post variables using AJAX for processing?

Is there a way to send data with AJAX using the code provided? It seems like the information is not being sent to the PHP file as intended. Here is the code snippet: $('#send').click(function(){ var pseudo = $('#psd').val(); }) ...

javascriptphpjqueryajax