Solved

I need help with a batch file import into MS Access or through MS SQL

Posted on 2011-09-04
11
364 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:caandal
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 36480588
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
0
 
LVL 10

Accepted Solution

by:
plummet earned 400 total points
ID: 36480673
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:

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

Open in new window


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.
0
 

Author Comment

by:caandal
ID: 36480675
  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
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36481082
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
0
 

Author Comment

by:caandal
ID: 36481098
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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36481103
OK, and what of my other questions?

:)
0
 

Author Comment

by:caandal
ID: 36481105
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
0
 

Author Comment

by:caandal
ID: 36481293
Hi Caprocorn1

Did you get the files?

Regards
Alan
0
 

Author Comment

by:caandal
ID: 36481304
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
0
 
LVL 10

Expert Comment

by:plummet
ID: 36481351
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
0
 

Author Closing Comment

by:caandal
ID: 36482361
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question