Re: Copy ascii file to a table.

dj2_at_alpha1.csd.uwm.edu (Donghong Jiao) wrote:

> I wonder how we can copy the contents
>in a ascii text file into a table.
>Of course, we can give the ascii file a
>format restriction. Any tools available?

I had to do this once, and it was a bit of a pain. The customer wanted to enable the field workers to punch in reports using WP (yuck !) on their laptop and then download it into Oracle. We stored it into a long column so that, using a edit box (Forms 3.0), they could also edit or review the text. Exporting back to ASCII for processing in WP was also a requirement. SQL*Loader can do the loading, if you keep to certain rules:

Since SQL*Loader cannot handle fields longer than 64K, you must restrict the text length to that size (CHAR 65336).

CTL file:
CONTINUEIF LAST !="#" (or another record seperator) specify the column as CHAR(65336)
The text column should be the last column in the datafile and has to be ended by the earlier specified record seperator (#)

The data file looks like this:

column1, column2, column3, long_column_spread_across_multiple_lines# column1, column2, column3, long_column_spread_across_multiple_lines# etc....

To keep the original formatting, you have to change the returns in the datafile to CHR$(10)

It took me a few days to get this up and running (including help from our friends at the big 'O'), but with these tips you could do it a lot quicker.
Hope it works at your place !

+------------------------------------------------------------+
|   Mike Philippens - Gorinchem - The Netherlands, Europe    |
|                 Vijfhart Automatisering bv                 |
|      Oracle Specialists in Training and Consultancy        |
|                 Utrecht    The Netherlands                 |
+------------------------------------------------------------+