Link to home
Start Free TrialLog in
Avatar of TOPIO
TOPIOFlag for United States of America

asked on

Correct Code of Conversion from CSV Files

I have a program that takes CSV files an converts them into an access database

(You can see the long history of this conversion program in
 https://www.experts-exchange.com/questions/21129724/Get-Data-into-Acces-97-From-Excel-2000-Automatically.html )

Basically the system takes the file from the CSV files and separates into three main data sections
For every Code (part Number) There's a file section with the following.

HEADER section for Part Number A
REPLENISHMENT section for Part Number A
DISTRIBUTION section for Part Number A
HEADER section for Part Number B
REPLENISHMENT section for Part Number B
DISTRIBUTION section for Part Number B

ETC.

And the program works perfectly for these files,
the problem is that I have some files where the part Number will have several sections as follows
HEADER section for Part Number A
REPLENISHMENT section 1 for Part Number A
DISTRIBUTION section 1 for Part Number A
REPLENISHMENT section 2 for Part Number A
DISTRIBUTION section 2 for  Part Number A


and the information extracted will be only
REPLENISHMENT section 1 for Part Number A
DISTRIBUTION section 1 for Part Number A
DISTRIBUTION section 2 for  Part Number A

(second replenishment section is missing)
The program will take the csv file, filter the information into a temporary  text file
and then input the data back into the tables  the correction in the code needs to happen before sending the info
for the temporary file.

the requirements to run this file are:
a Folder called C:\temporal\
the data file Sample.csv
a blank Text file complete.csv

Here's the code.



Private Sub Command4_Click()
    Dim TextLine As String
    Dim CommaPlace As Variant
    CommaPlace = Array("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")
   
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QRY_DeleteTemporary"
    DoCmd.SetWarnings True
    Input_ID = Nz(DMax("[inputID]", "[TBL_Header]"), 0) + 1

        Open "C:\temporal\sample.csv" For Input As #1       ' Open Input file.
        Open "C:\temporal\Complete.csv" For Output As #2             ' Open Onput file.

'********here's the loop where I think the problem Is.
         
    Do While Not EOF(1)    ' Loop until end of file.
   
        Line Input #1, TextLine    ' Read line into variable.

        'Avoid the Total and Grans lines, otherwise read the rest
        If Left(TextLine, 5) <> "Total" And Left(TextLine, 5) <> "Grand" And Left(TextLine, 4) <> ",Rec" And _
           Left(TextLine, 8) <> ",Planned" And Left(TextLine, 19) <> ",,,,,,Replenishment" Then

'Get the Headers
            If Left(TextLine, 6) = "Global" Then                                'Line that starts with Global
                Group1 = 0
                Group2 = 0
                Group3 = 0
                Input_ID = Input_ID + 1
                DataType = "Header"
                Group1 = Group1 + 1
                CommaPlace = splitString(TextLine, ",")
                Input_Date = Trim(Mid(CommaPlace(1), 6))

            ElseIf Left(TextLine, 10) = "Planner ID" Then                       'Line that starts with Planner ID
                Group1 = Group1 + 1
                CommaPlace = splitString(TextLine, ",")
                Input_PlannerID = Trim(Mid(CommaPlace(0), 12))
                Input_PlannerName = Trim(Mid(CommaPlace(1), 14))

            ElseIf Left(TextLine, 7) = "Product" Then                           'Line that starts with Product
                Group1 = Group1 + 1
                CommaPlace = splitString(TextLine, ",")
                Input_Product = Trim(Mid(CommaPlace(0), 9))
                Input_ProductDescription = Trim(Mid(CommaPlace(1), 22))

            ElseIf Left(TextLine, 4) = "Cube" Then                              'Line that starts with Cube
                Group1 = Group1 + 1
                CommaPlace = splitString(TextLine, ",")
                Input_Cube = Trim(Mid(CommaPlace(0), 6))
                Input_Layer = Trim(Mid(CommaPlace(1), 7, 6))
                Input_Pallet = Trim(Mid(CommaPlace(2), 8, 6))
                Input_BasicUM = Trim(Mid(CommaPlace(3), 10))
                Input_Factor = Trim(Mid(CommaPlace(4), 8))

            End If

'**Write Header to File
            If Group1 = 4 Then
Print #2, Format(Input_ID, "0000") & "," & DataType & "," & Input_Date & "," & Input_PlannerID & "," & Input_PlannerName & _
"," & Input_Product & "," & Input_ProductDescription & _
"," & Input_Cube & "," & Input_Layer & "," & Input_Pallet & "," & Input_BasicUM & "," & Input_Factor & ","
                Group1 = 0
            End If
           

'Get the Replenishment
            If Left(TextLine, 3) = "Loc" Then
                DataType = "Replenishment"
                Group2 = Group2 + 1
                GoTo OutOfHere1
            End If

'**Write Replenishment to File
            If DataType = "Replenishment" And Group2 = 1 Then
                Print #2, Format(Input_ID, "0000") & "," & DataType & "," & TextLine & ","
                Group2 = 10
            End If
OutOfHere1:

'Get the Distribution
            If Left(TextLine, 7) = "DelFlag" Then
                DataType = "Distribution"
                Group3 = 0
            End If
           
            Group3 = Group3 + 1
           
'**Write Distribution to File
            If DataType = "Distribution" And Group3 >= 2 Then
                Print #2, Format(Input_ID, "0000") & "," & DataType & "," & TextLine & ","
            End If
           
        End If
       
    Loop
    Close #1    ' Close file.
    Close #2
   
   
'+++++
    Open "C:\temporal\Complete.csv" For Input As #1
   
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
   
    On Error GoTo Err_NoField
   

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("select * from tblTempComplete2")

     
    Do While Not EOF(1)
    Line Input #1, TextLine
    CommaPlace = splitString(TextLine, ",")
   
        rs1.AddNew

            rs1!Field1 = CommaPlace(0)
            rs1!Field2 = CommaPlace(1)
            rs1!Field3 = CommaPlace(2)
            rs1!Field4 = CommaPlace(3)
            rs1!Field5 = CommaPlace(4)
            rs1!Field6 = CommaPlace(5)
            rs1!Field7 = CommaPlace(6)
            rs1!Field8 = CommaPlace(7)
            rs1!Field9 = CommaPlace(8)
            rs1!Field10 = CommaPlace(9)
            rs1!Field11 = CommaPlace(10)
            rs1!Field12 = CommaPlace(11)
            rs1!Field13 = CommaPlace(12)
            rs1!Field14 = CommaPlace(13)
            rs1!Field15 = CommaPlace(14)
            rs1!Field16 = CommaPlace(15)
            rs1!Field17 = CommaPlace(16)
            rs1!Field18 = CommaPlace(17)
            rs1!Field19 = CommaPlace(18)
            rs1!Field20 = CommaPlace(19)
            rs1!Field21 = CommaPlace(20)
            rs1!Field22 = CommaPlace(21)
            rs1!Field23 = CommaPlace(22)
            rs1!Field24 = CommaPlace(23)
            rs1!Field25 = CommaPlace(24)
            rs1!Field26 = CommaPlace(25)
           
ContinueNext:
        rs1.Update
       
    Loop
       
    Close #1

    rs1.Close
    Set rs1 = Nothing
'+++++

    MsgBox "Text import to a Temporary Table tblTempComplete is completed"
Exit Sub

Err_NoField:
    If Err.Number = 9 Then
        Resume Next
        GoTo ContinueNext

    Else
        MsgBox Err.Description & " " & Err.Number
    End If

End Sub

'************************************END OF CODE **********************

sample.csv file.
Will be uploaded to an easy to acces website.
Avatar of TOPIO
TOPIO
Flag of United States of America image

ASKER

You can get the sample files for this question at
http://www.geocities.com/topio/demodata/

look for  Q_21149677.zip
Hi TOPIO

Please post some Input_IDs to look at,

thanks,

jaffer
Avatar of TOPIO

ASKER

I have Uploaded an updated version of  all the files
in  http://www.geocities.com/topio/demodata/
the file is  Q_21149677.zip          29-Sep-2004 10:23   210k
Avatar of TOPIO

ASKER

This is the section that has the problem

'**Write Replenishment to File
            If DataType = "Replenishment" And Group2 = 1 Then
                Print #2, Format(Input_ID, "0000") & "," & DataType & "," & TextLine & ","
                Group2 = 10
            End If

On the first time that we get a replenishment line group2 will be 1
but the second time that replenishment appears  group2 will be 12
therefore this data will not be written.
there is something wrong, as the Totals and the Grands are being copied too, where they are NOT suppose to.
I will work on it tomorrow and test it against both the new and the old data too.

jaffer
Avatar of TOPIO

ASKER

There is no problem with the copying of totals and Grands I can eliminate them (using the update querys)
the problem is that my "correction" in the code as follows

            ' If DataType = "Replenishment" And Group2 = 1 Then '***************Original Line
            If DataType = "Replenishment" And ((Group2 = 1) Or (Group2 = 12)) Then '*****new line for 2 replenishment center data

Duplicates all the data at the distribution level.
Avatar of TOPIO

ASKER

Disregard the last comment I made an error on the update query
ASKER CERTIFIED SOLUTION
Avatar of jjafferr
jjafferr
Flag of Oman image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You have a more solid code with belts and bracelets.

I am glad I was of help,
and thanks for the points and the grade.

You don't talk much, do you!

jaffer
Avatar of TOPIO

ASKER

Sorry jaffer Immediatly after receiving your code i went into the database and applied it into a copy of the form, I was having some issue with the database growing so I included some code to insure that  the database is cleaned up and compressed every once in awhile and the I went head on into the reports, but I Had a difficult time getting them up and running since I was trustig the default  reporting wizard but since the replenishment lines are two or more on ocasion the default format for the report was not working so I wne in and did all the files that required it to be calculated on the report and I sent all the distribution data to a sub report, went  into a meeting and came back with 8 change requests, so basicallyI had a long day but very productive i might say
Hey No problem man,

Just drop me a line if I can be of help.

jaffer
Avatar of TOPIO

ASKER

thanks man!