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 ?
Answer by tonyunions25 · Oct 24, 2013 at 09:06 PM
Answer by PaulD · Jun 15, 2010 at 06:56 PM
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
Answer by edwards142 · Jan 13, 2020 at 05:39 PM
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
How do you open a device? 2 Answers
Importing testbed data - passwords? 1 Answer
Unknown Character after variable 4 Answers
How to take snapshot of current view using TCL scripting for ixNetwork 1 Answer
distinct value from response map 2 Answers
Copyright 2008- Spirent Communications, all rights reserved. Terms and Conditions.