Brett Magill
2005-Dec-14 20:45 UTC
[R] SAS.xpt/STATA.dta, field descriptions, and dbWriteTable
Hello all, I have a large database (~100MB in 13 relational files) that made its way to me in two formats. The files were sent in both SAS xport and STATA, thanks, I am told, to stat-transfer. The two files, ostensibly, contain the same data, just in different formats. My goal: Move these files to MySQl without the help of SAS or STATA (which I do not have). The tools I am using are: 1. sasxport.get from Hmisc 2. read.dta from foreign 3. dbWriteTable with the MySQL driver to create and populate the tables. The import and table creation went well, though the SAS file created all text field types in the MySQl database. (I am not sure if this is a characteristic of the original data set or a feature of sasxport.) Importing and writing to MySQL from the STATA file did just as well (thought taking a bit longer) and preserved the field types. However, what neither did (and I am certain this is a limitation of dbWriteTable) is write the field comments to the column comments in MySQL. I could see the column descriptions in R as part of the imported STATA file. A feature request for dbWriteTable is these elements are available, perhaps... In any case, this leads to my question: Does anyone know of a convenient way to write the column descriptions from R to the column comments in MySQL? The object returned by read.dta is a list that has the data.frame, the row names, the column names, and the descriptions, all nicely indexed. The column names, ofe course, are the same ones dbWriteTable used when it created the MySQL tables. My only thought for now is looping through these lists and embedding the needed SQL to write the comments. I am not certain my programming in either language is up to that though. Is there a better way? Any hints? Best, Brett Using Ubuntu Breezy 5.10 (Linux 2.6.12.6) R-2.2.0