我从FTP服务器下载的txt文件,并使用.bat文件自动用此文件填充表格。
我已经可以下载文件,并且仅将一个文件添加到表中,这是到目前为止的代码:

@echo off
color 17
cd C:/Users/Silvia/Documents/Roboto
rename *.txt actual.txt
cd "C:\Users\Silvia\Documents\MySQL Server 5.6\bin"
mysql -h localhost -u root -ppass <C:\Users\Silvia\Documents\Roboto\crear.bat
pause
exit


这是crear.bat代码:

use Operacion15;
create table db(PATENTE varchar(4), ADUANA varchar(3), PEDIMENTO varchar(7), RFC varchar(13));
LOAD DATA LOCAL INFILE "C:/Users/Silvia/Documents/Roboto/actual.txt"
INTO TABLE db
FIELDS TERMINATED BY '|'
(PATENTE, ADUANA, PEDIMENTO, RFC);


如何使cicle在所有下载的文件中运行?

不胜感激您的帮助,谢谢

最佳答案

首先看一下FOR /F命令的工作方式,从在命令行窗口中键入的下一个命令开始(可以使用CopyPaste

for /F %G in ('dir /b "C:/Users/Silvia/Documents/Roboto\*.txt"') DO @echo %~fG


然后,您可以按以下方式修改代码:

@echo off
SETLOCAL
color 17

    rem download directory
set "_pathDownload=C:\Users\Silvia\Documents\Roboto"

set "_pathToCrear=%_pathDownload%"

    rem make sure a "backup_done" folder exists
set "_pathBackup=%_pathDownload%\backup_done"
md "%_pathBackup%\" 2>NUL

    rem working directory
set "_pathMySQLbin=C:\Users\Silvia\Documents\MySQL Server 5.6\bin"
pushd "%_pathMySQLbin%"

    rem LOCAL INFILE: elicit from crear.bat
for /F "tokens=5*" %%G in (
          'type "%_pathToCrear%\crear.bat"^|FINDSTR INFILE'
    ) do (
            set "_fileActual=%%~G"
)
    rem main loop
for /F %%G in ('dir /b "%_pathDownload%\*.txt"') DO (

           rem remove ECHO from next three ECHO-ed lines as soon as debugged
           rem moreover: at same time, remove ^ ahead of < from mysql line
    ECHO copy /B /Y "%%~fG" "%_fileActual%"
    ECHO mysql -h localhost -u root -ppass ^<"%_pathToCrear%\crear.bat"
         rem (optional) backup file treated
    ECHO move /Y "%%~fG" "C:\Users\Silvia\Documents\Roboto\backup_done\"
    ECHO(
         rem previous ECHO( line: for ECHO-ed output only to make it easy to survey
)
pause

exit


注意:


backup_done文件夹:这是示例名称(以及路径);
在批处理文件中使用的%百分号加倍(%%G代替%G);
路径中的项目以\反斜杠符号分隔;
echo copy …echo mysql …echo move …:仅对ECHO操作命令进行调试(以查看最终的脚本行为):请在调试代码之前立即删除所有echo前缀;
actual.txt文件的路径有所不同:您提供的批处理文件中的…\Silvia\……/Garber/…代码中的crear.bat


编辑:代码已改进并已注释

如果您下载的文件允许在发送到mysql之前进行合并,则脚本中的主循环部分可能会更简单(仅使用mysql调用),如下所示:

        rem
        rem include part of previous script here up to 'rem main loop'
        rem

        rem main loop
        rem merge downloaded files
type nul > "%_fileActual%"
for /F %%G in ('dir /b "%_pathDownload%\*.txt"') DO (

           rem remove ECHO from next ECHO-ed lines as soon as debugged
           rem moreover: at same time, remove all ^ ahead of > from mysql line
      if /I "%%~fG"=="%_fileActual%" (
               rem no merge
        ) else (
            ECHO type "%%~fG" ^>^> "%_fileActual%"
                 rem (optional) backup file treated
            ECHO move /Y "%%~fG" "C:\Users\Silvia\Documents\Roboto\backup_done\"
            ECHO(
    )

)
    mysql -h localhost -u root -ppass <"%_pathToCrear%\crear.bat"

pause
exit

10-05 20:41
查看更多