An issue exporting a db into a text file with delimiters ";"

[nacho]

Hi everyone. I'm a new beginner in this Progress thing, and i am a little bit lost with a problem that is easy for sure, but turn out to be difficult for me due my level of expertise in 4gl.

I want to export a db of QAD that contain a lot of fields, and I want it to be a text file with delimiters ";", so I can import it on Excel or OpenOffice like if it were a simple CSV file. The problem is that I made a program that do exactly that, exporting all the data without any problems, but when I open the file in Excel turns out that the fields that are numbers (DECIMAL's for example) have been turned into CHARACTER, so, when I tried to do some math's operations to that fields (sum all for example), it only takes some of the values, but not all of them.

I try to do something like EXPORTing the values in this way:

EXPORT STREAM output DELIMITER ";"[INDENT]field1
field2
...
DECIMAL(fieldN-1)
DECIMAL(fieldN)
[/INDENT]END.

But that doesn't seem to work.

I noted that if I take the CSV (or .txt) generated by the program, and change the . (dots) corresponding to the numeric values to "," (comas), Excel recognize them like numbers, and can work with them. Is there any way that I can EXPORT to the file using comas instead of dots for the numeric values?

Thanks a lot, for reading this and for your replys.
Greetings from Chile,
Nacho

PS: is not that simple to take the file and "find and replace" all the dots to comas, cause the names and descriptions involved into the others fields have info that contain that kind of punctuation.

 

[tamhas]

You could always use REPLACE on the export fields, but this sounds to me like an issue with thousands and decimal separators per national practice and having the application and Excel out of sync ... which sounds confusing to me.

 

[nacho]

You could always use REPLACE on the export fields, but this sounds to me like an issue with thousands and decimal separators per national practice and having the application and Excel out of sync ... which sounds confusing to me.

Thanks for the reply. I will try now to use REPLACE on the EXPORT statement, and let you know.

It might be a problem of national practice, because we use comas for delimiters in decimals, and dot for delimiters in thousands.

I will let you know. Greetings.

/*** EDIT ***/

I solve this doing something like:

ASSIGN field_t = REPLACE(STRING(field), ".", ",")

EXPORT STREAM output DELIMITER ";"[INDENT]field_t
[/INDENT]END.

I hope this will be useful for someone else.
Greetings,

 

[tamhas]

The question is, why isn't the application and Excel consistent in national setup ... seems confusing otherwise.

 

[nacho]

The question is, why isn't the application and Excel consistent in national setup ... seems confusing otherwise.

Yes, I know. The fact is that this is not an "export to excel", like the option bundled in the application it self. This is a program made by me ("program.p") that do the creation of a file that can be read in Excel. In fact, QAD is exporting without any problem to an Excel file; the problem with it is that I have to export to Excel in "small pieces" of 50.000 registers, which is the QAD limitation for that kind of defaults exports. The other limitation is that Excel is capable of only take 65k+ registers at the same time; for that reason, we are importing the file to Access, which is the program that our salesman work best with.

The localization issues weren't wrong, it's only that we want (accomplished right now) to export to a text file without having the limitation in the number of registers that can be exported, because we made a lot more than 65k+ registers in a month, especially in the summer (we sell ice cream and snacks).

Thanks a lot for your replays. Indeed, they motivated me to search the real problem behind this issue.

Greetings from Chile!

 

[TomBascom]

I think you're missing Tamhas' point.

Excel wants "," for a decimal separator but your Progress session is using ".".

This is a Progress session property. You can change it either by using the -numdec startup parameter or by modifying the SESSION:NUMERIC-SEPARATOR property at run time. Either option is much simpler (and more robust) than goofing around with REPLACE.

 

[nacho]

I think you're missing Tamhas' point.

Excel wants "," for a decimal separator but your Progress session is using ".".

This is a Progress session property. You can change it either by using the -numdec startup parameter or by modifying the SESSION:NUMERIC-SEPARATOR property at run time. Either option is much simpler (and more robust) than goofing around with REPLACE.

Wow, I think that was what I was looking for. I'm a total newbie in Progress, so I doesn't know that this kind of parameters exist (such an obvious thing, I don't know how I didn't think of something like that at the first time).

Thanks a lot, both of you. I going to explore more in the systems options now.