-2

I have this file (warning: potentially unsafe file download site) that I need to insert into the database using Postgres and Java. Please download it and let me know how I can extract data from this file. I tried the copy command. For one, it did not let me use the header option. I have to, as you can see from the file, copy data from the 4th row onwards. Another thing is the delimiter. If you read the file in notepad++, you can see them as proper columns, but there is no delimiter as such. I have tried and faced these challenges. Please help me get a custom query. Or another approach that can achieve what I want to do. Thanks!

Edit: I have used a safer link to download the file. Hope it works.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
CodingInCircles
  • 2,565
  • 11
  • 59
  • 84
  • 2
    Try showing an excerpt of the file here, rather than asking everyone to go download it. Suuure, I'll download this random file when someone sends me a link, that's *safe*. While you're at it, try specifying your PostgreSQL version, and where this mystery ".out" file came from, ie what produced it. – Craig Ringer Oct 13 '12 at 12:13
  • At a guess you'll have to write your own parser for this funky format, one that either (a) transforms it into clean sane CSV, or (b) connects directly to PostgreSQL and inserts the extracted data. Scripting languages like Perl, Python, etc are good choices for this kind of job. – Craig Ringer Oct 13 '12 at 12:15
  • You're right.. I should have just put some of the data here, but I wasn't sure if the formatting would come out right.. And so I thought this might be better. The .out file is the result of running a script to extract weather data from mesowest. I'm using the latest version of PostgreSQL. What do you mean by connecting directly to PostgreSQL and inserting the data? – CodingInCircles Oct 13 '12 at 12:28
  • Something like the Python `psycopg2` driver or Perl's `DBI` and `DBD::Pg`. If it's just fixed-width data then Chris's suggestion is way simpler, though. As for the file - you're using a hosting service that demands an email address to send a link. Nobody's going to want to do that. – Craig Ringer Oct 13 '12 at 12:30
  • Shoot! I didn't know that.. I'm sorry.. I'll re-upload and update the question. I'll look up Perl and see what comes up. Is there no way of doing this in Java though? – CodingInCircles Oct 13 '12 at 12:32
  • Looks like you're working with a fixed-format file. See this question for solutions to your problem: http://stackoverflow.com/questions/1609807/whats-the-best-way-of-parsing-a-fixed-width-formatted-file-in-java. Next time please post excerpts from files, not links to dodgy download sites. That 2nd one you posted tried to download some executable to my machine, it's a menace. This question would've been answered in minutes. – Craig Ringer Oct 13 '12 at 14:40

1 Answers1

2

So it sounds like you are doing fixed width files. COPY doesn't handle these so your best bet is to load up a spreadsheet, import the file (most can handle fixed width), and then export as csv. You can then use COPY.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • Good idea using a spreadsheet as a basic ETL tool. – Craig Ringer Oct 13 '12 at 12:28
  • The problem with using Excel is that when I open the file in Excel, the entire row takes up one cell. So I have all the data in A1 to A30000, rather than it being in different cells in the same row. :/ – CodingInCircles Oct 13 '12 at 12:31
  • It has been a while since I used Excel, but last time I did it opened up a wizard and I had to select fixed width instead of delimiter. See http://office.microsoft.com/en-us/excel-help/text-import-wizard-HP010102244.aspx – Chris Travers Oct 13 '12 at 12:34
  • Thank you! I will definitely look this up and let you know how it worked out. Thanks! :) – CodingInCircles Oct 13 '12 at 12:37
  • @CraigRinger I use spreadsheets for that with relative frequency. – Chris Travers Oct 13 '12 at 12:40
  • @ChrisTravers Looks like [the answers to this question](http://stackoverflow.com/questions/1609807/whats-the-best-way-of-parsing-a-fixed-width-formatted-file-in-java) suggest some good alternatives, too, like the Flatworm library. – Craig Ringer Oct 13 '12 at 14:43