Importing SBT files ERROR: numeric field overflow

[Michael Martin]

I got a reply in a few minutes after my last post that is so cool. Thank you.

I am getting this error on some file

ERROR:  numeric field overflow

DETAIL:  A field with precision 4, scale 0 must round to an absolute value less than 10^4.

CONTEXT:  COPY apvend01, line 13, column openpo: "41072.9560"

Any suggestions for dealing with this error message?

Thanks

Michael

[Philipp Wollermann]

Hi Michael,

it seems like the data type definitions between your original data and what's in Postgres don't match up completely. This might a bug in pgdbf, or an error in your original data file.

Basically, pgdbf generates a NUMERIC field in Postgres for that column with precision=4 and scale=0, which means "the number has 4 digits in total and 0 digits after the decimal point" aka "it can be 9999 max". But your data file has numbers like "41072.9560", so they're too big and also have a decimal part.

What you could do is manually edit the generated SQL of pgdbf so that instead of a NUMERIC(4, 0) field it generates a NUMERIC(12, 4) field. That would be big enough up to 99999999.9999.

Or you could try the -N option for pgdbf, which means that your numeric fields will be saved as TEXT instead of NUMERIC. That might not be what you want, though.

Hope that helps,

Philipp

--
You received this message because you are subscribed to the Google Groups "PgDBF" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgdbf+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Philipp Wollermann]

(Adding the mailing list again to the recipient list.)

That's great! Regarding decimal instead of numeric, I checked the Postgres manual: "The types decimal and numeric are equivalent." So it is perfectly fine if you do it like that.

Just make sure you don't lose important data due to iconv. The error message suggests, that there are some characters that need to be converted correctly to UTF-8. For example, if you had German or French text (with umlauts), or currency symbols, etc. as text in your database (English is usually plain ASCII and not affected), it might look like this in your database vs. Postgres now:

Original: München, Wörterbuch, café, crêpe

After using iconv: M?nchen, W?rterbuch, caf?, cr?pe.

All characters that don't fit plain ASCII are simply removed or replaced with question marks by iconv in that mode.

If possible, try to find out which character set your database is using (probably ISO-8859-1 also known as latin-1 or something similar to that). Then use the newest pgdbf version that can convert character sets - that way you preserve all original texts as they are.

Philipp

On Sat, Oct 26, 2013 at 1:11 AM, Michael Martin <mma...@slauson.com> wrote:

It used decimal instead of numeric so I made that change and tried it but got 

ERROR:  invalid byte sequence for encoding "UTF8": 0xad

 so I used iconv.  That server has the older version of your program.

I used pgdbf -q -D arymst01.dbf -m arymst01.fpt | sed 's/DECIMAL(4)/NUMERIC(12, 4)/g' | iconv -c -f UTF-8 -t UTF-8 | psql -d slauson and that worked.

Thanks

Michael

On Thu, Oct 24, 2013 at 5:03 PM, Philipp Wollermann <philipp.w...@gmail.com> wrote:

I wouldn't recommend to use the "iconv" command unless it is absolutely needed, because it may corrupt your data in the SQL. It also produced errors like you had in my case. It is the method of last resort only in case the data in your original files is already corrupt.

Except for that, what you're doing looks correct to me.

Could you try the following command:

pgdbf -q -D arymst01.dbf -m arymst01.fpt | sed 's/NUMERIC(4, 0)/NUMERIC(12, 4)/g' | psql -d slauson

What this does is, run pgdbf with mostly default options (option -q means: quote the table names), replace the wrong NUMERIC fields with the bigger ones and pipe the output directly to Postgres. If it works as-is, that's great.

If not, it would be helpful if you can figure out the charset of your data. For that, could you install "chardet" (see https://pypi.python.org/pypi/chardet) and run it on the SQL output? You can install it via pip, easy_install or probably apt-get (the Ubuntu package is called python-chardet).

Then, run:

pgdbf -D arymst01.dbf -m arymst01.fpt > ar1.sql

chardet ar1.sql

and please post the output.

On Fri, Oct 25, 2013 at 7:55 AM, Michael Martin <mma...@slauson.com> wrote:

Yes the same error message. It changed everything to decimal..

I exported it to a text file then changed the field to NUMERIC(12, 4) 

If I could ask another question how do you get the file into Postgres?

pgdbf -D arymst01.dbf -m arymst01.fpt | iconv -c -f UTF-8 -t UTF-8 |  >ar1.sql

 psql -d slauson -f ar1.sql

I keep getting psql:ar1.sql:3173: invalid command \N

thanks

Michael

On Thu, Oct 24, 2013 at 3:36 PM, Philipp Wollermann <philipp.w...@gmail.com> wrote:

Sorry, did you get the same error message with -N or something else?

Also you could try to modify the generated SQL and change the data types from NUMERIC(4, 0) to something bigger like NUMERIC(12, 4). To do that, save the output of pgdbf to a file, open it with a text editor and search (and replace) the wrong NUMERIC fields.

That would be even better than using -N. After you modified the SQL, run it with Postgres as usual. If you get another error, feel free to post it.

On Fri, Oct 25, 2013 at 6:20 AM, Michael Martin <mma...@slauson.com> wrote:

I tried -N that didn't work.