0

I have a problem, I must upload every week to a MySQL database hundreds of excel files .xlsx and .xlsm. I started whit the "LOAD DATA LOCAL INFILE" and converting all the files to CSV. The problem is when I convert hundreds of files to CSV I use a software "Convert XLS" but when I upload the files to the database I begin to have problems because some columns contain commas. I have been googling how to import a lot of xlsx or xlsm files without converting to csv but I didn´t find anything. I have tried with Microsoft SQL Server but it´s not an option because I had a lot of problems with that software and emmmm, hope you can help me, maybe finding a software to upload a lot of xlsx or xlsm files or I don´t know, because there are a lot of files. Thanks.

This is my code, I repeat this for eachfile, an excel formula helps me

    LOAD DATA LOCAL INFILE 'C:/Users/Daniel/Documents/BBVA/checkList/checklist calidad/todos/BBVA Check List Suc 5937 Sem 16 BC (C).CSV' INTO TABLE carga_checklist
    charset latin1
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';
    INSERT INTO checklist (select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE,AF,AG,AH,AN,AO,'354' as CR, (select AO from carga_checklist where AN like 'CALIFICACI%') AS CALIFICACION
    from carga_checklist 
    limit 76,1080);       
    insert into paso_id 
(D) (select D from carga_checklist);
    INSERT INTO checklist_id (select  '354' as CR, (SELECT D from paso_id where id = 1167) AS OBRA
    from paso_id
limit 80,1080);
    truncate table carga_checklist;
    truncate table paso_id;
Daniel Palomino
  • 19
  • 1
  • 5
  • when your problems are commas (=local & language settings), try to set your system to English/US or English/UK before exporting. and is merging the files an option before exporting? This question has been asked quite often on SO already – MikeD Apr 23 '15 at 17:41
  • @BK435 always the first sheet in the excel file and I'm using windows. – Daniel Palomino Apr 23 '15 at 18:27
  • @MikeD My system is already English/US. I have 25x files weekly and I created a "query generator" to extract the name and path of the file and upload them with "LOAD DATA LOCAL INFILE". every file is saved in a single table on the database. – Daniel Palomino Apr 23 '15 at 18:31
  • @BK435 Nop, my python skills are basic. Nop every file is saved in a single table. – Daniel Palomino Apr 23 '15 at 21:43
  • @BK435 I pasted my code in the main topic :) – Daniel Palomino Apr 23 '15 at 21:57
  • @BK435 Only to some data is applied the quotes, not all the data with the commas. – Daniel Palomino Apr 24 '15 at 21:03
  • that doesn't make any sense why would the app that you are using to convert only apply the quotes to some fields and not all the fields?...well if that is the case are the string fields that have comma(s) in them surrounded by quotes??? – BK435 Apr 24 '15 at 21:48

0 Answers0