Best way to export from Excel to MySQL?

  • Thread starter Thread starter evillen
  • Start date Start date
E

evillen

I have over a thousand individual Excel files which contain data that I
would like to import into a MySQL database. Can anyone recommend an
efficient way I can achieve this?

I know that I can individually save each Excel file as a .csv file and
import that into MySQL but this is going to take me too long with the
number of files involved. What is the quickest way to "batch convert"
multiple .xls files to .csv? Ideally I would be able to convert
multiple .xls files to a single .csv file.

Is there a command line that I could run, something along the lines of:

c:\spreadsheets>excel save *.xls *.csv
or
c:\spreadsheets>excel save *.xls consolidated.csv

Any help and advice gratefully received

Len
 
For me, this is better done from some script language outside Excel, that
would do the following:
1.- Open a blank text file
2.- Cycle on all the Excel files that you need to export
3.- Open each Excel file, write the information you need on the text file
and close the Excel file
4.- Close and save the file.

This is something I did in vbscript for a similar problem, all the offset
are there for my particular case, but you can tweak it to fit your data
layout.

Const gPath = "C:\Surveys\"
Dim gAppExcel, gFile

Main
wscript.echo "Finalized"

Sub Main
Set gAppExcel = CreateObject("Excel.Application")

CreateLog "Data.txt"

Dim fso, f, fs, f1
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.getfolder(gPath)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = "xls" Then
ProcessWorkbook gPath & f1.Name
End If
Next

gFile.close
gAppExcel.Quit
End Sub

sub CreateLog (sFile)
Const ForWriting = 2
Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")
Set gFile = fso.OpenTextFile(gPath & sFile, ForWriting, True)
end sub

Sub ProcessWorkbook(strWorkbook)
Dim wbDatos, wsDatos, rDatos
Dim i, j
j = 5

set wbDatos = gAppExcel.Workbooks.Open (strWorkbook)
Set wsDatos = wbDatos.Worksheets(1)
Set rDatos = wsDatos.Range("C3")
With rDatos
Do While .Offset(i, 0).Value <> ""
'Gather data
Do While .Offset(-2, j).Value <> ""
gfile.writeline left(.Offset(-2, j).Value,3) & "|" &
..Offset(i, 0).Value & "|" & .Offset(i, 2).Value & "|" & .Offset(0, j).Value
j = j + 1
Loop
i = i + 1: j = 5
Loop
End With

wbDatos.Close False
End Sub
 
Back
Top