Is there a Command line XLS, XLM converter? 1

[Mrall]

I have been reading alot of information about the problems with using the Append command to convert an Excel 2007,2010 file to something VFP can import. The other issue that there seem to be a problem with is trapping for the error that crashes the program if the user trys to import a file that is not saved in the correct format. It seems to corrupt the file it is being appended to. In most cases I can't force the user to do a save as or remember to do a save as in excel.

The easiest way would be to have a command line function that would convert any Excel file into a compatible format that VFP9 can append from. Maybe an XL8 or some other format.

The problem that I have is, that the spreadsheet I need to import, comes via email from many different people who have different versions of Excel. All of the spreadsheets have the same design or layout but different information. The users don't want to deal with doing a save as. Or they just forget too. Setting a different default save format is not an option they will agree with.

A command line converter that detect version infomation would do the trick. Even a window application that can detect which version of Excel is being used and default to a single compatable excel format to convert to.

Thanks for any help

 

[JRB-Bldr]

command to convert an Excel 2007,2010 file to something VFP can import

You can use Excel Automation to perform an Excel SaveAs into a CSV format file.

With the CSV file, you can then do an:
APPEND FROM MyCSVFile DELIMITED

You can first test this by manually going into the Excel file and again manually doing a SaveAs. Then with the manually created CSV file, go into VFP and test your ability to successfully do the APPEND.

If that works fine for you, then merely replace the manual Excel operations with VFP Excel Automation.

In that way you would end up with a routine that would convert any Excel file into a compatible format that VFP9 can append from

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Why do you need a command-line untility? You are writing VFP code, so surely it is easier to do it in VFP, as part of your existing import routine.

You have two basic options:

1. Use Excel Automation, exactly as JRB-Bldr suggests; or

2. Use ODBC; this will allow you to use SQLEXEC() etc to read and write the Excel data.

Both options require that you have Excel 2007 or later installed on the machine that runs your application. If you don't, for the second option you can download and install the Office 2007 ODBC driver from Microsoft.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

You can find out what your VFP Excel Automation code needs to be when you first test this by manually going into the Excel file and again manually doing a SaveAs by recording the actions as an Excel Macro.

Then, when you are done, stop recording the Macro and examine it's VBA code. That VBA code will be a guide to your developing the VFP Excel Macro necessary to accomplish the task.

Good Luck,
JRB-Bldr

 

[Mrall]

First of all, thanks for you input on this matter. I ran into yet another problem. Some of the people using my application don't have Excel at all. I did however, locate a free command line converter that converts any Excel file XLS to a CSV. It doesn't require any version of excel to be installed. It seams that it works with the following verisons of Excel. In their normal saved format.

Excel 95
Excel 97
Excel 2000
Excel 2002
Excel 2007

I'm just not sure how to import the the CSV file. Does anyone have any examples of the process.

Thanks

 

[Mike Lewis]

To import a CSV file into VFP, use APPEND FROM ... TYPE CSV.

You will need an existing DBF file or cursor to import the data into.

You can also use APPEND FROM .... TYPE DELIMITED. There are a few minor differences between the two. Check the Help for details.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[JRB-Bldr]

I'm just not sure how to import the the CSV file

I already gave you the APPEND command above to import the CSV file.

You obviously need to know the Excel/CSV 'columns' so that your recipient data table can have the appropriate fields already in place into which to append the data.

Test it out for yourself.

Also note that your data itself cannot have any commas in it or it will 'confuse' the CSV file contents since commas are what differentiate one 'field' from another. Having extra commas in a 'row' of data will throw that off.

Unfortunately most simple (free??) XLS to CSV converters do not take that into consideration. The result can throw everything off. With that in mind, I'd personally stick with VFP Excel Automation to first remove all commas from the Excel data and then do the SaveAs into the CSV file.

Good Luck,
JRB-Bldr

 

[Mrall]

I thought I would also bring up a few more helpfull or not so helpfull facts.

1) I know how many fields (columns) are in the csv file (good)

2) some of the fields (in the rows) unfortunatly have chr(13) or returns in them (not good) so reading a line at a time may not work out so well.

3) I do know the field names and the max length the field is (I have a table already setup)

4) the csv is ; delimited

5) each field has " around it

ie. "Marty";"married";"10";"";"1235"...

Sooooo, I'm not sure how to read the file. I'm sure I can use a FOR and NEXT loop for the number of fields to read in the field names and dump them. But when the file is read in, does it read till is gets to the chr(13)?(a line at a time) Once I get past the first line(field names) some of the fields have chr(13) in them. How is that going to mess up things?

Yep! It's a challenge.

Thanks for your help

 

[Mrall]

I use the APPEND FROM GETFILE() TYPE DELIMITED WITH CHARACTER ";"
just to see how it would work.

The chr(13) or return located in one of the rows is messing the append up. I don't know how to fix it.

 

[JRB-Bldr]

NOTE - "Marty";"married";"10";"";"1235" is NOT data from a 'true' CSV formated file.

A CSV (Comma Separated Values) format is Comma delimited (sometimes Quote-Comma delimited), not Semi-Colon delimited.

Maybe you want to look for another conversion tool which will convert to a 'true' CSV file.

You might want to look at:
Import CSV with delimiter ';'

http://www.tek-tips.com/viewthread.cfm?qid=786685&page=401

Using baltman's basic approach suggested there you could:
1. Change all commas from the data to something else
2. Change all CHR(13)'s in the data to something else
3. Change all semi-colons to commas
4. Do a 'normal' APPEND FROM MyCSVFile DELIMITED

Good Luck,
JRB-Bldr

 

[Dan Freeman]

A CSV (Comma Separated Values) format is Comma delimited (sometimes Quote-Comma delimited), not Semi-Colon delimited.

<PEDANT>
A comma separated values (CSV) file has the values separated by commas (as the name says) and typically delimited with quotation marks.

Delimeters mark the begin and end of fields. The separator is not the delimiter.
</PEDANT>

 

[Mrall]

danfreeman, you are right.

I am still faced with the reality that the users may or may NOT have Excel and while, the csv does work with the ; (instead of the ,)one of fields has a chr(13) in it, which is the main problem. There may be some way to to use VFP to change the chr(13) but it may require some microsoft driver the the user may not have.

I have looked for a freware find and replace command line, but I am unable to locate one that will remove a return character(chr(13))

 

[Dan Freeman]

OK, wait. If you have something that converts the file to text (no matter what format) VFP can work with it.

You will NEVER find a way to make embedded CR work. Embedded CR will not work with APPEND or IMPORT. It will not. Stop looking.

Once you have a text file, though, you can either doctor (using VFP, not some external tool) the input file so that it will work, or you can change your approach and use low level file I/O to get the thing done.

For the latter, see FOPEN() and related functions in the help file. It'll be ugly. It sure won't be elegant. It'll be brute-force programming. But it can be done.

 

[Mike Lewis]

Regarding the problem of embedded CRs.

If the lines are terminated with a CRLF (that is, CHR(13) followed by CHR(10)), then it should be easy to get rid of the embedded CRs.

First, read the file into a variable, using FILETOSTR().

Next, use STRTRAN() to change all the CRLFs to some arbitrary string of characters that does not appear anywhere else in the file. Let's say, for argument's sake, you change it to ###.

Then use STRTRAN() again to remove al the remaining CRs (or change them to space, for example).

Finally, convert the ### back to CRLF, and write the overall string back to a file, which you can use with APPEND FROM.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mrall]

Thanks Mike that is exactly wat I need to do.