TOPIO
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.
(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"
'********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"
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
'*************************
sample.csv file.
Will be uploaded to an easy to acces website.
Hi TOPIO
Please post some Input_IDs to look at,
thanks,
jaffer
Please post some Input_IDs to look at,
thanks,
jaffer
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
in http://www.geocities.com/topio/demodata/
the file is Q_21149677.zip 29-Sep-2004 10:23 210k
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.
'**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
I will work on it tomorrow and test it against both the new and the old data too.
jaffer
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.
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.
ASKER
Disregard the last comment I made an error on the update query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
I am glad I was of help,
and thanks for the points and the grade.
You don't talk much, do you!
jaffer
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
Just drop me a line if I can be of help.
jaffer
ASKER
thanks man!
ASKER
http://www.geocities.com/topio/demodata/
look for Q_21149677.zip