Importing Excel CSV files into PhpMyAdmin

So sadly, Excel doesn’t enclose its CSV fields with double-quotes (“) which phpMyadmin needs. This workaround I found on the Microsoft support site:

  1. Open the original CSV file in Excel
  2. Run the macro listed above (repeated here) to save out the file:
    Sub QuoteCommaExport()
       ' Dimension all variables.
       Dim DestFile As String
       Dim FileNum As Integer
       Dim ColumnCount As Integer
       Dim RowCount As Integer
       ' Prompt user for destination file name.
       DestFile = InputBox("Enter the destination filename" _
          & Chr(10) & "(with complete path):", "Quote-Comma Exporter")
       ' Obtain next free file handle number.
       FileNum = FreeFile()
       ' Turn error checking off.
       On Error Resume Next
       ' Attempt to open destination file for output.
       Open DestFile For Output As #FileNum
       ' If an error occurs report it and end.
       If Err <> 0 Then
          MsgBox "Cannot open filename " & DestFile
       End If
       ' Turn error checking on.
       On Error GoTo 0
       ' Loop for each row in selection.
       For RowCount = 1 To Selection.Rows.Count
          ' Loop for each column in selection.
          For ColumnCount = 1 To Selection.Columns.Count
             ' Write current cell's text to file with quotation marks.
             Print #FileNum, """" & Selection.Cells(RowCount, _
                ColumnCount).Text & """";
             ' Check if cell is in last column.
             If ColumnCount = Selection.Columns.Count Then
                ' If so, then write a blank line.
                Print #FileNum,
                ' Otherwise, write a comma.
                Print #FileNum, ",";
             End If
          ' Start next iteration of ColumnCount loop.
          Next ColumnCount
       ' Start next iteration of RowCount loop.
       Next RowCount
       ' Close destination file.
       Close #FileNum
    End Sub
  3. In PhpMyAdmin, create the table you want to import into, then in the “Import” tab for that table, set the “Format” to CSV, and “Fields terminated by” to comma.
  4. Your import should now go through.

Be aware that if you have quotes in the fields, I’m not positive this script will handle them. YMMV.

Then if you are inserting this into a temporary table, you can copy it to the final table with something like

insert into table_final(field1, field2) 
select temp_field1, temp_field2 from table_temp;

How to install yum on Plesk 8 / Centos 5

My new VPS is running Plesk 8.x which looks awesome (I think it uses ExtJS for the UI), but oddly has a fairly steep learning curve to do simple things. Inexplicably, it starts you out without yum (perhaps to encourage you to use their package manager)? Their package manager couldn’t seem to resolve the dependencies to install yum for me (although it shows up as an available package), so I had to install it by hand, having only rpm at my disposal.

These are the steps that eventually worked (replace the “wg” with “wget”, since WordPress doesn’t like seeing that in a post):

# rpm -ivh

# wg
# rpm -ivh python-urlgrabber-3.1.0-2.noarch.rpm   

# wg
# rpm -ivh yum-metadata-parser-1.1.2-2.el5.i386.rpm yum-3.2.8-9.el5.centos.1.noarch.rpm

# wg
# rpm -ivh yum-3.2.8-9.el5.centos.1.noarch.rpm  

Update 8/11/08

The above FTP links no longer work. Here is a new script that works. Note the –nodeps, to avoid circular references you run into otherwise.

rpm -ivh
rpm -ivh
rpm -ivh
rpm -ivh
rpm -ivh
rpm --nodeps -ivh
rpm --nodeps -ivh
rpm -ivh

Integrating CakePHP and Joomla

For a website rewrite I am working on I am using Joomla 1.5 with CakePHP 1.2, using the Jake beta bridge (which so far is working great!).

I want to have:

  1. SEO-friendly URLs for Joomla, hence I am running Apache, which I found far easier to configure than IIS. Hence Joomla runs in the webroot.
  2. The Cake application running in a subdirectory of the root (/webapp) which I feel is the cleanest way to separate the two frameworks.
  3. The Cake app tables will sit inside the Joomla database however, which should be fine since the Joomla tables are already prefixed with “jos_”.

It works (almost) out of the box. The one change I ended up having to make was in my webroot .htaccess file, to add a rewrite condition for the special webapp directory:

########## Begin - Joomla! core SEF Section

# Skip rewrites of anything that is directed at /webapp
RewriteCond %{REQUEST_URI} !^/webapp

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_URI} !^/index.php
RewriteCond %{REQUEST_URI} (/|\.php|\.html|\.htm|\.feed|\.pdf|\.raw|/[^.]*)$  [NC]
RewriteRule (.*) index.php
RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization},L]
########## End - Joomla! core SEF Section

Gotchas when first installing IIS on Vista

Today I enabled IIS on my Vista box for the first time in order to run an existing ASP site. Here are my gotchas:

ASP handlers are not enabled by default

I went to Control Panel->Programs and Features->Turn Windows features on or off, and then checked the box for IIS without thinking about it.

However, it turns out that if you do this, the only content IIS will choose to serve is static HTML – no ASP or ASP.NET. If you hit an ASP page, you get this errror:

HTTP Error 404.3 – Not Found

The page you are requesting cannot be served because of the extension configuration. If the page is a script, add a handler. If the file should be downloaded, add a MIME map.

Now wouldn’t you think that if the person is going to the effort of enabling IIS in the first place, then they’d want to actually serve dynamic content and that should be the default?

Friendly error messages are enabled by default

You’ll get a lot farther if you disable the “friendly” error messages that are friendly I suppose, but not exactly helpful.

Enable parent paths

Double-click the ASP icon in the IIS manager, and set “Enable parent paths” to true. This is disabled by default for security reasons but is probably pretty common in legacy code.