How to debug Postgres copy command failure

postgresql postgresql-8.4 sqlbulkcopy

Question

I have around 75k records which I am loading to a Postgres table using copy command which is failing. I get an exception

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

Now i need to find which line is having this entry. Is there any way to do this? I am thinking in lines of enabling some postgres logging that might help or any other solution Note: I am getting the issue with only one particular file. Other files are getting loaded without issues

1
1
8/28/2012 9:29:30 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

I always seem to get a line-number in my error, no matter whether I use COPY or \copy and feed a file via redirection or -f.

ERROR:  invalid byte sequence for encoding "UTF8": 0xa3
CONTEXT:  COPY z, line 3

If there are only a couple of bad chars and you just want to strip them you can use iconv (assuming you're on a unix-like system).

iconv -c --from=utf8 --to=utf8 /tmp/badchars.txt > /tmp/stripped.txt

You could always run diff against the before + after versions if you wanted to see what was stripped out.

1
8/28/2012 8:34:02 AM

Related Questions

Is it possible to transfer data across servers using sql bulk copy?

Yes. In fact, in the demo code shown ...here..., they copy to a table within the same database.
sql sqlbulkcopy sql-server
asked by Joe Stefanelli

Managing a SqlBulkCopy transactional network failure

Assuming you are using a TransactionScope, m...y understanding is that no..., the transaction will not be left open, because SQL Server will detect the ambient transaction and auto enlist. This means that the worst case is that the transaction times out, rolling back. You can change the ...transaction binding... to specify what to do in the event o...
ado.net c# sql sqlbulkcopy
asked by BrandonZeider

Finding the rows that caused the constraint failure

FYI Could not find a way to get this info from the framework, so built a helper class that can retrieve all the errors and related data rows when a SqlBulkCopy process fails. See here for details: ...http://www.codeproject.com/script/Articles/ArticleVersion.aspx?waid=13789&aid=387465
constraints datatable sqlbulkcopy sql-server
asked by David Catriel

Debugging SqlBulkCopy problems when it doesn't return any errors

Oh god i'm stupid... i forgot this one line: ...table.Rows.Add(row);...I tried to insert an empty datatable. This is why i love stackoverflow, even if i don't get the answer from this avesome community, it helps me to solve my problems :D
c# sqlbulkcopy
asked by Gabriel

cursor.copy_from failed in postgres

Close the file and then open it for reading...f = open('tariff.sql', 'w') st_l.copy_to(f, 'tarf', sep=',') f.close() f = open('tariff.sql', 'r') st.copy_from(f, 'tariff', sep=',')
cursor postgresql psycopg2 python sqlbulkcopy
asked by Clodoaldo Neto

Get the count of rows from a COPY command

Not in PG 9.2, but there is in PG 9.3 courtesy of Pavel (E 1.3.1.7):...Allow PL/pgSQL to access the number of rows processed by COPY (Pavel Stehule)...The command is GET DIAGNOSTICS x = ROW_COUNT....http://www.postgresql.org/docs/devel/static/release-9-3.html
count plpgsql postgresql sqlbulkcopy
asked by Denis de Bernardy

IDataReader implementation failure with SqlCeBulkCopy

It turns out the problem was something I missed; the source was not null, it was actually an empty string. It seems when I read from the .DAT file I was converting at the wrong point.
c# idatareader sqlbulkcopy sql-server-ce
asked by MrShoes

Persistent time out issues copying very large tables across SQL Server 2012 instances on Amazon RDS

We were running into a similar situation: running SQLAZureMW on an Window server 2012 EC2 instance connecting to SQL Server 2012 RDS Instance. AWS support suggested the following changes on our EC2 instance and it seems to have solved all of our issues:...Increase TCP/IP timeout value as described here (i'm not sure this is actually necessary) ...h...
smo sqlbulkcopy sql-server sql-server-2012 ssis
asked by Paul

Does SQLBulkCopy Upload any data upon failure?

By default ...SqlBulkCopy... will roll back to the ...last batch... you completed. If you have a ...BatchSize... of 0 (the default value) it will do it all in a single batch but you may get ...a timeout... if the batch takes too long to upload (default 30 seconds)....Another option is wrap the entire thing in a external transaction and pass it in t...
c# sql sqlbulkcopy sql-server
asked by Scott Chamberlain

Postgres: Copying large subsets of data(10k+rows) from one schema to another for immediate use

Since you are copying from a bunch of tables in one schema of a database to another schema of the same database, and cannot start until your fastest way to do so is going to be by using ...INSERT INTO ... SELECT FROM..., run as a user who has access to both schema, e.g.:...INSERT INTO schema2.table_y ( field, field, field ) SELECT field, field, fie...
postgresql sqlbulkcopy
asked by FuzzyChef

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow