Amcom Home Page

 Source Code Management with Multiple QA environments using Git

Up until now we've been using Seapine Surround SCM, it works much like Microsoft's Visual Source Safe in that it's linear development. When we were a very small team of 3 people Surround worked great because our development life cycle was also linear in that we developed in a Development branch, promoted to a QA branch which had a related QA environment, and then promoted to a Staging/PreProduction branch prior to releasing.

Now being slightly larger (7 developers) the linear approach has become a hurdle. We have multiple projects in play, using the same code base, and only one QA environment. This resulted in various issues:

  • With two+ projects in *THE* QA environment, you were never really sure if you were testing the project you think you're testing. Or if there's a bug, which project that bug originated from. It's really trying to do integration testing without the premise that all changes are meant to work with each other.
  • You'd get a log jam of changes where projects in QA weren't done testing, while in Development the next round of changes on the same files couldn't move forward because they were tied up in QA.
  • Hotfixes were problematic. As you'd have to make the change in front of what's in QA, and then make sure it retroactively gets merged back down stream.
  • Sometimes a file that was in QA, would have an unrelated change in Development put in that is then tested in QA and then prematurely pushed forward into Staging.

The next step is we needed to introduce multiple QA environments so that we can develop all these project independently of each other, test all these projects independently of each other, integrate them together, and support hotfixes with less grief.

Moving forward...

After doing an analysis of tooling and source code management (scm) processes we determined that Git was the scm tool of choice. It provides a lot of flexibility in that you can merge any which way, and branching is fast and inexpensive, thus making it easy to tailor a process that will work well for what we want to do.

Our process and branching model is completely inspired by this blog posting, so a big kudos to Vincent Driessen for putting that diagram together.

What we did to help train the development and project management staff is break it down into the various scenarios so that they could relate to it in comparison to how they were used to doing things.

Git multibranch model

We'll probably have to do a few more tweaks to get the kinks out, but if it helps I've made the presentation available for download below (I tried posting to SlideSix, SlideRocket, and SlideShare - but they didn't render it properly).

Download/View the scm process slides.

 ColdFusion Position - S.F East Bay, CA. You can make a difference!

Job Title: Sr. Web Software Engineer

Parameters:

  • Location: San Ramon, CA
  • Salary: DOE
  • Telecommute: Not available. Position requires face to face interaction with our onsite customer.
  • U.S Citizenship or Greencard required.
  • Relocation available.
  • Salary: Above competitive.

Description:

  • Do you spend most of your time in traffic thinking about how to solve certain problems?
  • Do you try to squeeze in one extra line of code before you have to go home?
  • Do you spend time thinking if there's a better way to do something?
  • Do you pay attention to the industry?

Amcom Technology, Inc. is looking for an experienced Software Engineer to join our product development team (full time). As part of a team based environment the position involves working on mission critical web based solutions that enable the efficiency and growth of our clients utilizing rapid application prototyping methodologies and rich internet application development.

We are looking for highly self motivated team oriented people who have a passion for solving both technical and non-technical problems.

Work closely with customers... As a consulting company, this position entails working closely with the customer throughout the entire life cycle of the project. From the inception of the project where problem definition and business requirements are defined, to agile development and release, and assisting in achieving project ROI.

Wear many hats... The primary role is that of development (80%-90%); however wearing many hats is a must. The various hats that can be worn, but not limited to, include: process engineer, release engineer, business analyst, I.T analyst, team lead, DBA, information architect, technical architect, usability engineer, designer, project manager, and product managers.

One of the advantages of working with Amcom is that your position can be custom tailored.

Influence change... Working directly with our customers, often interfacing directly with the President and Executive staff, this position is a highly visible one. The ability to completely influence from a corporate level the processes and policies of technology and business management are very prevalent.

Leadership at all levels... The position is a time demanding one, and requires someone naturally diligent, highly organized, self-motivated, extremely focused, and has a "do whatever it takes to win" attitude. Amcom believes in leadership at all levels - we expect people to step up and lead in their areas.

Opportunities for growth... As you grow we grow. All members of the team are critical to the growth of the company, and with that growth comes new opportunities. The further you progress with your talents and skills, the bigger the goals we're capable of achieving.

The Mission... Your mission, should you choose to accept it, is to create great user experiences for our customers and users; from the experience of developing software, to the experiences of the software itself.

Basic Requirements:

  • Work with team members to help translate customer needs into design/application specifications.
  • Architect, develop and design detailed prototypes and Web/RIA interfaces.
  • Work directly with clients to finalize application requirements.
  • Document all forms of knowledge (processes, procedures, specifications, designs, code).
  • Create and maintain database-driven web applications.
  • Integrate online applications with back-end systems (Web Services, SOAP, REST, etc).
  • Learn customer business models in order to lead technical solutions.

Required Technical Proficiencies:

  • 8+ Years of Software Development Experience.
  • Advanced Level with a Web Programming technology (PHP, .NET, JSP, etc...), preferrably ColdFusion.
  • We are a ColdFusion shop, and thus would require learning ColdFusion if necessary.
  • Advanced Levels with SQL, CSS, JavaScript, Web Services, XML.
  • Usability Best Practices.
  • Systems Architecture, Data Analysis, and Software Architecture.

Desired Technical Proficiencies:

  • Familiarity with Frameworks and Design Patterns.
  • Understanding of Agile development practices and concepts.
  • ColdFusion
  • Crystal Reports
  • jQuery
  • Flex
  • Drools
  • Groovy/Grails
  • ColdBox
  • Transfer
  • ColdSpring
  • Hibernate

General Business and Customer skills required:

  • Close attention to detail.
  • Ability to effectively test and document your own work.
  • Great organizational and time-management skills, including an ability to estimate project completion times.
  • Ability to juggle multiple projects, tasks, and deadlines.
  • Self-motivated and self-directed: We need someone who will proactively tackle challenges, and propose innovative solutions.
  • Great communication skills: both written and verbal (Developers respond directly to client requests, so the ability to correspond professionally is critical).
  • Properly assess and meet customer needs in a professional and courteous manner.
  • Anticipate customers' needs and communicates/pitches new ideas.

Salary:

  • Dependent on skills and experience.

Contact:

  • Tariq Ahmed (Manager of Product Development). jobs [AT] amcomtech.net
  • Direct-Hire only, no recruiters please.

About Amcom (www.amcomtech.net):

Amcom Technology is a member of the Amcom companies located in the San Francisco Bay Area (San Ramon). The company provides premium end-to-end technology services from Web 2.0/RIA software development, to network infrastructure and business intelligence.

Amcom consists of two other divisions which include Amcom Data Processing (ADP) and Amcom Computer Services (ACS).

Amcom Data Processing was founded on April 1, 1973, and has been involved with the oil industry since 1978 with distributors and retail operations. The company currently has relationships with six major oil companies and specializes in the premiere software solutions for the Service Station industry.

Amcom Computer Services focuses on outsourcing, facilities management, programming, training, and supporting computer systems of small and medium sized businesses.

Acrobat PDF Version available

 Job Avail: Sr. Business/Data Analyst. San Ramon, CA.

Position: Sr. Data/Business Analyst
  • Job Type: Full Time, direct hire only.
  • Telecommute: Not available.
  • Location: San Ramon, CA.

Description:

Business Intelligence (B.I) is about getting the right information, to the right people, at the right time.

It provides the ability to connect the dots by taking raw information, analyzing it for patterns, and transforming it into human readable consumption. It enables management to have a constant pulse on the business, as well as being able to monitor changes in customer behavior, market conditions, the regulatory environment, and corporate position vs. competitors. By eliminating guess work the company can execute with a tactile competitive advantage.

Amcom Technology is currently looking for a Sr. Data/Business Analyst who can work with our customer (Old Republic Home Protection) to define their Business Intelligence needs, and then implement those needs.

The position requires someone passionate about growing their skills, and in doing so the opportunity to tailor ones career is ever present. Since the B.I Initiative is a relatively new service in our organization it allows anyone coming into this position to help define the vision, and take the B.I platform to as far as their imagination and desire can go.

Responsibilities Include (but not limited to):

  • Working with Executive Management to understand the information they need to drive the business.
  • Applying analytical skills to spot patterns and trends in raw data stores.
  • Develop published and interactive drill down dashboards that allow management to slice information into various perspectives.
  • Employ technologies that transform raw data into data warehouse repositories that facilitate self service reporting, and on-demand analytics.
  • Managing data dictionaries.
  • Working with Software Developers to ensure that data is stored in a manner that facilitates the goals of B.I.
  • Utilize technologies like Crystal Reports, Business Objects, and/or anything that you recommend.
  • Utilize technologies like Adobe Flex, Excelcius, etc... to build B.I tools.

Requirements:

  • Greencard or U.S Citizenship.
  • Telecommute not available.
  • Associates or Bachelors Degree.
  • Organized and detailed oriented.
  • Fast learner, with an ability to adapt (self motivated).
  • Ability to analyze raw information, someone analytical in nature.
  • Strong verbal and written communication skills.
  • Strong ability to understand how a particular business functions.
  • Strong financial analysis.

Technical Proficiencies:

  • Strong Database skills in the areas of SQL, Stored Procedures, Views, and Schemas.
  • Strong understanding of Crystal Reports and the Business Objects platform.
  • Strong understanding of Database fundamentals.
  • B.I/Reporting related technologies such as OLAP, OLTP, MS SQL Reporting Services, MS SQL Data Transformation Services.

Salary:

  • Dependent on Skills and Experience

Work closely with customer (ORHP)...

As a consulting company, this position entails working closely with ORHP throughout the entire life cycle of the project. From the inception of the project where problem definition and business requirements are defined, to agile development and release, and assisting in achieving project ROI.

Influence change...

Working directly with our customers, often interfacing directly with the President and Executive staff, this position is a highly visible one. The ability to completely influence from a corporate level the processes and policies of technology and business management are very prevalent.

About Amcom (http://www.amcomtech.net)

Amcom Technology is a member of the Amcom companies located in the San Francisco Bay Area (San Ramon). The company provides premium end-to-end technology services from Web 2.0/RIA software development, to network infrastructure and business intelligence.

Amcom consists of two other divisions which include Amcom Data Processing (ADP) and Amcom Computer Services (ACS).

Amcom Data Processing was founded on April 1, 1973, and has been involved with the oil industry since 1978 with distributors and retail operations. The company currently has relationships with six major oil companies and specializes in the premiere software solutions for the Service Station industry.

Amcom Computer Services focuses on outsourcing, facilities management, programming, training, and supporting computer systems of small and medium sized businesses.

About ORHP (http://www.orhp.com)

Amcom Technology is a member of the Amcom companies located in the San Francisco Bay Area (San Ramon). The company provides premium end-to-end technology services from Web 2.0/RIA software development, to network infrastructure and business intelligence.

Amcom consists of two other divisions which include Amcom Data Processing (ADP) and Amcom Computer Services (ACS).

Amcom Data Processing was founded on April 1, 1973, and has been involved with the oil industry since 1978 with distributors and retail operations. The company currently has relationships with six major oil companies and specializes in the premiere software solutions for the Service Station industry.

Amcom Computer Services focuses on outsourcing, facilities management, programming, training, and supporting computer systems of small and medium sized businesses.

Contact:

  • jobs @ amcomtech.net

 Meta Data Layer - the key to Agile Business Intelligence

Reflecting on the B.I industry as it relates to Agile methodologies: BI tools are not keeping up with user demands The BI life-cycle is a bit different from a software SDLC.

Agile development works best when you can apply object oriented designs that allow for the separation of concerns. This gives developers the flexibility to build and design applications without needing to worry about the data layer. BI on the other hand is all about the data layer.

One of the problems is the mix and match of multiple technologies – ETL, Meta Data, Reporting, Dashboards, Adhoc queries, and so on – each is/has its own development environment.

As part of B.I evolution thus far big companies have gobbled up products and now market them as integrated BI suites. Take SAP for example, which started off with ERP software, then bought Xcelsius and Business Objects, who had bought out Crystal Decisions. SAP now bundles all those products into what is marketed as a "single integrated" BI suite, but what you end up with are several programs that are forced to work together when they weren't designed to do so.

It is very difficult to follow Agile methods when you have to manage each component of your BI solution with a different set of tools.

This is why Meta Data is key – Meta Data allows you to become more Agile with BI because it allows for report development against one integrated source. The Meta Data layer, for example, could be made of multiple databases, data warehouses, text files, xml, etc. – but the report developer sees only the integrated Meta Data.

MicroStrategy is one enterprise BI suite that allows you to manage the entire BI lifecycle within one single environment. Report development and adhoc querying is extremely fast. Some of the new open source offerings make the same claim, but are they there yet?

Still, these products only work effectively if you can map to your data sources, which is why a data warehouse will be very important, regardless of what tools are used . As that database matures it becomes your single point of access to trusted data for BI.

The challenge many face going forward is providing "unstructured, free-form exploration and analysis [of data]... [for] real-time business questions." Right now, that means writing a lot of SQL.

Business Objects and MicroStrategy throw out of a lot of freebies and low priced alternatives to meet needs for 'canned' reports – but they also have tools that allow for unstructured data analysis. These tools basically generate SQL on the fly based on Meta Data definitions, but don't come cheap.

Business Objects Edge gives you some of that functionality, at around $25K – but it's not easy to use, you're still having to 'develop'. MicroStrategy on the other hand remains at the top of the list in terms of user acceptance and functionality – but then so does its pricing. :)

It will be interesting to see how things continue to develop in the industry.

 When ColdFusion Report Builder seems to mangle reports

We were working on an existing ColdFusion Report Builder (CFRB) report, and it ran fine and all that. But the mere act of just saving it seemed to corrupt the reports display.

What we found was that some of the item/fields don't retain all the metadata that they're supposed to. Turned out they were set to not visible or they were set too small.

Additionally another issue is that your desktop needs to have all the fonts utilized by the report.

Thanks to Jon Hirschi for figuring this out!

 TJ Downes on Flex RSLs

Amcom Technology's TJ Downes posted an in-depth article on RSLs, what they are, and tips to troubleshooting problems.

In this post we are going to examine the ins and outs of Runtime Shared Libraries. Runtime Shared Libraries (RSLs) are compiled libraries of code that allow you to cache functionality into the browser or Flash Player. One of the most common uses of RSLs are the Framework RSLs included in the Flex Framework, used to cache the Flex Framework. This can potentially speed load times. These Framework RSLs are the topic of today's post.

Read the full article...

 Sr. Business Intelligence Engineer position available

Position: Sr. BI Engineer
  • Job Type: Full Time, direct hire only.
  • Telecommute: Not available.
  • Location: San Ramon, CA.

Description:

Business Intelligence (B.I) is about getting the right information, to the right people, at the right time.

It provides the ability to connect the dots by taking raw information, analyzing it for patterns, and transforming it into human readable consumption. It enables management to have a constant pulse on the business, as well as being able to monitor changes in customer behavior, market conditions, the regulatory environment, and corporate position vs. competitors. By eliminating guess work the company can execute with a tactile competitive advantage.

Amcom Technology is currently looking for a Sr. Business Intelligence Engineer who can work with our customer (Old Republic Home Protection) to define their Business Intelligence needs, and then implement those needs.

The position requires someone passionate about growing their skills, and in doing so the opportunity to tailor ones career is ever present. Since the B.I Initiative is a relatively new service in our organization it allows anyone coming into this position to help define the vision, and take the B.I platform to as far as their imagination and desire can go.

Responsibilities Include (but not limited to):

  • Working with Executive Management to understand the information they need to drive the business.
  • Applying analytical skills to spot patterns and trends in raw data stores.
  • Develop published and interactive drill down dashboards that allow management to slice information into various perspectives.
  • Employ technologies that transform raw data into data warehouse repositories that facilitate self service reporting, and on-demand analytics.
  • Create forecasting models.
  • Managing data dictionaries.
  • Working with Software Developers to ensure that data is stored in a manner that facilitates the goals of B.I.
  • Define the long term B.I vision, and the roadmap to achieve it.
  • Utilize technologies like Crystal Reports, Business Objects, and/or anything that you recommend.
  • Utilize technologies like Adobe Flex, Excelcius, etc... to build B.I tools.

Additional opportunities:

  • Because we're a small organization, wearing many hats in addition to your primary role is common.
  • Additional roles can include SQL Server Database Administration, and/or Web Application Development.

Requirements:

  • Greencard or U.S Citizenship.
  • Associates or Bachelors Degree.
  • Understanding of Programming Concepts.
  • Organized and detailed oriented.
  • Fast learner, with an ability to adapt (self motivated).
  • Ability to analyze raw information, someone analytical in nature.
  • Strong verbal and written communication skills.
  • Strong ability to understand how a particular business functions.
  • Strong financial analysis.

Technical Proficiencies:

  • Strong Database development skills in the areas of SQL, Stored Procedures, Views, and Schemas.
  • Strong data architecture skills.
  • Strong understanding of Crystal Reports and the Business Objects platform.
  • Strong understanding of Database fundamentals.
  • B.I/Reporting related technologies such as OLAP, OLTP, MS SQL Reporting Services, MS SQL Data Transformation Services.
  • Software development skills.

Salary:

  • Dependent on Skills and Experience

Work closely with customer (ORHP)...

As a consulting company, this position entails working closely with ORHP throughout the entire life cycle of the project. From the inception of the project where problem definition and business requirements are defined, to agile development and release, and assisting in achieving project ROI.

Wear many hats...

The primary role is that of engineer (80%-90%); however wearing many hats is a must. The various hats that can be worn, but not limited to, include: business analyst, DBA, information architect, B.I platform specialist, etc...

One of the advantages of working with Amcom is that your position can be custom tailored.

Influence change...

Working directly with our customers, often interfacing directly with the President and Executive staff, this position is a highly visible one. The ability to completely influence from a corporate level the processes and policies of technology and business management are very prevalent.

Leadership at all levels...

The position is a time demanding one, and requires someone naturally diligent, highly organized, self-motivated, extremely focused, and has a "do whatever it takes to win attitude. Amcom believes in leadership at all levels - we expect people to step up and lead in their areas.

Opportunities for growth...

As you grow we grow. All members of the team are critical to the growth of the company, and with that growth comes new opportunities. The further you progress with your talents and skills, the bigger the goals we're capable of achieving.

About Amcom (http://www.amcomtech.net)

Amcom Technology is a member of the Amcom companies located in the San Francisco Bay Area (San Ramon). The company provides premium end-to-end technology services from Web 2.0/RIA software development, to network infrastructure and business intelligence.

Amcom consists of two other divisions which include Amcom Data Processing (ADP) and Amcom Computer Services (ACS).

Amcom Data Processing was founded on April 1, 1973, and has been involved with the oil industry since 1978 with distributors and retail operations. The company currently has relationships with six major oil companies and specializes in the premiere software solutions for the Service Station industry.

Amcom Computer Services focuses on outsourcing, facilities management, programming, training, and supporting computer systems of small and medium sized businesses.

About ORHP (http://www.orhp.com)

Amcom Technology is a member of the Amcom companies located in the San Francisco Bay Area (San Ramon). The company provides premium end-to-end technology services from Web 2.0/RIA software development, to network infrastructure and business intelligence.

Amcom consists of two other divisions which include Amcom Data Processing (ADP) and Amcom Computer Services (ACS).

Amcom Data Processing was founded on April 1, 1973, and has been involved with the oil industry since 1978 with distributors and retail operations. The company currently has relationships with six major oil companies and specializes in the premiere software solutions for the Service Station industry.

Amcom Computer Services focuses on outsourcing, facilities management, programming, training, and supporting computer systems of small and medium sized businesses.

Contact:

  • jobs @ amcomtech.net

 Windows 7 64bit and a crashing Citrix XenApp Client

We're starting to use Windows 7 64bit for Developer Desktops (well, three of us so far that are willing to be early adopters).

One of the things we had a problem with was logging into a Citrix remote-desktop; it would give the user a "CDViewer has stopped working". Here are the diagnostics:

Problem signature:

  Problem Event Name:                        CLR20r3
  Problem Signature 01:                       cdviewer.exe
  Problem Signature 02:                       11.2.0.31560
  Problem Signature 03:                       4aac18be
  Problem Signature 04:                       DesktopViewer
  Problem Signature 05:                       11.2.0.31560
  Problem Signature 06:                       4aac18ba
  Problem Signature 07:                       105
  Problem Signature 08:                       80
  Problem Signature 09:                       System.IO.InvalidDataException
  OS Version:                                          6.1.7600.2.0.0.256.48
  Locale ID:                                             1033

It has something to do with the version of Citrix that we're using on the server vs. the latest client version. The workaround is rather simple, in Windows 7 you need to associate the ".ica" extension with C:\Program Files (x86)\Citrix\ICA Client\wfica32.exe program.

 Microstrategy 9: Carving A New Dimension in Proactive Business Intelligence

Business Intelligence, in practice, provides decision makers with informative, robust reporting on the functioning of their business. It has evolved over time, but it is still a fairly new concept. It's based on the idea that aggregation and summarization of operational data into meaningful information can drive business decisions and steer an organization to optimal performance.

How this is done:

An organization needs to evolve to get to a level where their key decision makers are acting proactively to steer the company in the most profitable direction.

This means establishing a data collection framework built around the operational needs.

Then, identifying the key roles in the business and how they use the data to make decisions in their business. This means sitting with a financial analyst to understand what they are looking for, and then HR.

SAAS, or software as a service is the concept of adapting software to meet the needs of the business, and then leverage off the software tools to drive the business forward. For example, this could mean providing access to initial reports, and alerts and then changing their definition based on how they are used.

Then taking those definitions and making them the criteria for reporting, alerting, and analysis.

Decision makers shift from operations management to looking at the big picture; or how operational transactions roll up into aggregations of information that are used to determine suitable thresholds, acceptable variances, and financial forecasting. They should be planning their company's future and be able to preplan. For example, a decision maker should be able to create scenarios and determine profit and loss based on trends found in their company's data.

How efficient is it for a store owner to have to call up customer support every time they want to create a custom report, or whenever they want to explore a new "what if" scenario?

Its slow b/c this requires a new report to be generated, a database change... It costs money every time b/c staff needs to fulfill the request.

What if the time and cost to produce new aggregate reports is eliminated?

It can be. Tools like Microstategy 9 work off of existing datasources to create interactive reporting systems that are limited only by what the end user is privileged to view. The end user has all the functions, access to pertinent entities, visual tools to get meaningful reports and forecasting based on scenarios,

Reports can evolve to strategic planning tools for each part of the organization, ie Finance and HR.

If the end user can customize there reports to suit their needs, and evolve there data aggregations specific to their organizations in a timely manner, they can implement quick and relevant change to their business.

How does Microstrategy and similar tools give report creating power to the user? The key is the Symantic layer. This is where we define the meta data, the definitions of the entities and their relationships in our data model. We create the relationships that are the "truths" in the system. The relationships defined here become the foundation for all reports, ie derived SQL statements to create accurate correlations. A problem in the report is more accurately a problem in the meta data definition.

In theory, since the symantic layer contains the rules for our data object model, when we change an entity's definition here, the change rolls up into all the other layers of the system and is transparent to the user.

The symantic layer is built on top of and existing datasource. It could be a database, a data warehouse, a data mart, an excel file, a tab delimited file.

Another great option that Microstrategy 9 provides is the ability to build a single symantic layer on top of multiple data sources (multi-source OLAP). As long as we define their "truth" in the meta data, we can successfully create relationships across data sources. This is not a free piece of the product. It is very useful. Say the direction of the business is to build a data warehouse to product and agregrate data in a certain way. It takes months to establish this warehouse. In the meantime the business doesn't stop and the user can begin creating cubes, or inter related subsets of data that can suffice for analysis until the data warehouse is established. For example, a cube can look at any relationship over a given time...and then later across the cube itself and say a different data source (as long as the meta data defines the relationship)

Microstrategy eliminates the need for an ETL tool because with a single symantic layer pointing at multiple sources, the user can go analyze seamlessly across them. The fact that there's multiple sources is transparent to the user.

Another key factor is privileges. Finance should not have access to payroll data. Defining privileges limits what users can see and also helps them hone in on whats most important in their part of the business.

What does this mean?

No more need to print out reports. Reports can be delivered the web browser. Plugins are also available for accessing the data on MS Office products, MS outlook, smart phones... A dashboard component can be purchased. It is a flash application delivered to the user that gives them access to data off-line.

An alerting tool can be added on. The user defines alert triggers, it could be an event or if a certain threshold is exceeded. In essence, the user always has access to their data.

Your development staff no longer changes user reports. Instead, they work on managing the meta data that feeds the reports. They also make sure that their privileges provide users exactly what information they need to implement change in their organization.

What's next?

Microstrategy is offering 100 users free access to support and the basic pieces of their BI tool. It's a great opportunity for Amcom to explore how a tool like it can make our product and client proactive in terms of optimizing their business.

It also cuts cost because it eliminates the need for staff to support analysis and report changes.

Cost is a critical matter to consider. Upfront cost may save the company money in the long run since maintenance costs will be much lower than any home grown system. In terms of ROI, an out of the box product probably delivers a higher return in the long run, time is a crucial factor.

 Importing Excel Spreadsheets. The Easy Way

Recently I was asked to import data into our system using a spreadsheet provided by the client. There are, of course, numerous ways to go about this, including using tools built into SQL Server (DTS/SSI). However, I wanted to provide tool that the client could use to perform these imports themselves, in the future.

The obvious choice was a web-based tool. The client is used to web-based interfaces and I could also leverage some new functionality in ColdFusion 9. I knew that ColdFusion now has the cfspreadsheet tag, and it was a great opportunity to test this.

The spreadsheet provided to me was over 42,000 rows of data. This seemed like a lot for a spreadsheet! After reading through the documentation on the cfspreadsheet tag, I determined, for my use, that converting the contents to a query object was probably going to make it easiest to work with. I simply provided a form for the client to upload the Excel file to the server. Once the file was uploaded, I used the following to process the file and convert it to a query object:

<cfspreadsheet action="read"
   src="#variables.uploadedFile#"
   query="mySpreadsheet"
   headerrow="1"/>

I'll explain each of these attributes in minor detail:

action - This is what type of action we want to perform on the spreadsheet. The choice are read, update and write.

src - The source of the Excel spreadsheet. In my case, this is a dynamic value, but does require an absolute path!

query - The name I am giving to my query object for future reference headerrow - the row number in the spreadsheet which contains the column names

I'd like to point out that if you do not define headerrow you will need to reference your column names as such: col_1, col_2, etc. If you do provide the headerrow attribute you MUST reference the columns by name.

Now referencing your spreadsheet data is simple! If you want to output the data to the screen, simply use cfoutput like you normally would for a query!

<cfoutput query="mySpreadsheet">
#column1# #column2#<br />
</cfoutput>

It's that simple!

Instead of creating a query object you also have the ability to create a csv or html from your spreadsheet. to do this, using the following

<cfspreadsheet action="read"
   src="#variables.uploadedFile#"
   name="mySpreadsheet"
   headerrow="1"
format="html/csv" />

By leaving out the format attribute, it will return a string. Regardless of which format you choose, you can output the data to the screen like this:

<cfoutput>#mySpreadsheet#</cfoutput>

As you can see, the cfspreadsheet tag has made working with Excel files much easier!

I would like to point out a couple of caveats I experienced while working with the cfspreadsheet tag:

1. There's a bug, which I have yet to report, but will do soon. Essentially, if your spreadsheet contains pound signs (#), the read will fail. Hopefully this will get fixed soon.

2. Spreadsheets with large amounts of data, such as my project with 42,000+ rows, may completely fail when using HTML or CSV format. I have a fairly beefy machine (multi-core, 6GB RAM) and could not get mine to process the file in HTML or CSV format. Your mileage may vary.

Overall, it looks like cfspreadsheet is a very valuable addition to the CFML language. Hopefully it will get updated over time to include other spreadsheet formats too, like Open Office! It certainly saved me considerable time in my project and will prove a valuable tool for the client for future use. Also, I know how to leverage this for future projects now, time well spent!

More Entries

BlogCFC was created by Raymond Camden. This blog is running version 5.9.002. Contact Blog Owner