At 06:23 AM 10/12/2011, si_carter_987654321 wrote:

>Hi,
>
>I have created an external table defined as:
>
>CREATE TABLE WS_IPTOCOUNTRY_EXTERNAL EXTERNAL 'c:\...\iptocountry.csv'
>(
> FROMIP BIGINT ,
> TOIP BIGINT ,
> COUNTRY_CODE VARCHAR(2),
> COUNTRY VARCHAR(100)
>);
>
>This works and I can connect to it, the csv file looks like:
>
>0,16777215,"ZZ","Reserved"
>16777216,16777471,"AU","Australia"
>16777472,16777727,"CN","China"
>16777728,16778239,"CN","China"
>16778240,16779263,"AU","Australia"
>16779264,16781311,"CN","China"
>16781312,16785407,"JP","Japan"
>16785408,16793599,"CN","China"
>
>++ several thousand lines extra
>
>when I query I get results like:
>
>FromIP: 3904913403434593897
>ToIP: 3180203630043870513
>Country_code: es
>Country: ved"
>
>or
>
>FromIP: 724272675694793256
>ToIP: 3472898977122693170
>Country_code: ,2
>Country: 0937599,"KR","Korea Republic of" 2070937600,2071986175,"CN","China"
>2071986176,2072510463,"CN","Ch'
>
>
>(I know it doesn't match the input examples above)
>
>What is the correct layout for the csv file, I have tried googling but am missing something. Is it just a case of padding each column to the datatype?

A .csv can't be read as an external file. You'll need to write a little app, or find a utility, to convert the text to a fixed length format - and no quotes or other types of delimiter (like tabs or whatever). Each "row" of the input must be exactly the same length and it's your job to get the field boundaries properly aligned when you prepare the file. Left-pad the numbers with zeros and right-pad the strings with blanks.

Depending on the way the text file is formatted, you can build an end-marker into the row structure of the table definition, e.g. a carriage return and line-feed if the file was created on Windows, so in that case a CHAR(2).

./heLen