Bulk import of CSV with some columns only

Good day, I am using HeidiSQL since many years, which saved me a lot of time when I do import weekly CSV files. All my databases are on local Linux servers within ProxmoxVMs, working well. User side (GUI) is still Windows.

Now I have a new challenge... I have 1.400ish CSV files, each having two lines only (header and 1data), but 91 columns of which I only do need a few. So I need to figure out how to do the mass import, and second one how to import the required columns only. I searched the web for almost two hours now and ended up with lots of solutions involving PHP and shellscripts. Tried some solutions but mainly failed with more or less clear errors. Before further digging into the scripts (neither do I know PHP nor did I shellscripting yet) I thought this forum might be a place to ask for help. Maybe HeidiSQL can be used in a smart way or somebody has a proven way which works. Just point me in the right direction please :o) Thanks J.

SELECT nameColumnn1 ,nameColumn2 FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'EXCEL 12.0;DataBase=\path_to_file\fileName.csv;HDR=YES', 'SELECT * from [Sheet1$]') AS ex

Quite a challenging task. HeidiSQL cannot load multiple files per batch, so I suppose you really need a Windows PowerShell or Linux shell script here. You should be able to loop through a folder and execute such a command line for each file:

mysql.exe -hlocalhost -uroot --execute"LOAD DATA INFILE..."

The second thing is you say you want only a few columns from your files. MySQL generally supports that, with a special syntax where you retrieve all fields first and then only assign a few of them to your table:

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE mytable
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'
(@col1, @col2, @col3) -- all CSV fields
SET
  name=@col4,
  id=@col2;
  -- do nothing with @col3 ...

@ansgar Thanks a lot for the information, this sounds feasible, even for me ;) I'll go down this route later today