Hi, I am working on an app that reads a .csv file and loads each cell as
a row into a database. However much I discourage users to copy and past
curly quotes from MS Word into their .csv, I need to build in a way to
handle this.
Using the create method, my generated insert statement will include
values like this for cells with questionable strings:
E’Dido’s Woman’
And the E’ means that there should be an escaped character in here, but
Ruby doesn’t escape the character.
Any idea how to fix this? I have tried using the Iconv library to
convert, but that doesn’t solve the escaped character issue.
Hi, I am working on an app that reads a .csv file and loads each cell as
a row into a database. However much I discourage users to copy and past
curly quotes from MS Word into their .csv, I need to build in a way to
handle this.
Using the create method, my generated insert statement will include
values like this for cells with questionable strings:
E’Dido’s Woman’
And the E’ means that there should be an escaped character in here, but
Ruby doesn’t escape the character.
Are the characters getting into the database properly?
Is your database encoding set to UTF8?
Does your database.yml file specify UTF8 encoding?
What exactly are the problems you’re seeing? Specific errors or
corrupted data would be helpful.
Any idea how to fix this? I have tried using the Iconv library to
convert, but that doesn’t solve the escaped character issue.
Are the characters getting into the database properly?
No, the system is chocking at this character
Is your database encoding set to UTF8?
Yes
Does your database.yml file specify UTF8 encoding?
No…
What exactly are the problems you’re seeing? Specific errors or
corrupted data would be helpful.
Here is my error:
RuntimeError: ERROR C22021 Minvalid byte sequence for encoding “UTF8”:
0x92 HThis error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
“client_encoding”. Fwchar.c L1534: INSERT INTO “table_cells”
(“row_id”, “updated_at”, “col_span”, “table_meta_id”, “additional_info”,
“content”, “column_id”, “created_at”) VALUES(2, ‘2009-10-21
19:36:14.440000’, 1, 154, NULL, E’Dido’s Woman’, 3, ‘2009-10-21
19:36:14.440000’)
Another error:
IOError (closed stream):
I believe the problem is that the ’ in Dido’s is not being escaped,
despite the E’ indicating that it should/will be.
Are the characters getting into the database properly?
No, the system is chocking at this character
Is your database encoding set to UTF8?
Yes
Does your database.yml file specify UTF8 encoding?
No…
That could be your problem, then. Change it and restart the app.
What exactly are the problems you’re seeing? Specific errors or
corrupted data would be helpful.
Here is my error:
RuntimeError: ERROR C22021 Minvalid byte sequence for encoding “UTF8”:
0x92 HThis error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
“client_encoding”. Fwchar.c L1534: INSERT INTO “table_cells”
(“row_id”, “updated_at”, “col_span”, “table_meta_id”, “additional_info”,
“content”, “column_id”, “created_at”) VALUES(2, ‘2009-10-21
19:36:14.440000’, 1, 154, NULL, E’Dido’s Woman’, 3, ‘2009-10-21
19:36:14.440000’)
Another error:
IOError (closed stream):
I believe the problem is that the ’ in Dido’s is not being escaped,
despite the E’ indicating that it should/will be.
Well, the error you’re getting would indicate an encoding mismatch
between the app and the DB – hardly surprising if you’re not telling
the app about the DB encoding. So fix your database.yml and see what
happens.
those curly quotes are now preceded by a “”, which goes into the
database like this – not what I need, either. It’s as if this “” is
what is being used to escape the funny characters.
those curly quotes are now preceded by a “”, which goes into the
database like this – not what I need, either. It’s as if this “” is
what is being used to escape the funny characters.
No, that’s an extra byte that’s there because UTF-8 is a multibyte
encoding. – not really an escape character. But now that your database
and app encodings are identical, try taking out the Iconv call; it will
probably not be necessary. If that doesn’t work, then you may need to
call Iconv with reversed arguments – after all, it should be converting to UTF-8.
In any case, I would strongly suggest that, as a first step, you make
sure that you can round-trip characters between the app and the DB.
Then verify the file uploads.
Again, thanks. I did pull out the Iconv, and still, the same results.
Using a strait form, yes, I can get a curly quote in and out. Just not
when that curly quote is in a .csv, which I parse into an array of
arrays, like this:
[[“a”, “b”, “c”],[“cell1”, “cell2”, “cell3”]] and then input into the
database like this:
Table.create(:table_attrib => var) do |tc|
tc.column_id = col_id
tc.row_id = r_id
tc.content = cell
end
Try getting the fields you want and putting them in directly, ignoring
any CSV stuff… That is, get them out of the CSV file, but don’t let
your CSV library mess with them. Maybe that’s the culprit.
You might also fiddle around with this Iconv line…
Again, thanks. I did pull out the Iconv, and still, the same results.
Using a strait form, yes, I can get a curly quote in and out.
OK. Then your app and DB are talking the same encoding. Great. (And
remember to specify UTF8 in your HTML headers.)
Just not
when that curly quote is in a .csv,
Well, yeah, the uploaded file could be in a different encoding, so you
probably should run it through Iconv before doing any processing. Let
me check the Iconv docs and see if I can find anything useful.
Or you could encourage your users to save their files in UTF-8, or
perhaps to paste them into a form instead of uploading (which should
bypass the issue entirely, assuming your content-type headers are
correct).
This seems like a very bad solution – particularly the 2nd line, which
is pre-escaped HTML instead of a literal character. Please read my
previous message in this thread for better ideas.
Try getting the fields you want and putting them in directly, ignoring
any CSV stuff… That is, get them out of the CSV file, but don’t let
your CSV library mess with them. Maybe that’s the culprit.
You might also fiddle around with this Iconv line…
