LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jhoitt

What is the status of a node that works with Excel 2007?

The company I am working with produces thousands of files in Excel 2007 and it isn't feasible for me to manually convert XLSX files to CSV's.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

This is one of the reasons the Excel File node is still in experimental, unfortunately. The library that we use to implement the Excel node currently does not support Excel 2007; however, it is being developed, and there is an alpha version, so progress is being made. I can't make a commitment as to when it will be released, but know that when it does, we will incorporate those updates to our product in the next maintenance release.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: henk01

It seems that there are some batch utilities available on the "world wide web" to convert from xlsx to xls, which may be useful here as a work around.


Perhaps obvious, but DISCLAIMER: the purchase or use of third party utilities is entirely at own risk, etc. etc.......


It is good to know the work on Excel 2007 is on-going. I'll look forward to this "xlsx" node being available!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: bds

I haven't used the current experimental excel reader node and really need an example showing how to use the node to read an example spreadsheet. Given you seem to have done quite a bit of work with this node, I wondering whether you might have an example (not to complex) to share. Then I could use this as a template for my current task and later tasks.

Thanks Brett

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Sure, post an example Excel file, and I will post the Excel node to read it.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: bds

Hi Rich,
I attached the spreadsheet I need to read.

Regards,

Brett

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

Brett, I ran this against the Excel File node in Experimental that shipped with 4.1.6, and I didn't have to do anything to the node except point the filename parameter to the path where the xls file was.

Did you try the node? Did you have any issues? Did you check out the documentation? Not sure what you were having problems with, and why it wasn't reading for you. Can you post the errors you were getting? If you were trying to put something in the WorkbookSpec Param (it wasn't needed, the default worked), please post that as well.

Also I deleted your attachment. I'm not sure if the xls file you posted was obfuscated; it looked like it might not have been, but I assume it was. I was just being extra safe, since it was posted to the public forum, not MDA private.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mzhao

When the worksheet is read in, all columns are changed to unicode. It is possible to indicate the required type for each column? Say to read column 1 as integer, column 2 as double, column 3 as string, etc.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rboccuzzi

No, currently all data comes in as string or unicode. This will likely remain in the foreseeable future as well; Excel can have different types mixed in columns...
Inside the LAE, once the data is imported, you can inspect the data, and make a decision on a row-by-row basis. The Data Analyzer works very well to solve this, and I frequently put one right after the Excel node, and it does a pretty good job of figuring out what the columns should be. There is a bug in the currently released Data Analyzer however, that makes it treat Unicode poorly; this will be addressed in the 4.2 release.

Cheers
Rich

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Daniel Stephenson

Originally posted by: henk01
					

It seems that there are some batch utilities available on the "world wide web" to convert from xlsx to xls, which may be useful here as a work around.



Perhaps obvious, but DISCLAIMER:  the purchase or use of third party utilities is entirely at own risk, etc. etc.......



It is good to know the work on Excel 2007 is on-going.  I'll look forward to this "xlsx" node being available!
			

Guys, Any updates on a new node to import .xlsx files?

thanks, Daniel

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Guys, Any updates on a new node to import .xlsx files?


thanks, Daniel

A new fully supported (non-experimental) version of the Excel File node, will be part of LAE 4.5.2 - the next LAE maintenance release on the 4.5 branch. This will handle both .xlsx & .xls flies.

This release is in the finals iterations of regression testing at the moment and should be available sometime before the end of the month.

When the worksheet is read in, all columns are changed to unicode. It is possible to indicate the required type for each column? Say to read column 1 as integer, column 2 as double, column 3 as string, etc.

Unfortunately, there will still be no mechanism for specifying the type on each column. As Rich mentioned, since Excel is cell-based, there is no guarantee that the cells within a column will all have the same format. This means that there is no way to auto-detect the column type. If the user where to specify the column type within the WorkbookSpec parameter, there would also need to be parameters governing what the node should do in case any of the cells within the column cannot be converted to the specified type. However, we will consider these enhancement for future releases.

Regards,
Tim.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: rabbott

There's a new Excel File node in LAE 4.5.2 that supports .xlsx files (Excel 2007 and beyond).

LAE 4.5.2 was released on 22-August-11.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: kursattaskin

We are trying to use excel node but it does not import big excel files. It says "ERROR: Insufficient memory to read the Excel file(s)." There is 74000 record not more than that.

Any idea or comment?

Kind Regars,

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: Tim Meagher

Hi,

There is an issue with the Excel File node released with 4.5.2 in that it needs to load the Excel Workbook into memory prior to converting to the LAE internal (BRD) format.

The issue exists up until LAE 4.5.4.

This issue has been raised in our bug tracking system and will be fixed in the upcoming LAE 4.5.5 and LAE 4.6.0 releases.

Until that time, it isn't easy to workaround the limitation.

Probably the easiest approach is to go into Excel and save the file in a CSV format, then use the CSV file node to read in the data.

Depending on the size of the file, it is sometimes possible to use multiple Excel File nodes, with the first node reading the first set of rows, the second node reading the next set of rows (e.g. 30,000 rows per node or something), then joining them together with a Cat node. In order to do this, you would need to specify the rows to read in the WorkbookSpec parameter.
A final alternative is to split the data in the workbook into multiple Excel Files and read each of these separately with an Excel File node in the LAE.

Obviously, none of these alternatives are ideal and are going to be addressed in the next LAE releases in both LAE 4.5.5, and LAE 4.6.0.

Regards,
Tim.