2

I am trying to merge a month's worth of daily CSV files without using ArcGIS. Each file has ~25,000 entries so this is a high data load project.

Sample data can be found here. Note that we are using the Prerun product.

Arc can handle it (using simple merge geoprocess tool), but we have to make this open source as it will be used to teach students who don't have access to ESRI software.

We tried a simple c# code but ran into the problem that some of the days have extra columns, which snafu'd our code.

Before we spend the time to make our code much more robust to handle the disparity in the products, I wanted to ask if there was a way to merge tables (CSV files) within QGIS? This would be the simplest methodology for the people we're trying to benefit.

4
  • 1
    this looks more like a SO question. have a look at the csv module of Python, for instance. – radouxju Jun 20 '14 at 19:21
  • I agree with @radouxju that this would be more appropriate to Stack Overflow rather than GIS.SE - this would be a more appropriate if you were looking at spatially joining the two CSVs based on latitude/longitude for example. – om_henners Jun 22 '14 at 23:29
  • I agree too but before it is migrated I think it needs references to ArcGIS and QGIS edited out and perhaps Python made a requirement. – PolyGeo Jun 27 '14 at 3:14
  • I asked here because I am myself and the ppl I'm trying to benefit are more familiar with varying GISs than with coding. Someone else on my team wrote an executable in C# so we got the problem solved. Do to the post as you will; let me know if I need to delete or something. Always appreciative of this community-thanks! – WClark97 Jun 27 '14 at 15:10
4

I don't know if its possible to do this within QGIS at this moment with a tool like ESRI's. I'd recommend using a database for this because it's easier to manage. In the case of a database, you would simply use an APPEND or an equivalent SQL command (APPEND is MS Access syntax, the ANSI SQL equivalent is INSERT INTO) to custom load your data into your schema. If you aren't used to working in an RDBMS like MySQL,PostgreSQL, or SpatiaLite, I've located a quick and dirty solution on StackOverflow that allows you to use Python to specify the columns you want to keep in a .CSV merge, & order of columns do not matter. It uses Python's csvwriter. Here is the code (copied verbatim and untested, use at your own risk):

import csv

files = ['c1.csv', 'c2.csv', 'c3.csv']
final_headers = ['col1', 'col2', 'col3']

merged_rows = set()
for f in files:
    with open(f, 'rb') as csv_in:
        csvreader = csv.reader(csv_in, delimiter=',')
    headers = dict((h, i) for i, h in enumerate(csvreader.next()))
        for row in csvreader:
            merged_rows.add(tuple(row[headers[x]] for x in final_headers))
with open('output.csv', 'wb') as csv_out:
    csvwriter = csv.writer(csv_out, delimiter=',')
    csvwriter.writerows(merged_rows)
2

Populating a spatialite database with GDAL ogr2ogr seems to me the easiest way.

This can be automated using batch scripts.

Qgis would then only be used to visualize the current state of the database.

1
  • Ogr2ogr beginning from GDAL 1.11 has a parameter -addfields which seems to be made for you gdal.org/ogr2ogr.html. However, I do not know if CSV format belongs to those which support it. It will be very fast to test. – user30184 Jun 22 '14 at 19:33
1

You should take a look at csvkit which is a command line tool inspired by GDAL. It has a csvjoin tool that should do exactly what you want;

csvjoin -c join_column --left first.csv second.csv

or, to join on two columns with different names

csvjoin -c first_csv_column,second_csv_column --left first.csv second.csv

Not the answer you're looking for? Browse other questions tagged or ask your own question.