Re: pg_atoi error?

Hi,

I get that all the time as well. It is the very first character in the very
first line that has that stuff. I receive files from a MySql database and
have to import them into my postgresql database all the time. My postgresql
is running on Win2K/cygwin.

The dos to unix utilities have never been able to remove/translate that data
for some reason.

I made a simple program(I am no expert programmer) that I run my input text
file through if I modify the text file in a windows app before uploading.
Here is the source code. All you need is gcc to compile. I have hard coded
the input and output file names. Feel free to modify it in anyway. It also
changes a csv delimited to using the pipe(|) for delimiting. Any commas
between " " are left alone.

#include <iostream>
#include <fstream>

using namespace std;

int main () {

char str = '0';
char strPrevious = ' ';
char strTextFlag = 'N';
int intqute = 0, intPosition = 0;
char strSkipOutput = 'N';

ifstream in("C:\\Inventory_summary.csv");
if(!in)
{
cout << "Cannot open the input file.";
return 1;
}

ofstream out ("c:\\test.txt");
if(!out)
{
cout << "Cannot open the output file.\n";
return 1;
}

in.unsetf(ios::skipws);
cout.unsetf(ios::skipws);

while(in)
{

in >> str;
intPosition++;

if (intqute == 2)
{
intqute = 0;
strTextFlag = ' ';
}

switch(str)
{
case '\"' :
{
strTextFlag = 'Y';
intqute++;
str = ' ';
}
break;

case '#' :
{
strSkipOutput = 'Y';
}
break;

case '>' :
{
strSkipOutput = 'Y';
}
break;

case '<' :
{
strSkipOutput = 'Y';
}
break;

case '\t' :
{
str = '|';
}
break;

case '\r' :
{
strSkipOutput = 'Y';
}
break;

}

if (str == 'M' && strPrevious == '^') //convert dos line feed to unix
line feed. only if prior character was ^
{
str = 'J';
}

if (str == 'Z' && strPrevious == '^') //convert dos end of file to
unix end of file. only if prior character was ^
{
str = 'D';
}

if ( str == ',' && strTextFlag != 'Y') //convert comma delimiter to
a pipe
{
str = '|';
}

if (strPrevious == '\n' && str == '\n') //don't output extra \n
windows inserts on last line
{
strSkipOutput = 'Y';

}

if( strSkipOutput == 'N')
{
out << &str;
strSkipOutput = 'N';
}

strPrevious = str;

}

in.close();

out.close();

return 0;

}

-----Original Message-----
From: Lynna Landstreet [mailto:lynna(at)gallery44(dot)org]
Sent: Wednesday, July 30, 2003 6:03 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] pg_atoi error?

Hi folks,

Run into a wee problem here. I just attempted to flush and re-populate the
images table in my database, because I realized I'd done something in an
inefficient way. But now I'm getting an error when I try to use \copy in
psql to re-load the modified text file into the table. Here's the error I'm
getting:

ERROR: copy: line 1, pg_atoi: error in "1339": can't parse "1339"
lost synchronization with server, resetting connection

Here's what I was telling psql:

\copy images from images.txt using delimiters '|' with null as ''

And here's the contents of Line 1 in the text file I was using:

1339|/akimoto_shinobu/akimoto1.jpg|Secretary Project (installation
view)||chromogenic (colour) prints|7.5 x 10.8 cm each||1998|Slide||In
<i>Secretary Project,</i> Akimoto demonstrates, through ritualistic
documentation of her commuter train and locker, how mundane and repetitive
labour, limited time, space and resources, and the social imperative to
conform, shape and direct artistic practice.|2003-3-19|Lynna
Landstreet|2003-3-19|Lynna Landstreet

It doesn't look to me as though there should be anything particularly
contentious in there. The string quoted in the error message does not
appear, and there are no non-ASCII characters, Mac line returns or anything
else untoward in that row. The database is set to Unicode encoding, and the
text file was saved as Unicode/UTF-8 from my text editor, BBEdit (BBEdit Pro
7.0.3 introduced that option, which is very handy).

I've already tried the following:

- Stripping out and replacing all line returns in the file, while making
sure line returns are set to UNIX.
- Setting line returns to Unicode and doing likewise.
- Using BBEdit's "Zap Gremlins" feature to search for hidden control
characters and the like.
- Uploading the file both as ASCII and as binary to see if that made a
difference

This is the exact same text file I originally populated the table from --
the only difference is that I stripped out the string "/db_images" from the
beginning of the image_path field in each row. Does anyone have any idea
what might be causing this error message?

Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

-----Original Message-----
From: Lynna Landstreet [mailto:lynna(at)gallery44(dot)org]
Sent: Wednesday, July 30, 2003 6:03 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] pg_atoi error?

Hi folks,

Run into a wee problem here. I just attempted to flush and re-populate the
images table in my database, because I realized I'd done something in an
inefficient way. But now I'm getting an error when I try to use \copy in
psql to re-load the modified text file into the table. Here's the error I'm
getting:

ERROR: copy: line 1, pg_atoi: error in "1339": can't parse "1339"
lost synchronization with server, resetting connection

Here's what I was telling psql:

\copy images from images.txt using delimiters '|' with null as ''

And here's the contents of Line 1 in the text file I was using:

1339|/akimoto_shinobu/akimoto1.jpg|Secretary Project (installation
view)||chromogenic (colour) prints|7.5 x 10.8 cm each||1998|Slide||In
<i>Secretary Project,</i> Akimoto demonstrates, through ritualistic
documentation of her commuter train and locker, how mundane and repetitive
labour, limited time, space and resources, and the social imperative to
conform, shape and direct artistic practice.|2003-3-19|Lynna
Landstreet|2003-3-19|Lynna Landstreet

It doesn't look to me as though there should be anything particularly
contentious in there. The string quoted in the error message does not
appear, and there are no non-ASCII characters, Mac line returns or anything
else untoward in that row. The database is set to Unicode encoding, and the
text file was saved as Unicode/UTF-8 from my text editor, BBEdit (BBEdit Pro
7.0.3 introduced that option, which is very handy).

I've already tried the following:

- Stripping out and replacing all line returns in the file, while making
sure line returns are set to UNIX.
- Setting line returns to Unicode and doing likewise.
- Using BBEdit's "Zap Gremlins" feature to search for hidden control
characters and the like.
- Uploading the file both as ASCII and as binary to see if that made a
difference

This is the exact same text file I originally populated the table from --
the only difference is that I stripped out the string "/db_images" from the
beginning of the image_path field in each row. Does anyone have any idea
what might be causing this error message?

Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match