reading excel file

[girijaprasad]

I am girija prasad @ giri,

I am having requirement to read excel file. I used TextFileBeginProcess for reading excel file. then MePro is terminating abruptly and prompting for error reporting. is AsciiFileBeginProcess is suitable or not

Thanks in Advance

regards

giri

[Cory]

The native Excel file is not a delimited format and you can’t do that.

 

Generally people have lists of things and they put it in excel just because it’s a handy way you have a grid. If this is the case I suggest you save the file to CSV instead. This way you can open and edit it on Excel and use ASCII File Process in MEP. However there’s no formatting, no multiple worksheets, and you can’t have it open at the same time.

 

Other alternatives is to have the user copy the range of cells to your clipboard and parse that with a split command in MEP. When you copy like that all clipboard data is inherently a tab separated value format. IE like CSV but using tabs instead.

 

But if you need to access the data in the Excel file format without changing it then you will need to use some VBScript and the External Script command to get to it. I have one I use often that will simply export one worksheet to a temp TSV file that I can deal with. Let me know if you need help, I’m available.

[girijaprasad]

The native Excel file is not a delimited format and you can’t do that.

 

Generally people have lists of things and they put it in excel just because it’s a handy way you have a grid. If this is the case I suggest you save the file to CSV instead. This way you can open and edit it on Excel and use ASCII File Process in MEP. However there’s no formatting, no multiple worksheets, and you can’t have it open at the same time.

 

Other alternatives is to have the user copy the range of cells to your clipboard and parse that with a split command in MEP. When you copy like that all clipboard data is inherently a tab separated value format. IE like CSV but using tabs instead.

 

But if you need to access the data in the Excel file format without changing it then you will need to use some VBScript and the External Script command to get to it. I have one I use often that will simply export one worksheet to a temp TSV file that I can deal with. Let me know if you need help, I’m available.

 

thanks cory, Now I will try for that and complete my work. :-))

[lemming]

Excel files are binary files (i.e. not plain text files), so you can't use AsciiFileBeginProcess on them. You might want to check out XLS2CSV:

 

www.brothersoft.com/xls2csv-69870.html

 

Does exactly as its name says. Small, fast and free. You don't even need to have Excel installed. Once data is in CSV format, it is a lot easier to read with MEP. It works from the command line, so you can call it from within macros with the Program Launch command.

 

I've was using XLS2CSV for a project where I needed to extract data from dozens of xls files every day.

Publisher's description

Microsoft Excel (XLS) file to Comma Seperated Values (CSV) file conversion program. This product helps you to convert your Microsoft Excel files to the standard Readable CSV file format which can be useful for your software or web site.

[lemming]

Generally people have lists of things and they put it in excel just because it’s a handy way you have a grid.

[...]

 

Yea, I've seen this quite often. Usually, they're just storing data with no calculations or formulas. Sometimes there aren't even numbers. On their PCs, they almost always have Word too, so I would suggest to them to use Word's tables and its table wizard instead. Data entry is a lot easier in a Word table, and the tables are very customisable.

[Cory]

I don't agree:) If they use Excel and just change the file format to CSV they can edit and manipulate in Excel and yet naively access it with MEP. Also one can link to such CSV files in Excel. This way you can have a linked source data file in one worksheet and simply reference it in others' of your workbook to do all kinds of formulas and such.

 

However the VBScript to export a worksheet in Excel to a CSV file is dead simple. Create object, set worksheet, export.