Solved

Correct Code of Conversion from CSV Files

Posted on 2004-09-29
12
226 Views
Last Modified: 2008-01-09
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
 http://www.experts-exchange.com/Databases/MS_Access/Q_21129724.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.
0
Comment
Question by:TOPIO
  • 7
  • 5
12 Comments
 
LVL 10

Author Comment

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

look for  Q_21149677.zip
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12181728
Hi TOPIO

Please post some Input_IDs to look at,

thanks,

jaffer
0
 
LVL 10

Author Comment

by:TOPIO
ID: 12182160
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
0
 
LVL 10

Author Comment

by:TOPIO
ID: 12182850
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.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12183205
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
0
 
LVL 10

Author Comment

by:TOPIO
ID: 12183326
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Author Comment

by:TOPIO
ID: 12183754
Disregard the last comment I made an error on the update query
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 12188145
Sir;

I tried it on both csv files and it seems to work correctly this time,
Changes done:

There were two problems,
A- The IF statement did not do its job correctly (I don't know why!).
B- The DataType = "Replenishment" was not working accurately.

So here is what was done:

1- An IF statement to reject (Skip) each line which is not required,
yet I made it look for Two words in that line, just to make sure that the Product Description will not include any of these one words.

2- I didn't Delete the old code, I just put a Remark on it, so that you can see what I did.

3- This line was changed (nothing to do with the problem, but I though to let you know a better way)
If strInputFileName = "" Then Exit Sub  'GoTo outahere2

4- I changed all the LEFTs to INSTRs which can detect form the whole Textline.

5- Increased the Input_ID digits to 7. This is useful for Sorting Ascending and Descending.




Private Sub command2_Click()
    Dim TextLine As String
    'Dim CommaPlace() As String
     '   ReDim CommaPlace(30)
        Dim CommaPlace As Variant
        'Dim CommaPlace 'As String
    CommaPlace = Array("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "")
   
   
'Read csv
'original code        Input_ID = 0 'defaults
 'input_ID = DMax("[inputID]", "[TBL_Header]") + 1
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "QRY_DeleteTemporary"
  DoCmd.SetWarnings True

 Input_ID = Nz(DMax("[inputID]", "[TBL_Header]"), 0) + 1

Dim strFilter As String
Dim strInputFileName As String

'strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strFilter = ahtAddFilterItem(strFilter, "Text files (*.csv,*.txt)", "*.csv;*.txt")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
                Flags:=ahtOFN_HIDEREADONLY)



If strInputFileName = "" Then Exit Sub  'GoTo outahere2

        Open strInputFileName For Input As #1        ' Open Input file.
        'Open "C:\temporal\sample.csv" For Input As #1       ' Open Input file.
        Open "C:\temporal\Complete.csv" For Output As #2             ' Open Onput file.
         
    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" Or Left(TextLine, 5) <> "Grand" Or Left(TextLine, 4) <> ",Rec" Or _
'           Left(TextLine, 8) <> ",Planned" Or Left(TextLine, 19) <> ",,,,,,Replenishment" Then
       
        If InStr(TextLine, "Replenishment Center") Then DataType = "Replenishment": GoTo SkipThisItem
        If InStr(TextLine, "Rec") Then
            If InStr(TextLine, "Committed") Then GoTo SkipThisItem
        End If
        If InStr(TextLine, "Planned") Then
            If InStr(TextLine, "MTD") Then GoTo SkipThisItem
        End If
        If InStr(TextLine, "(OrderQty)") Then
            If InStr(TextLine, "CIP") Then GoTo SkipThisItem
        End If
        If InStr(TextLine, "Distribution Center") Then DataType = "Distribution": GoTo SkipThisItem
        If InStr(TextLine, "TOTAL") Then GoTo SkipThisItem
        If InStr(TextLine, "GRAND") Then GoTo SkipThisItem
       

'Get the Headers
'            If Left(TextLine, 6) = "Global" Then                                'Line that starts with Global
            If InStr(TextLine, "Global Product Detail") Then
                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
            ElseIf InStr(TextLine, "Planner ID") Then
                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
            ElseIf InStr(TextLine, "Product Description") Then
                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
            ElseIf InStr(TextLine, "Basic UM") Then
                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, "0000000") & "," & 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
'            If InStr(TextLine, "Replenishment Center") Then
'                DataType = "Replenishment"
'                Group2 = Group2 + 1
'                GoTo OutOfHere1
'            End If

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

'Get the Distribution
'            If Left(TextLine, 7) = "DelFlag" Then
'            If InStr(TextLine, "Distribution Center") Then
'                DataType = "Distribution"
'                Group3 = 0
'            End If
           
'            Group3 = Group3 + 1
           
'**Write Distribution to File
            If DataType = "Distribution" Then 'And Group3 >= 2 Then
                Print #2, Format(Input_ID, "0000000") & "," & DataType & "," & TextLine & ","
            End If
           
'        End If

SkipThisItem:
    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"
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QRY_AppendHeader", acViewNormal, acAdd
    DoCmd.OpenQuery "QRY_AppendReplenishment"
    DoCmd.OpenQuery "QRY_ReplenishmentLocation"
    DoCmd.OpenQuery "QRY_AppendDistribution"
    DoCmd.OpenQuery "QRY_DistributionAsterisk"
    DoCmd.OpenQuery "QRY_DistributionLocPriority"
'    DoCmd.OpenQuery "QRY_DeleteTemporary"
    DoCmd.SetWarnings True
   
   
Kill strInputFileName
Beep
'MsgBox "Finished"
'outahere2:


    Exit Sub


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

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



jaffer
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12192399
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
0
 
LVL 10

Author Comment

by:TOPIO
ID: 12195927
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
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12197633
Hey No problem man,

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

jaffer
0
 
LVL 10

Author Comment

by:TOPIO
ID: 12223923
thanks man!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now