SQLite Import tools

[phaworth]

I'm trying to transfer data over from an old database into an SQLite db by exporting a .csv file from the old database and importing it into the SQLite db.

Some of the data in the old db includes carriage returns and also tabs, commas, and semi-colons. These characters are all use as either column or row separators.

The data containing those characters is enclosed in quotes but all the import tools I've tried so far treat them as separators in spite of that which results in a lot of import errors.

Does anyone know of an import tool that will deal with this situation? I have a couple of workarounds in mind but would be easiest if I could find an import tool.

Thanks,
Pete

[Joe F.]

I would just use a text editor to convert the tabs to spaces and the returns to characters. Then you'd be able to save as a tab-delimited file which would leave all your commas and semi-colons intact.

[espais]

Hi,

Try with

https://addons.mozilla.org/es-ES/firefox/addon/5817

and

http://www.sqlabs.com/sqlitemanager.php

Salut,
Josep

[phaworth]

Thanks Joe and Josep, I'll try both things.

In fact, I have already tried replacing the offending characters with special symbols and importing into the database but have now run into another problem. I replaced the returns with "~CR" and now I would like to replace the "~CR" with returns in the database. I'm using a statement in the form:

UPDATE table SET field=replace(field,"~CR",??) WHERE field LIKE "%~CR%"

... but I can't figure out what to put in where the "??" is to indicate a return. The db I came form recognised \r\n as carriage return/line feed but using that with SQLite just results in "~CR" being replaced with "\r\n".

Any ideas?

Thanks,
Pete

[phaworth]

The Firefox plugin processes carriage returns correctly so problem solved. Thanks for all the input.
Pete

[Joe F.]

So what do they have to be?
I was going to suggest "ln".