Thread: create database from text file, to delete columns
Readers, I am using a program that saves data in csv format. I have realised that I cannot view the file in my spreadsheet due to 256 column limit. I think that I need to import the file into a database. Then I want to review, then delete columns with null data. I guess I can use the 'copy from' command? Also I do not know the total number of columns so can I import into an empty database and the columns are automatically created from the end of each line? Yours, postgresqlnovice@conference.jabber.org
On Wed, Apr 2, 2008 at 10:56 AM, e-letter <inpost@gmail.com> wrote: > Readers, > > I am using a program that saves data in csv format. I have realised > that I cannot view the file in my spreadsheet due to 256 column limit. > > I think that I need to import the file into a database. Then I want to > review, then delete columns with null data. > > I guess I can use the 'copy from' command? Also I do not know the > total number of columns so can I import into an empty database and the > columns are automatically created from the end of each line? No. You can easily use python, perl, R, java, or some other language to parse the file and generate the create table statements. However, if you just need to remove some columns, you do not really need the database at all. Just use a little script (again, in a scripting language) to remove the columns you like. Sean
On 02/04/2008, Sean Davis <sdavis2@mail.nih.gov> wrote: > No. You can easily use python, perl, R, java, or some other language > to parse the file and generate the create table statements. However, > if you just need to remove some columns, you do not really need the > database at all. Just use a little script (again, in a scripting > language) to remove the columns you like. > I'm afraid cannot easily use awk, perl, nor rexx (simply beyond my comprehension). So I want to return to the database way as the nearest to a spreadsheet. I tried importing my csv file using the copy command but nothing happens. I created a blank database and then issued the command: COPY databasename FROM '/filename.csv' | STDIN WITH DELIMITER ',' NULL '0.0E0' CSV Then I issue the command: \dl and there is nothing in the database just two empty columns appear, ID|Description. How to resolve please.
On Thu, Apr 3, 2008 at 8:52 AM, e-letter <inpost@gmail.com> wrote: > On 02/04/2008, Sean Davis <sdavis2@mail.nih.gov> wrote: > > No. You can easily use python, perl, R, java, or some other language > > to parse the file and generate the create table statements. However, > > if you just need to remove some columns, you do not really need the > > database at all. Just use a little script (again, in a scripting > > language) to remove the columns you like. > > > I'm afraid cannot easily use awk, perl, nor rexx (simply beyond my > comprehension). So I want to return to the database way as the nearest > to a spreadsheet. > > I tried importing my csv file using the copy command but nothing > happens. I created a blank database and then issued the command: > > COPY databasename FROM '/filename.csv' | STDIN WITH DELIMITER ',' NULL > '0.0E0' CSV You will still need to create all the columns for your table first. Also, I think you'll want to look at the documentation for the copy command, as what you give above should probably result in an error. Sean
On 03/04/2008, Sean Davis <sdavis2@mail.nih.gov> wrote: > > You will still need to create all the columns for your table first. > Also, I think you'll want to look at the documentation for the copy > command, as what you give above should probably result in an error. > So columns cannot be automatically created. The documentation is poor, for example the 'explanation' of the use of |stdin should really explain that as an alternative
On Thu, Apr 3, 2008 at 10:43 AM, e-letter <inpost@gmail.com> wrote: > On 03/04/2008, Sean Davis <sdavis2@mail.nih.gov> wrote: > > > > You will still need to create all the columns for your table first. > > Also, I think you'll want to look at the documentation for the copy > > command, as what you give above should probably result in an error. > > > So columns cannot be automatically created. The documentation is poor, > for example the 'explanation' of the use of |stdin should really > explain that as an alternative No. Columns are not automatically created. As for documentation, what you see is the standard way of describing SQL. I agree it can be confusing in the beginning. If all you want to do is load data into a database to look at it, you might look at MS Access or Filemaker. I don't recommend these as database solutions, but as more of an excel replacement. Sean