Re: pg_atoi error? - Mailing list pgsql-novice
| From | Godshall Michael |
|---|---|
| Subject | Re: pg_atoi error? |
| Date | |
| Msg-id | A596FA3368757645AF862C701495CA000138CBBE@hor1mspmx01.gmachs.com Whole thread Raw |
| In response to | pg_atoi error? (Lynna Landstreet <lynna@gallery44.org>) |
| List | pgsql-novice |
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@gallery44.org]
Sent: Wednesday, July 30, 2003 6:03 PM
To: pgsql-novice@postgresql.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@gallery44.org]
Sent: Wednesday, July 30, 2003 6:03 PM
To: pgsql-novice@postgresql.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
pgsql-novice by date: