Best way to export from Excel to MySQL?



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
c:\spreadsheets>excel save *.xls consolidated.csv

Any help and advice gratefully received



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

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

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

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
i = i + 1: j = 5
End With

wbDatos.Close False
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question