Converting data 2

[carolynh]

I am working on a data conversion project from one piece of software (which uses flat files) to another piece of software which is Progress based. We dump a lot of data from the original software to a PostgreSQL db via a web based product. I would like to take the data that is in the SQL table and move it to my Progress tables (painlessly). Is there a (free) SQL or Progress tool set that would be able to help me with this project or has someone already done this that could give me some pointers/advice.

 

[harebrain]

Low-tech: create a flat file by selecting from Postgress in quoter format, then use Progress import to read the data.

 

[carolynh]

I tried that but I have to do some data cleansing first. The data in PostgrSQL needs to be converted somehow before I can import it into Progress. Like fore example the address fields.

 

[harebrain]

Um, well then you have two choices. Either clean-up the data in the Postgress DB or clean-up the flat file after exporting. There are no data elves that will fix your data while you sleep.

 

[carolynh]

Could you tell me how to read the file in once I get it into a 'cleaned' .txt file. I know I have to import it into a temp table, but I can not find any good documentation that tells me how to do this.
Thanks

 

[tvrtko]

Try this

INPUT STREAM File FROM VALUE(YourFileName) NO-ECHO NO-CONVERT BINARY.

REPEAT WHILE TRUE:
IMPORT STREAM File UNFORMATTED cTempLine.

Now you have the line and you should put it's content into your temp table. How you are going to do that depends about your file. For tab delimited file:

ttTable.Name = ENTRY(1, cTempLine, CHR(9))
ttTable.Age = INTEGER(ENTRY(2, cTempLine, CHR(9)))

etc....

END.
STREAM File CLOSE.

 

[carolynh]

tvrtko
what if you are reading in a comma delimited file? How would you set your fields in the temp-table?
Thanks

 

[harebrain]

That's even easier. Your import statement becomes:

IMPORT STREAM File DELIMITER "," field1 field2 ... fieldn.

This is quite well documented in the Language Reference.

 

[tvrtko]

I think that harebrain's solution is ok, but you can also do this:

ttTable.Name = ENTRY(1, cTempLine, ",")

or even just:

ttTable.Name = ENTRY(1, cTempLine)

because comma is default delimiter for ENTRY() function.

 

[harebrain]

tvrtko,

Don't you consider it rather silly to parse the input line yourself when the IMPORT statement will do it for you? Also, I'd bet (although I'm not willing to create a benchmark) that a delimited import to variables is faster than importing a line and performing repeated calls to string functions to extract the values from the line.

Work smarter, not harder.

 

[tvrtko]

harebrain, you're 100% right, I was just giving a option...

 

[carolynh]

I am importing my comma delimited text file in and dumping it to a temp table. It has 13 lines (records) in it. But when I do a For each on my temp table it says that I have 14 records in it (imp-cnt = 14) and I init imp-cnt to 0. How do I get it to stop reading if its at the end of the file?

DEF TEMP-TABLE tt-terms
FIELD tt-type AS CHAR /* 1 */
FIELD tt-conum AS CHAR /* 2 */
FIELD tt-terms AS CHAR /* 3 */
DEF STREAM str-terms.
INPUT STREAM str-terms FROM VALUE("C:\ts54\Conversion Files\TS Import\AR\AR_TERMS_CODES.txt") NO-ECHO NO-CONVERT BINARY.
REPEAT WHILE TRUE:
CREATE tt-terms.
imp-cnt = imp-cnt + 1.

IMPORT STREAM str-terms DELIMITER ","
tt-terms.tt-type /* 1 */
tt-terms.tt-conum /* 2 */
tt-terms.tt-terms /* 3 */
END.
MESSAGE imp-cnt VIEW-AS ALERT-BOX.

 

[HelloMike]

Instead of REPEAT WHILE TRUE, just REPEAT.

 

[carolynh]

Just using the REPEAT alone does not work either. I still get a blank record.

 

[richtpt]

Been there, done that, seen it many times. I was told many years ago to use REPEAT TRANSACTION ON ENDKEY UNDO, LEAVE:

In v6 that worked great, but doesn't seem to work right anymore. Now anytime I import, when I leave my repeat loop, I simply do a DELETE temp-table. I know there's a better answer, but this always works for me.

One of these years I'll take the time to understand this better. Wish I had a better answer for you. Oh, and there's no difference here between repeat and repeat while true.

Hope that helps!

Rich

 

[carolynh]

Rich
Thanks again! I knew I used to do something to get rid of that last blank record, but could not remember what it was. I remember using the REPEAT TRANSACTION ON ENDKEY UNDO, LEAVE: years ago and tried that, but it did not work either (using V9 now), but now that you mention it; I do rember using the DELETE as well.
Thanks again
Carolyn