Обсуждение: importing a data file without defining a table structure
What's the quickest and easiest way to load data into tables from external files. I've dug around a bit and it may be the case that people just don't do what I'm looking to do, so I'm asking here. I've got a csv file with column names in the first row. I want to load that into a table and have the column types and names generated automatically. Can this be done? (Or do I need to first create a table with CREATE, typing out each column name and data type manually, and then edit the data file to remove the first row, and then copy the file into the table using COPY?) Thanks, Charles
Hello, > typing out each column name and data type manually, and then edit the > data file to remove the first row, and then copy the file into the > table using COPY?) You should be able to avoid editing the file to remove the first row. Try HEADER option http://www.postgresql.org/docs/8.4/static/sql-copy.html For the difficult questions, let us wait for expert opinion. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
On Jul 22, 2010, at 4:21 PM, c b wrote: > What's the quickest and easiest way to load data into tables from > external files. > > I've dug around a bit and it may be the case that people just don't do > what I'm looking to do, so I'm asking here. > > I've got a csv file with column names in the first row. I want to > load that into a table and have the column types and names generated > automatically. > > Can this be done? (Or do I need to first create a table with CREATE, > typing out each column name and data type manually, and then edit the > data file to remove the first row, and then copy the file into the > table using COPY?) No, Postgres does not automatically create the table structure for you. But not too difficult to implement with your favoritescripting language. I use this method to bulk load tables from other systems and then write views and functions toconvert it to the format I need. John DeSoi, Ph.D.