David Patrick McKenzie

Digital Public Historian

It’s 3 a.m. … Do you know where your CSV columns are?

Tomorrow, or technically today, I’m presenting in Clio 3 on Data Manipulation.

As Professor Gibbs and I defined it on Monday, my presentation on this potentially broad topic is twofold:

  • Using SQL commands in PHPMyAdmin to merge and split fields; e.g., merge or split names;
  • Using PHP to switch a CSV file’s date format into an acceptable one for input into a MySQL database.

The first is one with which I feel rather comfortable, and ready to present.

The second, on the other hand… I spent a few hours last night dealing with that (and a bad allergy attack), and I’ve spent all evening tonight on it. After a lot of trial and error, I have much of it working. I can get the file open, and even write back into it. The problem is the middle–switching the order of the dates.

Here is what I have:

The middle parts are the problem.

I am most thankful to this blog post by Evan Cordulack, an American Studies graduate student at William & Mary; after looking at many sites that gave me parts of what I needed, his helped me crystalize most of what I needed.

I tried a few different things: getting slightly familiar with PHP functions (via these two posts that gave functions for changing order of numbers), and using Sasha’s code for her form. The latest version (as posted below, next to the original) reflects Sasha’s code (thanks for going over it with Megan and me on Monday! Hey, look, alliteration!).

I get the feeling that part of my issue is trying to change the data in just one column. Here’s what arouses my suspicions: I get a variation of the jumbled data each time I try.

So… I’ve reached a point where I’m not sure what else to do. There’s something that I’m clearly missing here. Since I’m having too hard of a time figuring out what I’m doing wrong in that middle part, I’m writing this post. Any suggestions are most appreciated.

Dr. Gibbs–if I am able to get off work early (a big if), will you be around? Otherwise, may I make figuring this out part of my presentation? 🙂

This is what the CSV file originally looked like. I stripped out everything else except for the case number.

Eeep. Other times, it’s changing my initial numbers.

5 Comments

  1. Just to be clear: your csv file has the dates in the standard US format dd/mm/yyyy, not as separate columns? And are you trying to reformat the date on import or once the data is in a mysql table?

  2. Yup, they are in the US standard format, in one column. The goal, at least as I understood it, is to reformat them in MySQL standard, but spit them back out into the same csv file. That’s where I got stuck–fitting it into the parameters of the statement that reads the data from, and writes it back into, the csv file. On the bus now to work but if you have any suggestions please do let me know. 🙂 And thanks for having a look and commenting!

  3. Okay, the one thing I can see is that in your date re-arrangement you might be using the wrong variable in line 16. Have a look at mine, which is based on Sasha’s. The value in the re-arranging reset should be the variable you created with the explode. In other words, try using $new_date[2] etc when re-setting the value for variable $date and then set $data[1]=$date.

    (I really hope it makes sense, but if it doesn’t, compare variable use to lines 16 and 17 of my insert gist, and the look at which variable I use on line 28.)

  4. megan’s right. you are exploding old_date into new_date (line 15), but then trying to recombine elements of old_date into the date variable (line 16), which explains why you’re getting nonsense.

  5. David McKenzie

    September 20, 2012 at 9:17 am

    Thanks to both of you for your replies! I’m at work now, but will try it when I get home before I head to class. So will let you know what happens!

Leave a Reply

%d bloggers like this: