How do I read the content of 2 different csv files and generate an output file based on some criteria

Question

0.00/5 (No votes)

See more:

I have two different files, fileA contains employee data (ID and townCode) while file B contains field office data (OfficeID, Town, TownCode, officeCapacity) and a town can contain more than one office. i want to distribute these employees to the offices in the town against their names evenly

What I have tried:

I read the 2 files simulteanously and created another file to hold the last office it was distributed to. My challenge is that the distribution is not even and it picks only 2 offices for the distribution

VB

For Each line In File.ReadLines(src1)
           Dim STRDATA() As String = line.Split({","c})
           employeeID = STRDATA(0)
           EmpTownCode = STRDATA(1)

           For Each lines In File.ReadLines(src2)
               Dim STRDATA2() As String = lines.Split({","c})

               OfficeID = STRDATA2(0)
               StrTownName = STRDATA2(1)
               StrTownCode = STRDATA2(2)
               OfficeCapacity = STRDATA2(3)
               For Each last In File.ReadLines(src3)
                   lastCentre = last.Substring(0, 16)
               Next

               If StrRegTown = StrTownCode Then
                   If StrCentreRef = lastCentre.Last Then
                       Exit For
                   End If
                   FileOpen(intoutfilenum, outfilepath, OpenMode.Append)
                   WriteLine(intoutfilenum, EmployeeID, TownCode, OfficeID, OfficeCapacity)
                   FileClose(intoutfilenum)
                   FileOpen(intoutfilenum2, outfilepath2, OpenMode.Append)
                   WriteLine(intoutfilenum2, OfficeID)
                   FileClose(intoutfilenum2)
                   Exit For
               End If

Posted 22-Apr-21 9:08am

ketura

Updated 22-Apr-21 18:07pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Garth J Lancaster · Answer 1 · 2021-04-22T18:07:00

I think reading the two data files and attempting to solve this problem at the same time is not such a nice approach.

I would

1) read each file into a data structure - possibly array or 'list'
2) sketch out a distribution algorithm on paper. You don't indicate relative 'numbers' of employees vs offices - but I'd assume that for every town -> office, you want at least 1 employee, distributing up to OfficeCapacity employees q) what happens if all town -> offices are allocated OfficeCapacity employees ? do you assign them to another (nearest) town ?
3) translate the sketch algorithm from 2 into code - depending on how complex your rules are, you may need to change the data structures used and/or add back-tracking