CODEWITHSUNDEEP
phpexcelcsv
snehal
snehal

Reputation: 449

Convert large xlsx file to csv without using phpExcel

I have a large xlsx file that is 90MB using phpexcel it is giving me 

Warning: simplexml_load_string(): Memory allocation failed : growing buffer

I tried to load the file using every methods documented here, and also changed php.ini memory_limit = -1 .

I am trying to convert the xlsx file to a csv file so it can be easily loaded.

Is there any way to convert xlsx file to csv without using phpexcel?

Upvotes: 10

Views: 4306

Answers (4)

Eduardo Poço
Eduardo Poço

Reputation: 3079

XLSX files are compressed zip files. If you decompress your XLSX file, look at the folder xl/worksheets, which contains a xml file for each sheet of the file.

You may want to extract these XML files first and then parse the (xml) content, element by element, so that the buffer to get each xml element does not need to be so big. This way, you can make your own script in php to read the extracted file, or use some xml parser, to transform the sheets into xml objects and them dump your csv.

The structure of the resulting xml is something like this example (the important information is inside sheetData):

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac">
    <dimension ref="A1:J42"/>
    <sheetViews>
        <sheetView workbookViewId="0">
            <selection activeCell="C7" sqref="C7"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultRowHeight="12.75" x14ac:dyDescent="0.2"/>
    <cols>
        <col min="1" max="1" width="18.140625" style="1" customWidth="1"/>
        <col min="2" max="16384" width="9.140625" style="1"/>
    </cols>
    <sheetData>
        <row r="1" spans="1:10" x14ac:dyDescent="0.2">
            <c r="B1" s="1" t="s"><v>0</v></c>
            <c r="C1" s="1" t="s"><v>1</v></c>
            <c r="D1" s="1" t="s"><v>2</v></c>
        </row>
        <row r="2" spans="1:10" x14ac:dyDescent="0.2">
            <c r="A2" s="1" t="s"><v>4</v></c><c r="B2" s="1"><v>200</v></c>
            <c r="C2" s="1"><v>200</v></c>
            <c r="D2" s="1"><v>100</v></c><c r="E2" s="1"><v>200</v></c>
        </row>
        <row r="3" spans="1:10" x14ac:dyDescent="0.2">
            <c r="A3" s="1" t="s"><v>10</v></c><c r="C3" s="1"><f>6*125</f><v>750</v></c>
            <c r="H3" s="1" t="s"><v>6</v></c><c r="I3" s="1"><v>130</v></c>
        </row>
    </sheetData>
    <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
    <pageSetup paperSize="0" orientation="portrait" horizontalDpi="0" verticalDpi="0" copies="0"/>
</worksheet>

That is, you need to look at each cell (c tag) of each row (row tag) the xml has:

worksheet.sheetData.row[i].c[j].v

and take the content of the value (v tag).

Upvotes: 2

J. Chomel
J. Chomel

Reputation: 8395

You can do this with ; with the below, you will scan A1 -> A10 and export the 5 first columns of the "DATA" tab of current workbook.

Sub exportCSV()

  Dim wkRange As Range
  Dim cpSheet As Worksheet

  Dim myPath As String, myFileName As String
  Dim fn As Integer ' File number
  Dim cLine As String ' current line to be writen to file

  ' create output file:
  myPath = "C:\local\"
  myFileName = "out.csv"
  fn = FreeFile
  Open myPath & myFileName For Append As #fn
  Set wkRange = ThisWorkbook.Sheets("DATA").Range("$A1:$A10")
  For Each c In wkRange
  ' select your columns with "offset"
    cLine = c.Offset(0, 0).Value & ","
    cLine = cLine & c.Offset(0, 1).Value & ","
    cLine = cLine & c.Offset(0, 2).Value & ","
    cLine = cLine & c.Offset(0, 3).Value & ","
    cLine = cLine & c.Offset(0, 4).Value
    Print #fn, cLine
  Next
  Close #fn
  MsgBox "done!"

End Sub

Upvotes: 0

hd1
hd1

Reputation: 34657

You can use python:

wb = xlrd.open_workbook(os.path.join(filepath, 'result.xls'))
sheet = wb.sheet_by_index(0)
fp = open(os.path.join(filepath, 'result.csv'), 'wb')
wr = csv.writer(fp, quoting=csv.QUOTE_ALL)
for rownum in xrange(sheet.nrows):
  wr.writerow([unicode(val).encode('utf8') for val in sheet.row_values(rownum)])

Upvotes: 3

Hundred Songs
Hundred Songs

Reputation: 60

Online converter up to 100MB file size:

http://www.zamzar.com/convert/xlsx-to-csv/

3 way's tutorial:

http://www.ehow.com/how_6921725_convert-xlsx-file-csv.html

hope this helps...

Upvotes: 1

Related Questions