Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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:\spreadsheetsexcel save *.xls *.csv or c:\spreadsheetsexcel save *.xls consolidated.csv Any help and advice gratefully received Len |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Best way to export from Excel to MySQL?
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 -- It is nice to be important, but it is more important to be nice "evillen" wrote: 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:\spreadsheetsexcel save *.xls *.csv or c:\spreadsheetsexcel save *.xls consolidated.csv Any help and advice gratefully received Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import MySQL data to Excel | Excel Discussion (Misc queries) | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Export to Microsoft Excel from IE | Excel Discussion (Misc queries) | |||
excel chart export error runtime 1004 | Charts and Charting in Excel | |||
How do I get data (tables) from IE to export to an EXCEL sreadshee | Excel Discussion (Misc queries) |