Very interesting, though I haven't grasped well yet.

In this case the workflow goes like this:
1) Customer remote office prepares data in csv format using Excel. They may open and save file several times.
2) Customer main office may also open, modify and save same file.
3) Customer main office then imports csv file into our application. We use APPEND FROM FILE xxx DELIMITED.

The problem at hand is how to continue using CSV file format, which is practical, allowing user to open and close file with Excel at will yet preserve leading zeroes in a particular data column.

Thanks for your help.

Alex


>

>#define xlWorkbookNormal -4143
>
>#DEFINE xlDelimited	1
>#DEFINE xlFixedWidth	2
>
>#DEFINE xlGeneralFormat	1
>#DEFINE xlTextFormat	2
>#DEFINE xlMDYFormat	3
>#DEFINE xlDMYFormat	4
>#DEFINE xlYMDFormat	5
>#DEFINE xlMYDFormat	6
>#DEFINE xlDYMFormat	7
>#DEFINE xlYDMFormat	8
>#DEFINE xlSkipColumn	9
>#DEFINE xlEMDFormat	10
>
>
>TEXT TO myVar noshow
>0001,VFP7,01/01/2002,skipme,30/01/2003,001
>0002,VFP8,01/31/2003,skipme,29/02/2000,002
>ENDTEXT
>lcFileName = fullpath('ExcelOpenTextTest.txt')
>STRTOFILE(myVar, lcFileName)
>
>dimension arrFldInfo[6,2]
>for ix=1 to 6
>  arrFldInfo[m.ix,1]=m.ix
>endfor
>arrFldInfo[1,2]=xlTextFormat
>arrFldInfo[2,2]=xlTextFormat
>arrFldInfo[3,2]=xlMDYFormat
>arrFldInfo[4,2]=xlSkipColumn
>arrFldInfo[5,2]=xlDMYFormat
>arrFldInfo[6,2]=xlTextFormat
>
>oExcel = createobject('Excel.Application')
>with oExcel
>  .Workbooks.OpenText(m.lcFileName,,,xlDelimited,,,,,.t.,,,, @arrFldInfo)
>  .ActiveWorkBook.SaveAs(ForceExt(m.lcFileName,'XLS'),xlWorkbookNormal)
>  .ActiveWorkBook.Saved = .T.
>  .visible = .t.
>endwith
>

Cetin
>
>>>Alejandro,
>>>CCing I assume you read my reply, if not read it.
>>>Cetin
>>
>>Apologies Cetin, I did read it and should have answered separately. Saving with extension txt (tab delimited) works. If user needs to open table again with Excel they go through the dialog where they indicate the type of data in the column. I discarded txt extension (tab delimited) because I wanted something a little simpler and bulletproof for my user. Sorry for not sending a better answer before.
>>
>>Alex
>>
>>>
>>>>Thanks. That works fine until they save the file and open it again (as they would if they need to make a correction).
>>>>
>>>>We may be stuck here. Maybe we can allow them to add a character not in any key (such as ":") as the first char if key has leading zeroes and then ignore it or a trailing alpha char beyond the 6 chars that form the key.
>>>>
>>>>Thanks.
>>>>
>>>>>They should not "format" the numeric data in the column, but make a number stored as a text:
>>>>>
>>>>>'000102
>>>>>
>>>>>Quotation mark first!
>>>>>
>>>>>
>>>>>>A customer provides some information to our app in CSV files which they prepare using Excel. One of the columns has a key value which is a 6 char string. Some clients have defined keys with all digits such as "000102". If they format the column in Excel as text the file is saved with the full key, but if they happen to open the file again with Excel the value is interpreted as numeric and the leading zeros are lost.
>>>>>>
>>>>>>Is there any solution to this?
>>>>>>
>>>>>>TIA,
>>>>>>
>>>>>>Alex