Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have the Provincial Groundwater Monitoring Network as a point feature of 39 monitoring wells which includes an attribute for WELL ID. Each monitoring well then has a corresponding .csv file with 400 records of water level fluctuations.

I was wondering how I could relate the one well point to the many water level readings within a .csv file?

The WELL_ID field in the point layer is a String with a length of 254 however the corresponding field in the .csv file is a string with a length of 255. I have tried adding a field to the point layer with exactly the same name, type and length, however ArcMap 10 defaults the new string field to a length of 254.

Is there something I am doing wrong or is there something I could try differently.

sneely1

share|improve this question
    
Which software are you using? If you are using ArcGIS, I think you need to convert the file into a DB from the CSV to do relates properly –  dassouki Feb 28 '13 at 20:27
    
I am using ArcMap 10. I will try that thanks –  sneely1 Feb 28 '13 at 20:35
    
the water level records are named W00006-1.csv for example, so i am getting and error that the name contains invalid characters. is there an easy way in model builder to rename my 39 .csv files to W00006_1 ? –  sneely1 Feb 28 '13 at 20:46
1  
You can batch rename these on the command line. Make a back up first. Then just open a cmd in that directory and run this: for /f "tokens=* delims= " %i in ('dir /b "*.csv"') do Set LIST=%i& set LIST | ren "%~fi" "%LIST:-=_%" –  Jamie Feb 28 '13 at 21:45
    
I use filerenamer to handle that stuff. Then make sure your field names dont have any symbols or other items (spaces). Don't worry about the column width. You need a one to many join. –  Brad Nesom Mar 1 '13 at 3:30

1 Answer 1

Instead of having 39 tables and trying to relate them individually, it sounds like what you want to do is combine/denormalize your various CSV files into one. Then simply import that into a geodatabase table or DBF and relate it to your point layer. If the filename is significant you could add it as a column to the combined CSV file.

You might be able to do this simply using the ArcToolbox Merge tool, but if you want a bit more flexibility (such as adding the filename as a column like I mentioned), the merge_csv_files function in the following Python script, which makes use of the very handy csv module, can do this for you.

The script, if run on its own as written, will run through a standalone test case (the if __name__ == "__main__" section) to demonstrate its use (see output below). Feel free to adapt it to your needs. For example you could import it as a module or copy-paste the merge_csv_files function into your own script.

import os, sys, csv, pprint, tempfile

def merge_csv_files(input_csv_files, output_csv_file, filename_column=None):
    """
    Merges each of the CSV files specified by the input_csv_files sequence into
    the output_csv_file, optionally mapping the filename (minus extension) of
    each input_csv_file into a new column specified by filename_column.
    """
    with open(output_csv_file, "wb") as fo:
        w = None
        for i, csvfile in enumerate(input_csv_files):
            if filename_column:
                filename = os.path.basename(os.path.splitext(csvfile)[0])
            with open(csvfile, "rb") as fi:
                r = csv.DictReader(fi)
                if filename_column:
                    r.fieldnames.append(filename_column)
                if i < 1:
                    w = csv.DictWriter(fo, r.fieldnames)
                    w.writerow(dict((fn,fn) for fn in r.fieldnames)) # Python 2.6
                    ## w.writeheader() # Python 2.7+
                for row in r:
                    if filename_column:
                        row[filename_column] = filename
                    w.writerow(row)
        return output_csv_file

def write_csv_file(csv_file, rows):
    """Writes the input rows to the output csv_file."""
    with open(csv_file, "wb") as f:
        w = csv.writer(f)
        w.writerows(rows)
    return csv_file

def pprint_csv_file(csv_file):
    """Reads the input csv_file and pretty-prints its contents."""
    with open(csv_file, "rb") as f:
        r = csv.DictReader(f)
        pprint.pprint(r.fieldnames)
        for row in r:
            pprint.pprint([row[key] for key in r.fieldnames])

if __name__ == "__main__":
    # Get the path to the user's temporary files directory
    tempdir = tempfile.gettempdir()

    # Create a temporary CSV file for testing
    testcsv1 = write_csv_file(
        os.path.join(tempdir, "W00006-1.csv"),
        [["WELL_ID", "WATER_LEVEL"],
         [1, 50.5],
         [2, 60.5],
         [3, 70.5]])

    # Create another temporary CSV file for testing
    testcsv2 = write_csv_file(
        os.path.join(tempdir, "W00006-2.csv"),
        [["WELL_ID", "WATER_LEVEL"],
         [4, 55.5],
         [5, 65.5],
         [6, 75.5]])

    # Merge the two CSV files
    testmergecsv = merge_csv_files(
        [testcsv1, testcsv2],
        os.path.join(tempdir, "WELL_WATER_LEVELS.csv"),
        "FILE_NAME")

    # Print all three CSV files
    pprint_csv_file(testcsv1)
    pprint_csv_file(testcsv2)
    pprint_csv_file(testmergecsv)

    # Clean up temporary files
    [os.remove(f) for f in [testcsv1, testcsv2, testmergecsv]]

Example test output:

['WELL_ID', 'WATER_LEVEL']
['1', '50.5']
['2', '60.5']
['3', '70.5']
['WELL_ID', 'WATER_LEVEL']
['4', '55.5']
['5', '65.5']
['6', '75.5']
['WELL_ID', 'WATER_LEVEL', 'FILE_NAME']
['1', '50.5', 'W00006-1']
['2', '60.5', 'W00006-1']
['3', '70.5', 'W00006-1']
['4', '55.5', 'W00006-2']
['5', '65.5', 'W00006-2']
['6', '75.5', 'W00006-2']

The merged CSV file can be imported into a geodatabase table and related to your points feature class on WELL_ID. Actually in 10.1 you can relate to the CSV file directly without having to import it to a GDB table. Not sure about 10.0. For best performance, you'll probably want to import it to a file geodatabase and create attribute indices, however.

share|improve this answer
    
Thank you all for your help! –  sneely1 Mar 1 '13 at 20:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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