Reading CSV File 1

[T111]

Hi,

I have a CSV file that contains over 12000 lines when opened with excel. It stores employee details and I need to read about 30 fields from each employee's record. Employee’s details are 56 Lines long e.g. first employee details are on line 8 to Line 64. Employee name is on line 8 column 3; employee date of birth is on line 10 column 2 etc. Employee no 2 details start on line 64 and employee number 3 details start on line 120 etc. I need to know is there any way of setting up a new worksheet in that I copy (by linking the original fields), if I set up two employee records (keeping all the employees 30 fields on one column) and then filling the series so I can read all the 200 employees records in a new CSV file that only contains 200 lines e.g. one employees record per line. This way the program can loop through all the records with little code. I tried but it didn’t work any suggestions are welcome.

Thanks.

 

[marlinatgls]

create your new file structure as <newfile>.
postion record pointer to first employee
in <csvfile>. (go 8)
do while not eof()
replace <newfile>.name with <csvfile>.csvfld3
skip 2
replace <newfile>.dob with <csvfile>.csvfld2
... and so on ...
skip <to next employee name record>
enddo
not elegant will get the job done.

 

[marlinatgls]

p.s. append blank record to <newfile>
at the beginning of each loop.

 

[T111]

Thanks marlinatgls thought there might be an easer way but this works fine. I created a structure and the made an array of employee structures and then loop through the table and slotted in the corresponding values. Thanks again.

 

[marlinatgls]

Just for completeness (FYI):

The 'more elegant' approach would involve macro-substitutions, or indexed-variables. As I am not sure what language you are using I don't know if you have this option. For a one-time conversion, either way is about the same amount of work. However, if you have recurring conversions where the item positions may change, this may be the better approach.

In this approach you create a look-up table as follows:
newfld oldfld skiptonext
------ ------ ----------
name csvfld3 2
dob csvfld2 ....

create your new file structure as <newfile>
postion record pointer to first employee in <csvfile>
do while not eof(<csvfile>)
append blank to <newfile>
scan through <look-up>
mvarfldname = "newfile." + look-up.newfld
mvaroldcolm = "csvfile." + look-up.oldfld
replace &mvarfldname with &mvaroldcolm
skip look-up.skiptonext in <csvfile>
endscan
enddo

The & is the macro-substitution as in FoxPro or dBase.

 

[T111]

It was a once off conversion (in-house) so efficiency was of little importance. I just needed to make sure that each record was correctly exported. Maybe someone else with a different problem may need to use the macro-substitution..