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;

Leave a comment

Your email address will not be published. Required fields are marked *