/    Sign up×
Community /Pin to ProfileBookmark

reading an excel file

Copy linkTweet thisAlerts:
Aug 30.2005

Hi

I have a large xls file and I want to read it. I tried a lot of ways an I got different errors for them.

I have found a class that works fine as long as the xls file is small but when the size exceeds 5 MB things don’t go well.

I even tried asp but it isn’t working either.

file() function works fine but I don’t know how to separate columns, it just returns lines of text.

Any suggestion please?

and, I cant convert it to csv because the text inside cells contains commas, and the same goes for tab.

to post a comment
PHP

8 Comments(s) ↴

Copy linkTweet thisAlerts:
@NogDogAug 30.2005 — Commas don't matter if you save it as a CSV file. Part of the CSV specification deals with that. If you use the PHP fgetcsv() function it will also deal with it. The only concern is if you have line-breaks within any of the CSV fields.

Another possibility if on a Windows server is to use an ODBC driver for Excel and access the xls file as a database.
Copy linkTweet thisAlerts:
@amahmoodauthorAug 30.2005 — Thanks NogDog

the file does contain line breaks in it.

I go with ODBC driver and get back to tell the result.
Copy linkTweet thisAlerts:
@amahmoodauthorAug 30.2005 — after setting ODBC in windows xp and implementing the following code:
[code=php] // Connect to the ODBC datasource 'accessBase1'
$connect = odbc_connect("test","","");
// form query statement
$query = "SELECT * FROM TestData"; [/code]
I got this error:
[code=php] Error: Too many fields defined, [/code]

Aparently ODBC only accepts 127 fields but my excel has around 290 fileds.

Any suggestion?
Copy linkTweet thisAlerts:
@NogDogAug 30.2005 — 290 fields! :eek:

Afraid I've run out of ideas - other than getting all that data into a real database. ?
Copy linkTweet thisAlerts:
@amahmoodauthorAug 30.2005 — 290 fields! :eek:

Afraid I've run out of ideas - other than getting all that data into a real database. ?[/QUOTE]

Thanks NogDog

Actually thats what I am trying to do => reading excel and putting info into mysql.
Copy linkTweet thisAlerts:
@NogDogAug 30.2005 — Doh! Sounds like a "catch 22".

Only other thing I can think of is to try using this PHP code to convert the CSV data: http://www.php.net/manual/en/function.fgetcsv.php#56192
Copy linkTweet thisAlerts:
@theuedimasterAug 31.2005 — how bout xml?

or you could read the csv file in parts, just do a for loop and accept and do the first 100 fields, then do the next 100. (just use a for loop).
Copy linkTweet thisAlerts:
@amahmoodauthorAug 31.2005 — I'm afraid data contains comma as well.

for the moment I have decided to split the excel file into three files.
×

Success!

Help @amahmood spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.2,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,
)...