Importing certain info from .csv file and appending it an existing Tbl 1

[Khawer]

Hi all,

I have a *.csv file that is exported daily. This .csv is not in any particular order. The date is in the first row (B2, i know its comma delimited just giving an idea), the data is running across and sometimes running downwards, meaning it isn't in a purely table format. After opening the .csv from a specified path, what I want to do is grab certain information from the csv file according to the location of the field, lets take the date field (date,04/23/06,) how can I in vba specify look for a field name "date" once it locates it look at the value which follows it meaning "04/23/06" then it will take this value and place it in Table1->Date field. Then it looks at another field till all of the field values in Table1 have been populated. I have searched through this forum and tried numerous things but they don't work according to what I require. If someone can assist in showing me the right direction that would be great.

 

[Remou]

Can you post a small sample of the data? At first glance it seems that you need to either use the Open statement or the FileSystemObject to open the file and loop through the data.

 

[Khawer]

Date:,4/20/2006,Service Intervals Changed:,n,,,,,,,,,,,,,,,,,,
Split/Skill:,Sales_Eng,Acceptable Service Changed:,n,,,,,,,,,,,,,,,,,,
% Within Service Level:,94.44,,,,,,,,,,,,,,,,,,,,
Seconds,0,-,5,-,10,-,15,-,20,-,25,-,30,-,35,-,40,-,45,-,>
ACD Calls:,13,15,3,0,0,0,0,0,0,2,,,,,,,,,,,
Aban Calls:,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,,
ACD Calls:,36,Aban Calls:,0,,,,,,,,,,,,,,,,,,
Avg Speed Ans:,12.47222233,Avg Aban Time:,0,,,,,,,,,,,,,,,,,,
% Ans Calls:,100,% Aban Calls:,0,,,,,,,,,,,,,,,,,,

 

[Khawer]

Hi Remu,

I have posted the sample csv file. Thanks

 

[Remou]

I cannot see how to process the file without more information, because I notice that two fields have the same name; both ACD Calls and Aban Calls occur twice in two very different formats. This problem can be got around if the data is always exactly as shown in the sample, that is, several lines of data that always begin with the same field names. If fields can be in different positions, checking to find out which of the two similarly named fields is to be processed could get complicated.
Furthermore, the size of the file to be processed could be important, with a small field it may be ok to use an approach that would take too long for a large file.
I notice also that you seem to have tables within a table in that these lines:

[tt]Seconds,0,-,5,-,10,-,15,-,20,-,25,-,30,-,35,-,40,-,45,-,>
ACD Calls:,13,15,3,0,0,0,0,0,0,2,,,,,,,,,,,
Aban Calls:,0,0,0,0,0,0,0,0,0,0,,,,,,,,,,,[/tt]

Seem to belong in a second, and even third table, unless you intend to store a string of values in a field. I notice that "Seconds" is not followed by a colon, is this the case or is it a typo when setting up a sample?

I had been thinking:

Open "C:\Output.csv" For Input As #1
    Line Input #1, strLine
    
    astrLine = Split(strLine, ",")
    For i = 0 To UBound(astrLine)
        If astrLine(i) = "Date:" Then
            rs1.AddNew
            rs1!DateX = astrLine(i + 1)
        Else
            If InStr(astrLine(i), ":") > 0 Then
<...>

But you can see immediately that this will not work where fields have the same name.

 

[Khawer]

Hi Remou,

Thanks for the quick reply, Yes the file will be in the same format all the time. The "-" are not typo but automatically generated. We can use line number as a reference for both ACD and Aban calls. The thing is I want this info to be added to the SS2test table which consists of these fields:

ID
Date
% Within Service Level
0 Seconds
5 Seconds
10 Seconds
15 Seconds
20 Seconds
25 Seconds
30 Seconds
35 Seonds
40 Seconds
45 Seconds
ACD Calls
Aban Calls
Tot ACD Calls
Tot Aban Calls
Avg Speed Ans
Avg Aban Time
% Ans Calls
% Aban Calls

Lets say once it locates the date it will open SSTest table and plug in the date in the date field and work its way through and so on. Thanks again.

 

[Remou]

This may help:

Dim strLineIn, astrLine, j
Dim rs1 As DAO.Recordset

Set rs1 = CurrentDb.OpenRecordset("Select * from NewTab Where 1=2")
Open "C:\Output.csv" For Input As #1
Do While Not EOF(1)
    rs1.AddNew
    For j = 1 To 9
        Line Input #1, strLineIn
        astrLine = Split(strLineIn, ",")
        Select Case j
            Case 1
                rs1![Date] = astrLine(1)
                'rs1![Service Intervals Changed] = astrLine(3)
            Case 2
                'rs1![Split/Skill] = astrLine(1)
                'rs1![Acceptable Service Changed] = astrLine(3)
            Case 3
                rs1![% Within Service Level] = astrLine(1)
            Case 4
                'All assumed to be text fields
                rs1![0 Seconds] = astrLine(2)
                rs1![5 Seconds] = astrLine(4)
                rs1![10 Seconds] = astrLine(6)
                rs1![15 Seconds] = astrLine(8)
                rs1![20 Seconds] = astrLine(10)
                rs1![25 Seconds] = astrLine(12)
                rs1![30 Seconds] = astrLine(14)
                rs1![35 Seonds] = astrLine(16)
                rs1![40 Seconds] = astrLine(18)
                rs1![45 Seconds] = astrLine(20)
            Case 5
                'Assumed to be text fields
                rs1![ACD Calls] = Mid(strLineIn, 11)
            Case 6
                'Assumed to be text fields
                rs1![Aban Calls] = Mid(strLineIn, 12)
            Case 7
                rs1![Tot ACD Calls] = astrLine(1)
                rs1![Tot Aban Calls] = astrLine(3)
            Case 8
                rs1![Avg Speed Ans] = astrLine(1)
                rs1![Avg Aban Time] = astrLine(3)
            Case 9
                rs1![% Ans Calls] = astrLine(1)
                rs1![% Aban Calls] = astrLine(3)
        End Select
    Next
    rs1.Update
Loop
Close #1

 

[Khawer]

Renou,

Thanks that worked.

 

[Khawer]

Renou,

One more question. Im using the same code as mentioned above for this other excel sheet. The rows in this excel sheet are inconsistent. There could be 2 or greater (max of 9). I was searching but couldn't find anything. I know that we will have to change this line:

For j = 1 To 9 <-- (For j = 1 To i) "i" will be an integer that checks the number of populated rows. I was able to get columns but rows doesn't seem to work. It will be much appreciated if you can assist me with this. Thanks