software

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 quot;batch convertquot;
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:\spreadsheetsgt;excel save *.xls *.csv
or
c:\spreadsheetsgt;excel save *.xls consolidated.csv

Any help and advice gratefully received

LenFor 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 = quot;C:\Surveys\quot;
Dim gAppExcel, gFile

Main
wscript.echo quot;Finalizedquot;

Sub Main
Set gAppExcel = CreateObject(quot;Excel.Applicationquot;)

CreateLog quot;Data.txtquot;

Dim fso, f, fs, f1
Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
Set f = fso.getfolder(gPath)
Set fs = f.Files
For Each f1 In fs
If right(f1.Name,3) = quot;xlsquot; Then
ProcessWorkbook gPath amp; f1.Name
End If
Next

gFile.close
gAppExcel.Quit
End Sub

sub CreateLog (sFile)
Const ForWriting = 2
Dim fso

Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
Set gFile = fso.OpenTextFile(gPath amp; 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(quot;C3quot;)
With rDatos
Do While .Offset(i, 0).Value lt;gt; quot;quot;
'Gather data
Do While .Offset(-2, j).Value lt;gt; quot;quot;
gfile.writeline left(.Offset(-2, j).Value,3) amp; quot;|quot; amp;
..Offset(i, 0).Value amp; quot;|quot; amp; .Offset(i, 2).Value amp; quot;|quot; amp; .Offset(0, j).Value
j = j 1
Loop
i = i 1: j = 5
Loop
End With

wbDatos.Close False
End Sub

--
It is nice to be important, but it is more important to be nicequot;evillenquot; wrote:

gt; I have over a thousand individual Excel files which contain data that I
gt; would like to import into a MySQL database. Can anyone recommend an
gt; efficient way I can achieve this?
gt;
gt; I know that I can individually save each Excel file as a .csv file and
gt; import that into MySQL but this is going to take me too long with the
gt; number of files involved. What is the quickest way to quot;batch convertquot;
gt; multiple .xls files to .csv? Ideally I would be able to convert
gt; multiple .xls files to a single .csv file.
gt;
gt; Is there a command line that I could run, something along the lines of:
gt;
gt; c:\spreadsheetsgt;excel save *.xls *.csv
gt; or
gt; c:\spreadsheetsgt;excel save *.xls consolidated.csv
gt;
gt; Any help and advice gratefully received
gt;
gt; Len
gt;
gt;