Garland data imported
Garland's data is now imported.
Garland gave me three XLSX (Excel 2007) files with these dates:
- 1/1/1997 through 1/1/1999 (10.1 MB)
- 1/2/1999 through 12/31/2005 (26.4 MB)
- 1/1/2006 through 10/31/2009 (57.0 MB)
I converted these to CSV and then stitched together into one consolidated file.
The CSV had several backslashes. These have special meaning during the import because they escape other characters. I reviewed many of these, and in fact each use of a backslash was done when a forward slash would have made more sense, so I just did a search and replace to convert the backslahses. I wanted to not replace any double backslashes, so I searched for \\([^\\]) and replaced with /\1. This means I found any instance of a backslash that wasn't followed by another backslash and converted it to a forward slash. But I then searched more and found that remaining instances of backslash made no sense, so I just converted them all to forward slashes.
Here's the SQL:
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Officer_Id VARCHAR(5),
Driver_License VARCHAR(20),
Race VARCHAR(1),
Sex VARCHAR(1),
Veh_License VARCHAR(12),
Veh_Year VARCHAR(4),
Veh_State VARCHAR(2),
Veh_Make VARCHAR(15),
Veh_Color VARCHAR(8),
Arr_Code VARCHAR(4),
Arr_Description VARCHAR(60),
Allg_Speed VARCHAR(3),
Posted_Speed VARCHAR(3),
Arr_Date VARCHAR(10),
Arr_Address VARCHAR(169),
Arr_City VARCHAR(7),
Final_Disposition VARCHAR(39),
Disp_Date VARCHAR(10),
Fine VARCHAR(9)
);
LOAD DATA INFILE 'C:/Users/Aren Cambre/Desktop/g/Consolidated.csv'
INTO TABLE de.raw_garland
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(Officer_Id, Driver_License, Race, Sex, Veh_License, Veh_Year, Veh_State, Veh_Make, Veh_Color, Arr_Code, Arr_Description, Allg_Speed, Posted_Speed, Arr_Date, Arr_Address, Arr_City, Final_Disposition, Disp_Date, Fine);
This work by Aren Cambre is licensed under a Creative Commons Attribution 3.0 United States License.
Comments
Post new comment