# Solved: Batch script is it possible?



## sconder (Nov 27, 2012)

I need to create a batch script that will search a text file for varying 6 digit numbers (orders) and date, then use that data to execute an SQL query and with the returned data append the original text file....can this be done and how do I do it???


----------



## Squashman (Apr 4, 2003)

An example of the text file would help and what you want to match.
And an example of the output would help as well.

I know nothing about SQL. I assume you know that part.


----------



## sconder (Nov 27, 2012)

Ok below is an example of the text files being scanned:

%BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
%END%
"Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision"
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "", "SEAL, VITON", "E"

Items in Red are what I am trying to get and will be different in each file. Once I have those I will use the following SQL Query

SELECT [LotKey]
FROM [Access].[dbo].[LotMaster]
where [WorkOrderNumber]=142796
or [PurchaseOrderNumber]=142796
and [Date]='11/15/12'
 
Then append the text file previous with the lotkey returned in the second empty "" spot. I should be able to get the "" to the last spot if that is too difficult.

Thanks,


----------



## Squashman (Apr 4, 2003)

So you need to find the DATE and some other number in this line?

```
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "", "SEAL, VITON", "E"
```
Is there something within the line that is always constant to search for that specific line?

Is the number always the 3rd delimited column?
Is the DATE always the 6th delimited column?


----------



## sconder (Nov 27, 2012)

Yes, you are correct the position of the date and order number do not change only the values.


----------



## Squashman (Apr 4, 2003)

sconder said:


> Yes, you are correct the position of the date and order number do not change only the values.


That only answers 2 of my questions.


----------



## sconder (Nov 27, 2012)

"So you need to find the DATE and some other number in this line?"

Yes, I need the DATE and the ORDER NUMBER, which is the 6 digit number.

"Is there something within the line that is always constant to search for that specific line?"

No, the DATE, ORDER and all other items on that line are variable. The previous line is static.

"Is the number always the 3rd delimited column?"
Yes.

"Is the DATE always the 6th delimited column?"Yes.


----------



## Squashman (Apr 4, 2003)

Is it always the 4th line in the file?


----------



## sconder (Nov 27, 2012)

Yes it is always the fourth line


----------



## Squashman (Apr 4, 2003)

Myfile.txt

```
%BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
%END%
"Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision"
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "", "SEAL, VITON", "E"
```
sconder.bat

```
@echo off

For /f skip^=3^ tokens^=5^,11^ delims^=^" %%G in (myfile.txt) do (
	SET POnum=%%~G
	SET Rdate=%%~H
)
echo %POnum% 
echo %Rdate%
```
Output

```
C:\batch files\delims>sconder.bat
142796
11/15/2012

C:\batch files\delims>
```


----------



## sconder (Nov 27, 2012)

Perfect that is exactly what I need for the first part, now the second would be taking the sql results below and appending them to this original file.

sql query returns:

LotKey 
--------------------
309176
(1 rows affected)

I just want to add the 309176 to original.txt


----------



## sconder (Nov 27, 2012)

This is the text file mysql.txt (FYI):

LotKey 
--------------------
309176
(1 rows affected)


----------



## Squashman (Apr 4, 2003)

You need to explain how you want your final output to look like. I am a visual person.


----------



## sconder (Nov 27, 2012)

Not a problem, I really appreciate the help. So original file is this:

%BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
%END%
"Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision"
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "", "SEAL, VITON", "E"

Then after I apply your first script and my sql query I want the 309176 in mysql.txt to display in the original as so:

%BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
%END%
"Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision"
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "*309176*", "SEAL, VITON", "E"

I can move it to the last position if that makes it easier for the append operation.


----------



## Squashman (Apr 4, 2003)

You cannot in place edit a text file with batch. You need to create a temp file first with your desired output and then you can delete the old file and rename the temp file to the original.

I will post something for you tomorrow. Maybe you will get lucky and Foxidrive will come around and finish this up tonight. I believe he lives a few time zones ahead of me. He has seen that for cmd syntax on another forum we are on.


----------



## foxidrive (Oct 20, 2012)

Is this file only 4 lines in length all the time?



> %BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
> %END%
> "Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision"
> "DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "*309176*", "SEAL, VITON", "E"


----------



## foxidrive (Oct 20, 2012)

With the next two files, and the batch below that

File.csv

```
%BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
%END%
"Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision"
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "", "SEAL, VITON", "E"
```
mysql.txt

```
LotKey
--------------------
309176
(1 rows affected)
```
appenditem.bat

```
@echo off
for /f "skip=2 delims=" %%a in (mysql.txt) do if not defined item set item=%%a
setlocal EnableDelayedExpansion
set c=0
del "newfile.csv" 2>nul
for /f "delims=" %%a in (file.csv) do (
set /a c=c+1
if !c! EQU 3 (
>>"newfile.csv" echo %%a, "ITEM"
) else (
if !c! EQU 4 (
>>"newfile.csv" echo %%a, "%item%"
) else (
>>"newfile.csv" echo %%a
)
)

)
pause
```
and it creates this file newfile.csv with ITEM appended to the data at the end of the line.

```
%BTW% /AF="C:\IDEX\Labels\RECEIVING.btw" /D="%Trigger File Name%" /PRN="\\MPVSPRINT1\MPVPR431" /R=3 /C= /P /DD
%END%
"Route code", "Route Code Desc", "PO Number", "Item Number", "Location", "Date Received", "Quantity Received", "UOM", "User", "Number of labels", "Lot Number", "Item Description", "Item Revision", "ITEM"
"DKCR", "Receive w/out Insp-cert step", "142796", "7881", "PF", "11/15/2012", "3000", "EA", "RMIKITYUK", "", "", "SEAL, VITON", "E", "309176"
```


----------



## sconder (Nov 27, 2012)

Thanks! This is what I asked for so I will mark this complete.


----------



## sconder (Nov 27, 2012)

One last question, when tweaking and making everything work I am running into now the sql script is returning: 
LotKey 
--------------------
310562
(1 rows affected)

So there are now blank spaces in the new file, how do I get rid of those??

Thanks again, some said this was impossible! (the whole task not getting rid of blank space)


----------



## sconder (Nov 27, 2012)

The previous post doesnt show it but there are 14 blank spaces from the left of the file to the number 310562.


----------



## foxidrive (Oct 20, 2012)

Learn how to use code tags so that spaces are shown. (on the advanced editor, mark the text, click on #)

There is a change in line 3 below which should get rid of the spaces. Added line 2 to ensure the variable is set.


```
@echo off
set "item="
for /f "skip=2" %%a in (mysql.txt) do if not defined item set item=%%a
setlocal EnableDelayedExpansion
set c=0
del "newfile.csv" 2>nul
for /f "delims=" %%a in (file.csv) do (
set /a c=c+1
if !c! EQU 3 (
>>"newfile.csv" echo %%a, "ITEM"
) else (
if !c! EQU 4 (
>>"newfile.csv" echo %%a, "%item%"
) else (
>>"newfile.csv" echo %%a
)
)

)
pause
```


----------



## sconder (Nov 27, 2012)

Thanks again, I am learning alot!


----------

