Multiple CSV Import

lordrom posted 12 months ago in General

I have 345 csv files to import into mysql. All identical, all to be appended to an existing table.

I can import one by one using the import csv file screen, but is there a way to import all files from one directory?


lordrom posted 12 months ago
oops, should have been in import/export section...
ansgar posted 12 months ago
You could copy the LOAD DATA command produced by HeidiSQL's import dialog from the SQL log once, then paste it 345 times into a query tab, and modify the filename in these commands. Should be the most efficient way.
lordrom posted 12 months ago
and i used a routine in excel vba to create the strings automatically. real devs wouldn't use vba, but that's my limit.
Sub createsqlloadfiles()

Dim directorytouse

directorytouse = "your directory"

sFileName = Dir(directorytouse)
Do While sFileName <> ""
Application.ScreenUpdating = False
ActiveCell.value = "LOAD DATA LOCAL INFILE 'C:\\Users\\Data\\csv\\" & sFileName & "' IGNORE INTO TABLE `ac88`.`data` CHARACTER SET latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '" & Chr(34) & "' ESCAPED BY '" & Chr(34) & "' LINES TERMINATED BY '\r\n' (`Date`, blah, blah);"
ActiveCell.Offset(1, 0).Select

sFileName = Dir

End Sub

Thanks for your help, and hope my routine can speed things up for others.

Please login to leave a reply, or register at first.