caandal
asked on
I need help with a batch file import into MS Access or through MS SQL
Hi Gurus
I have a batch file that comes from a scanner in the form of a txt file. The file looks like this:
LOC-AA-AB-1,1
31-3-188-1702,25
7-3-188-1699,30
7-3-188-1700,12
LOC-C-C-1,1
7-3-189-1707,45
9-1-8-1040,30
The Items With LOC as the first three letters are Bin locations
The Items following need to be allocated to the bins above, so looking at this file :
LOC-AA-AB-1,1 -- Is bin1
31-3-188-1702,25 - stock - bin1
7-3-188-1699,30 - stock - bin1
7-3-188-1700,12 - stock - bin1
LOC-C-C-1,1 -- bin2
7-3-189-1707,45 - stock - bin2
9-1-8-1040,30 - stock - bin2
The number after the comma denotes qty. I am not sure how to get this right without creating individual batch files which is not practical for my client. Any help with this will be very much appreciated.
Regards
Alan
I have a batch file that comes from a scanner in the form of a txt file. The file looks like this:
LOC-AA-AB-1,1
31-3-188-1702,25
7-3-188-1699,30
7-3-188-1700,12
LOC-C-C-1,1
7-3-189-1707,45
9-1-8-1040,30
The Items With LOC as the first three letters are Bin locations
The Items following need to be allocated to the bins above, so looking at this file :
LOC-AA-AB-1,1 -- Is bin1
31-3-188-1702,25 - stock - bin1
7-3-188-1699,30 - stock - bin1
7-3-188-1700,12 - stock - bin1
LOC-C-C-1,1 -- bin2
7-3-189-1707,45 - stock - bin2
9-1-8-1040,30 - stock - bin2
The number after the comma denotes qty. I am not sure how to get this right without creating individual batch files which is not practical for my client. Any help with this will be very much appreciated.
Regards
Alan
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
caandal,
Is this a one-time task, or will this be done day after day?
Consider a batch such as LOC-AA-AB-1. Will it only ever appear once in a file? Or is there a possibility that it will appear in today's file, and then again in next Wednesday's file? If it can appear in >1 file, will the later appearance append to the existing data for that batch, or replace the existing data?
Patrick
Is this a one-time task, or will this be done day after day?
Consider a batch such as LOC-AA-AB-1. Will it only ever appear once in a file? Or is there a possibility that it will appear in today's file, and then again in next Wednesday's file? If it can appear in >1 file, will the later appearance append to the existing data for that batch, or replace the existing data?
Patrick
ASKER
Hi Patrick
This will be done hour after hour on a daily basis. We are using batch scanners to go into a very large warehouse and collect data. If it was once off it would not be an issue.
Alan
This will be done hour after hour on a daily basis. We are using batch scanners to go into a very large warehouse and collect data. If it was once off it would not be an issue.
Alan
OK, and what of my other questions?
:)
:)
ASKER
Patrick
ASorry I did not answer your questions completely.
Data will always be appended to the file on in or out basis - thereby giving us stock levels of the product. Th location is there for logistic purposes and for picking slips.
LOC-AA-AB-1 is a location and will be used on an ongoing basis as will all the storage bins. The bins are actually areas set aside in varying sizes depending on what is being stored.
Alan
ASorry I did not answer your questions completely.
Data will always be appended to the file on in or out basis - thereby giving us stock levels of the product. Th location is there for logistic purposes and for picking slips.
LOC-AA-AB-1 is a location and will be used on an ongoing basis as will all the storage bins. The bins are actually areas set aside in varying sizes depending on what is being stored.
Alan
ASKER
Hi Caprocorn1
Did you get the files?
Regards
Alan
Did you get the files?
Regards
Alan
ASKER
Hi Plummet
Pretty late here in South Africa. Been checking in on my mails as the evening wore on. I will try your solution in the a.m. - makes sense tho
Regards
Alan
Pretty late here in South Africa. Been checking in on my mails as the evening wore on. I will try your solution in the a.m. - makes sense tho
Regards
Alan
Hi Alan
I think so, and with further refinement it could extract the exact data you need into the correct target fields. I'm happy to help some more if you think that's the way forward.
Cheers
John
I think so, and with further refinement it could extract the exact data you need into the correct target fields. I'm happy to help some more if you think that's the way forward.
Cheers
John
ASKER
Thanks Plummet and Capricorn1
I used Plummets solution but did not need to create tables. As I am using a the same table to process data in each instance
Thanks once again for the help
Regards
Alan
I used Plummets solution but did not need to create tables. As I am using a the same table to process data in each instance
Thanks once again for the help
Regards
Alan
ASKER
dbo_tLocation is where the ID for the bin will come from AA-AB-1 is LocationID and will be written into dbo_tIssRet LocationID against the ProducID that is returned from dbo_tProductID.
dbo-tIssRet-RESULT.xls
The * RESULT Excel spreadsheet is what I am trying to achieve
sample.mdb