我从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
命令的工作方式,从在命令行窗口中键入的下一个命令开始(可以使用Copy
和Paste
:
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