Mapping a CSV file fields

Question

Hi everyone,

               I have been tasked with creating a database to store employee time information. I’m using Access 2010 on a W7 PC. Twice daily I receive a CSV file from our time and attendance system that contains employee time records. The file contains the field header names on the second line instead of the first. The first line contains the name of the file. I have asked and been informed the name of the file on the first line cannot be removed. :(

                From left-to-right the first few fields on the CSV file remain static (Dept #, Employee ID, Employee Name, etc). But the hour types are dynamic. For example, one day a file may contain vacation hours used. The next day vacation hours field is not present. That is because no one used any vacation time when this file was created.

                I need to a process to import these files that ignores the file name on the first line of the CSV files and maps the correct fields in the file to the correct fields in the Access table.

I hope this make sense.

Thank you,

Kevin

Answer 1

Hi Kevin,

Are you free to modify the CSV file yourself? For example, maybe you can make a copy of the file and then delete the first line before you import it into Access?

As for the missing fields, you can import the data into a temporary table and then loop through the fields collection as you create an APPEND SQL to import the data into the permanent table in their appropriate fields.

Just my 2 cents...

Answer 2

               I have been tasked with creating a database to store employee time information. I’m using Access 2010 on a W7 PC. Twice daily I receive a CSV file from our time and attendance system that contains employee time records. The file contains the field header names on the second line instead of the first. The first line contains the name of the file. I have asked and been informed the name of the file on the first line cannot be removed. :(

Hi Kevin,

The standard import routines work in a "standard" way, but cannot handle many exceptions. That is why I never use them, but use my own import functions.

The way of working is really simple. Open the csv-file for read, and read the contents of the file line by line.

In your case. skip the first line.

You can make an array of the fieldnames by using the Split function on line 2.

Then process all next lines by making an values array by using the Split function. Loop through the fieldnames array and the values array to fill the values:

      recordset(fieldnames(x)) = values(x).

Depending on the exceptions you can use your special treatment for any line.

Imb.

Answer 3

It's pretty easy to remove the first line while processing as .theDBguy mentioned, but my question would be, does each line have the same number of column delimiters (assuming commas)? If yes, then this should be easy. If no, then it's going to get a lot more complicated when determining whether certain data is present or not. It would probably help to see a few sample lines from the file, including those with missing data.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 4

Hi KevinATF,

I try to create a table like yours and try to made a test with 2 csv files.

One contain the vacation hours and other doesn't contain that field.

File1.csv

File2.csv

Code:

Option Compare Database

Sub demo()
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim rs As ADODB.Recordset
Dim i, size As Integer
i = 0
 
sSQL = "SELECT * FROM emp_data"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
 
 
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\Users\panchals\Desktop\csv data\File2.csv") Then
    Set objStream = fso.OpenTextFile("C:\Users\panchals\Desktop\csv data\File2.csv", 1, False, 0)
End If
Do While Not objStream.AtEndOfStream
    strLine = objStream.ReadLine
       ReDim MyArray(0)
    MyArray = Split(strLine, ",")
    i = i + 1
    
       size = UBound(MyArray) - LBound(MyArray) + 1
       If i >= 2 Then
            If size = 4 Then
                If i > 2 Then
                    rs.AddNew
                    rs("Dept") = MyArray(0)
                    rs("Employee_ID") = MyArray(1)
                    rs("Employee_Name") = MyArray(2)
                    rs("Vacation_Hrs") = MyArray(3)
                    rs.Update
               End If
            Else
                 If i > 2 Then
                    rs.AddNew
                    rs("Dept") = MyArray(0)
                    rs("Employee_ID") = MyArray(1)
                    rs("Employee_Name") = MyArray(2)
                    rs("Vacation_Hrs") = ""
                    rs.Update
                 End If
            End If
       End If
    
Loop
 
MsgBox "Done"
End Sub

Output:

Further, You can try to understand the code and try to modify it as per your requirement.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 5

Hi everyone. Thank you all for your suggestions. However, understanding the the number of fields can be dynamic, I think it is best if I manually import each file manually adjusting for missing fields.

Thanks again for your help!

Cheers, Kevin

Answer 6

Hi Kevin,

Sounds good enough for me. Good luck!