PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
3 days, left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
I need help with a batch file import into MS Access or through MS SQL
Posted on 2011-09-04
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
-
2
2- +1
11 Comments
Active today
this can be done, by using vba codes.
* open the text file and read line by line
* parsing the lines and storing the items to the corresponding fields in the table
a sample db with the destination table and copy of the text file is needed
* upload a copy of both
* open the text file and read line by line
* parsing the lines and storing the items to the corresponding fields in the table
a sample db with the destination table and copy of the text file is needed
* upload a copy of both
hi Alan
I'd do something like this, which reads the source file and creates a new table merging the bin locations with the stock numbers and counts:
1) Create a new module (or use an existing one)
2) Write a new function like this one:
Then you should be able to load the data into a new table which will be called "NewTable" by running the code above and specifying the source file name
I hope that's of some interest, please let me know if you want any more information or help.
I'd do something like this, which reads the source file and creates a new table merging the bin locations with the stock numbers and counts:
1) Create a new module (or use an existing one)
2) Write a new function like this one:
Sub LoadSourceFile(sFilename As String)
Dim iFile As Integer
Dim sInputLine As String
Dim sLocCode As String
Dim sStockCode As String
Dim sSQL As String
Dim tblNewTable As New DAO.TableDef
Dim fldNew As DAO.Field
iFile = FreeFile
Open sFilename For Input As iFile
Set tblNewTable = CurrentDb.CreateTableDef("NewTable")
Set fldNew = tblNewTable.CreateField("Bin", DB_TEXT, 30)
tblNewTable.Fields.Append fldNew
Set fldNew = tblNewTable.CreateField("Stock", DB_TEXT, 30)
tblNewTable.Fields.Append fldNew
CurrentDb.TableDefs.Append tblNewTable
Set tblNewTable = Nothing
Set fldNew = Nothing
Do While Not EOF(iFile)
Line Input #iFile, sInputLine
If Left$(sInputLine, 3) = "LOC" Then
sLocCode = sInputLine
Else
sStockCode = sInputLine
If sLocCode <> "" Then
sSQL = "insert into NewTable (Bin, Stock) values ('" & sLocCode & "','" & sStockCode & "')"
CurrentDb.Execute sSQL
End If
End If
Loop
Close
End Sub
Then you should be able to load the data into a new table which will be called "NewTable" by running the code above and specifying the source file name
I hope that's of some interest, please let me know if you want any more information or help.
Active 1 day ago
Batch.TXT
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
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
Active today
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
Active 1 day ago
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
Active 1 day ago
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
Active 1 day ago
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
Featured Post
What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Failover Cluster Primary Nodes Current Vote = 0 | 5 | 36 | |
| TDE for SQL Web Edition | 1 | 42 | |
| how to use ROW_NUMBER() correctly | 8 | 44 | |
| SqlServer amend PK column | 5 | 15 |
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
- Phishing
- Email Servers
- Microsoft Access
- Microsoft IIS Web Server
- Email Software
- The Email Laundry
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string.
Specify the first argument, which is the expression to be returned:
Specify the second argument, which …
Suggested Courses
Course of the Month3 days, left to enroll
752 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.