Using readFile on Excel Spreadsheets with multiple Worksheets
I have a single Excel Spreadsheet with 100+ Worksheets.
Is there any simple method of using readFile (on CSV) to pull in and separate out each Worksheet ?
Do I need to go thru a pre-process of creating a set of individual CSV files JUST in order to separate out the individual Worksheets ?
Then readFile them in one-by-one.
Is there a better solution ?
Read excel files by the using of xlsx repair Utilize [
http://www.excel.recoverytoolbox.com][1] [1]:
http://www.excel.recoverytoolbox.com
Excel does not support saving spreadsheets in CSV when they have multiple worksheets.
However, there is a much nicer way to do this. Save your spreadsheet as "XML Spreadsheet 2003". This will save a pure XML document. Then you can use readFile to load that document into iTest. Since it is XML, it becomes immediately queryable in iTest using XPATH and you'll find all of the spreadsheet information in the Structure view of that readFile step.
Then you can extract data using a query that might look like, for example,
/Workbook/Worksheet[1]/Table/Row[2]/Cell[3]
Note that there is a bug open about dealing with namespace-aware XML documents. So if you try to refer to attributes (like ss:Name or ss:Type or x:Margin) in your query, you will have problems. We're looking into that.
Nice Solution ! Works perfectly ! Cheers !
Will keep an eye on the namespace issue.
Has the namespace bug been addressed? Even in 4.1.4, this still seems to be a major impediment to reading Excel XML Spreadsheets.
Thanks!
Jason
I know Excel does not support saving spreadsheets in CSV when they have multiple worksheets. So, I prefer to use Google Spreadsheet for exporting Excel file to CSV. I find this simple workaround quiet easy so I choose it. If you have have Google Drive already installed, perform the following 5 easy steps.
1.From the Google Drive, tap to the Create button and then hit the Spreadsheet.
2.Tap to the Import option from the File menu.
3.Hit the Upload option and select the file from PC.
4.Select the Replace spreadsheet option. After then hit on the Import option.
5.Tap to the File menu > Download as, choose the Comma separated values (CSV, current sheet) and then save the CSV file on your computer.
Finally, open up your CSV file any text editor to make a check whether all the characters have saved perfectly or not.
Apart from this if somehow your CSV files got corrupted then also need not to worry. Check out the solution from this post: 5 Tested Solutions to Recover Corrupted CSV Files
