There are two xls files we get from another department every week and these files need to get into the database. We have a few other fixed space txt files that we already have built cfm pages to parse and insert into the database. But reading the xls file isn't as clean. Is there a way to read in an xls file as a (tab, space, whatever) delimited string?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
read, parse, and insert an excel spreadsheet
- Thread starter diggy8
- Start date
csteinhilber
Programmer
Simple, rather reliable way... open the .XLS and do a Save As from Excel as "CSV (Comma Delimited)", "Formatted Text" or "Text (Tab Delimited)" file types, depending on which is more appropriate for your situation.
Then you can use that file pretty much as easily as your other txt files. Using them with the Merant Text Driver (pre-MX) is an great solution... though it doesn't provide UPDATE capability.
The harder, less reliable way... use one of the Custom Tags that have been built to read .XLS files directly as a "native" data source. I say this is harder and less reliable because it seems your set-up has to be absolutely perfect (ie - exactly like the set-up that the developer developed it on) for it to work. If you have a different version of Excel, a different OS service pack, or if the moon and stars are even slightly mis-aligned, you won't get very far.
There are free versions on Macromedia's developer exchange, I'm pretty sure, and commercial versions (just do a web search).
Also, all the ones I've seen only run on a Windows server.
-Carl
Then you can use that file pretty much as easily as your other txt files. Using them with the Merant Text Driver (pre-MX) is an great solution... though it doesn't provide UPDATE capability.
The harder, less reliable way... use one of the Custom Tags that have been built to read .XLS files directly as a "native" data source. I say this is harder and less reliable because it seems your set-up has to be absolutely perfect (ie - exactly like the set-up that the developer developed it on) for it to work. If you have a different version of Excel, a different OS service pack, or if the moon and stars are even slightly mis-aligned, you won't get very far.
There are free versions on Macromedia's developer exchange, I'm pretty sure, and commercial versions (just do a web search).
Also, all the ones I've seen only run on a Windows server.
-Carl
- Status