Re: [firebird-support] loading data from a .csv file?
Den 2014-05-18 21:23 skrev Alan Shank li...@notoriousshankbrothers.com [firebird-support] såhär: Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. I have Firebird2.5-super on Ubuntu 14.04, 64-bit. Thomas mentioned external tables. I've tried a couple of other data pump methods, both free and commercial import utilities as well as my own C# import loop (Windows and .Net in that case obviously). They all use a loop of insert statements, one for each .csv file row. While it works fine, it's generally very slow. I've now switched completely to external tables, or in some cases (not so many records) generating insert statements and executing them in one query batch. It does require that you modify the file, but it's extremely fast! I usually modify the format to fixed width with or without separator, matching a FB table with char columns (with an extra dummy column for each separator, if present, and an extra dummy column for the linebreak). It's easiest with a character set with a fixed number of bytes per character like iso 8859-1, ascii or win-1252, because FB expects each column to be the same number of bytes, not characters. I.e. with utf8 format it expects a char(10) column to consist of 40 byte in the file. The problem with that is that with text editors etc. such a string will appear as anything from 10 to 40 characters, depending on what characters you enter. If you enter ten 4 byte characters, that's the same byte length as 40 1 byte characters. So, such a file will not be fixed-width if you count characters. If you convert the format with some utility, you have to pad the strings to 40 byte, taking care to really count byte, not characters. With fixed width character sets, it's much easier. Then use an external table like this (if separators are present and line breaks in your system are 1 single character) create table External external file 'yourfilepath' ( DataCol1 char(10) character set iso88591, Sep1 char(1) character set iso88591, DataCol2 char(25) character set iso88591, Sep2 char(1) character set iso88591, DataCol3 char(7) character set iso88591, Linebreak char(1) character set iso88591 ); Then import into your real table making sure the char columns are appropriately converted: insert into MyTable ( SomeString, -- varchar(10) SomeDate, -- datetime SmallInteger -- int ) select trim(cast(DataCol1 as varchar(10)) SomeString, cast(DataCol2 as datetime) SomeDate, cast(DataCol3 as int) SmallInteger; Good luck! Kjell
[firebird-support] loading data from a .csv file?
Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. I have Firebird2.5-super on Ubuntu 14.04, 64-bit. Thanks, Alan Shank Woodland, CA
Re: [firebird-support] loading data from a .csv file?
I process mine through php ;) Although I have dropped mysql data as simple insert data. It would perhaps be nice as an option in flamerobin Sent from my android device so the quoting is crap! -Original Message- From: Alan Shank li...@notoriousshankbrothers.com [firebird-support] firebird-support@yahoogroups.com To: firebird-support@yahoogroups.com Sent: Thu, 22 May 2014 20:46 Subject: [firebird-support] loading data from a .csv file? Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. I have Firebird2.5-super on Ubuntu 14.04, 64-bit. Thanks, Alan Shank Woodland, CA Posted by: Alan Shank li...@notoriousshankbrothers.com ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links
Re: [firebird-support] loading data from a .csv file?
Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. There are some very useful tips here. http://stackoverflow.com/questions/8213/generate-insert-sql-statements-from-a-csv-file http://stackoverflow.com/questions/8213/generate-insert-sql-statements-from-a-csv-file I agree with Lester this would nice to have in Flamerobin or we can ask Ivan nicely for a Linux version of FSQL ;)
Re: [firebird-support] loading data from a .csv file?
To import csv, I usually use B_LOADFROMFILE from rfunc UDF library (http://rfunc.sourceforge.net/) with some recursive query to split lines and fields. If you need hints on this, I'll provide an example. If you can modify the format of the data to import, you might consider external tables. Thomas Am 22.05.2014 22:09, schrieb Lester Caine les...@lsces.co.uk [firebird-support]: I process mine through php ;) Although I have dropped mysql data as simple insert data. It would perhaps be nice as an option in flamerobin Sent from my android device so the quoting is crap! -Original Message- From: Alan Shank li...@notoriousshankbrothers.com [firebird-support] firebird-support@yahoogroups.com To: firebird-support@yahoogroups.com Sent: Thu, 22 May 2014 20:46 Subject: [firebird-support] loading data from a .csv file? Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. I have Firebird2.5-super on Ubuntu 14.04, 64-bit. Thanks, Alan Shank Woodland, CA Posted by: Alan Shank li...@notoriousshankbrothers.com mailto:li...@notoriousshankbrothers.com ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links firebird-support-fullfeatu...@yahoogroups.com mailto:firebird-support-fullfeatu...@yahoogroups.com -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
