Cleaning up data for import to database

[webmaniac]

Hello all, I am new here so I hope i do this in the correct forum.

I have an excel user who each week receives an EDI transfer of orders from one, two or three different companies. Each EDI may have one, two or all three depending. THere are only three companies ever.

The problem that I have is that the data is formatted so crazily that I need some major clean up module before I can import it into a database to work with the data. I am not even sure VB can do it. I have attached an example of the raw data and an example of the ideal data. The data always appears like this so one instance would work ongoing.

Essentially what I need to do is eliminate the crap rows of junk data and assign the company name and the "for" date to each part ordered so that the data would look like this

Company Name Date Needed Model Part
Company 1 7/10/2006 blah blah
Company 2 7/10/2006 blah blah
Company 3 7/10/2006 blah blah
Company 3 7/10/2006 blah blah

What you will see in the attachment is that the company name and date needed are listed in columns above all of the data that corresponds. Below the company in the same column can be either a number or the word "for" or "line" for some reason. I need to somehow take the company name and move it left and fill it down for the corresponding data (sheet two of the attachment shows how it would ideally look.)

If anyone can give me some direction with this I would be most appreciative or if you could direct me to something to read on this. ANYTHING closer would be greatly appreciated as well. ... Thank you in advance.

[brucevde]

This is a simple Tab Delimited file. You can easily read through the file using the following code

Dim lngFile1 As Long
    Dim strBuffer As String
    Dim strFields() As String
    
    Dim strDate As String
    
    lngFile1 = FreeFile
    Open "c:\uncorrected.txt" For Input As lngFile1
    
    Do
        Line Input #lngFile1, strBuffer
        strFields = Split(strBuffer, vbTab)
        Select Case strFields(0)
            Case "LINE"
                If StrComp(strFields(1), "Chassis", vbTextCompare) <> 0 Then
                    'the strFields array now contains the information for Line, Chassis, Part etc..
                    'otherwise it contains the header info so ignore the line
                End If
            Case "For"
                'contains the date
                strDate = strFields(2)
            Case "FOR"
                'the strfields array is populated with the FOR information
            Case Chr$(12) 'Page line, the next line contains the Company Name
                Line Input #lngFile1, strBuffer
                strFields = Split(strBuffer, vbTab)
            Case Else
                If Left$(strFields, 3) = "F23" Then 'may need to change F23 to handle more types
                Else
                   'Unknown line - raise an error or ignore
                End If
        End Select
            
    Loop Until EOF(lngFile1)
    
    Close lngFile1

For each Select Case you would create an output line and then print it to the corrected.txt file when ready.

As long as the File Layout stays consistent this code will work.

[mendhak]

Have you considered opening it in Excel? Getting closer to the source of the creation would be ideal, since you'd have a better chance of dealing with it. If you open it in Excel and it appears... well, then you can use an Excel object to loop through it, and create your corrected text file.

[webmaniac]

Thank you both! I feel like we are getting somewhere. Yes I open the file in excel but you cant post excel files as attachments on this site. How would the module differ from the txt file module above? I want to go the most effective route.

Thanks again!

[szlamany]

But that is not an EDI transmission - that appears to be a report/list file from a mainframe type of machine.

You need to get rid of the headings - brucevde is showing you the logic to process, in a loop, and ignore meaningless lines...

Can you get that data without the headings?

If not I would develop a silly-little filter program to process the input file and give clean output from that. Then a bulk insert can be done from that clean file.

[webmaniac]

thanks! it is an edi report that is output into a text file. i can actually jsut disregard the headings. its not great for normalizaiton of data but it wont stop the process. Thank you very much!!!