Append Columns and Values to exisiting CSV

[holly]

Hi

I have the below code which I pass a RS to add new columns and values to
a CSV file but it appears to update the same value for all rows..

can anyone see what I'm doing wrong..I need to treat them individually and
add different values..

Thanks

Pls Help!!

'Append Column names to CSV file
Private Sub AddColumnCSV(rs As Recordset)
    
    Dim row As Long, col As Long
    Dim fd As ADODB.Field
    Dim i_file As Integer
    Dim strLine As String
    Dim Counter As Long
    Dim strSQL As String
    Dim strFilename As String
    Dim strIn, strOut As String
    'Dim db As Database
    
    strFilename = "N:\DailyFiles\QuoteAndBuy\Zurich_Retailer_Extract\dailyextractZurich_Survey.csv"
   
    Set rs = New Recordset
    strSQL = ""
    strSQL = " SELECT tab_BASS_BDA_MAIN_SQL.Name_char, tab_BASS_BDA_MAIN_SQL.Address_1_char, tab_BASS_BDA_MAIN_SQL.Address_2_char, tab_BASS_BDA_MAIN_SQL.County_char, tab_BASS_BDA_MAIN_SQL.Post_code_char, tab_BASS_BDA_MAIN_SQL.TEL_GENERAL_char, tabTempSurvey.Survey, First(tab_QMS_ContactList.Contact_char) AS FirstOfContact_char, tabTempSurvey.Ac"
    strSQL = strSQL & " FROM (tab_BASS_BDA_MAIN_SQL INNER JOIN tabTempSurvey ON tab_BASS_BDA_MAIN_SQL.A_C_NO = tabTempSurvey.Ac) INNER JOIN tab_QMS_ContactList ON tab_BASS_BDA_MAIN_SQL.A_C_NO = tab_QMS_ContactList.Ac_No"
    strSQL = strSQL & " GROUP BY tab_BASS_BDA_MAIN_SQL.Name_char, tab_BASS_BDA_MAIN_SQL.Address_1_char, tab_BASS_BDA_MAIN_SQL.Address_2_char, tab_BASS_BDA_MAIN_SQL.County_char, tab_BASS_BDA_MAIN_SQL.Post_code_char, tab_BASS_BDA_MAIN_SQL.TEL_GENERAL_char, tabTempSurvey.Survey, tabTempSurvey.Ac"
    strSQL = strSQL & " HAVING (((tabTempSurvey.Survey)= 'yes'))"
    rs.Open strSQL, gLocal, adOpenStatic, adLockOptimistic
    
    'Check if rs is not empty
    If rs.BOF Then Exit Sub
    rs.MoveFirst
    'open the file
    i_file = FreeFile()
    
    Open strFilename For Input As #i_file
    
    'Set db = CurrentDb
     Counter = 0

    Do While Not EOF(i_file)
        Line Input #i_file, strIn
        Counter = Counter + 1
       
           If Counter = 1 Then
               strOut = strOut & strIn & "," & Chr(34) & "Survey" & Chr(34) & "," & Chr(34) & "Broker Name" & Chr(34) & "," & Chr(34) & "Address_1" & Chr(34) & "," & Chr(34) & "Address_2" & Chr(34) & "," & Chr(34) & "County" & Chr(34) & "," & Chr(34) & "Post_Code" & Chr(34) & "," & Chr(34) & "Telephone_General" & Chr(34) & "," & Chr(34) & "Survey Contact" & Chr(34) & "," & Chr(34) & vbCrLf
           Else
               'Do similar for data
               strOut = strOut & strIn & "," & Chr(34) & rs.Fields("Survey").Value & Chr(34) & "," & Chr(34) & rs.Fields("name_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("Address_1_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("Address_2_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("County_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("Post_code_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("TEL_GENERAL_char").Value & Chr(34) & vbCrLf
           End If
       Loop

    Close #i_file
    CreateMSCSV strOut, "N:\DailyFiles\QuoteAndBuy\Zurich_Retailer_Extract\Zurich_Survey Report_RL01.csv"
    
End Sub

[si_the_geek]

You do not have a .MoveNext in there, so you are re-using the same fields from the recordset each time.

It is far from clear how it should be added, as presumably you want to somehow match the recordset data to the data that is already in the file.

[holly]

Hi Si..Thanks for replying

Yes via the policy number within the CSV with a tmp table previously created
within another routine!!

and where would I need place the .movenext?

[si_the_geek]

You wouldn't use a .MoveNext, because that will just go to the 'next' record - it will not find the record that you actually want.

What you should do instead is move the recordset opening etc to inside the loop, and use a Where clause to get the apt record.

[holly]

Hi

I've managed to get some of it sorted but now I'm encountering a problem with
reading the CSV file as a row can contain upto 250 columns and it appears
to be reading line by line...wherby I need to be able to read all columns at once!!

pls help!!

'Append Column names to CSV file
Private Sub AddColumnCSV(rs As Recordset)
    
    Dim row As Long, col As Long
    Dim fd As ADODB.Field
    Dim rs1 As ADODB.Recordset
    Dim i_file As Integer
    Dim strLine As String
    Dim Counter As Long
    Dim strSQL As String
    Dim strFilename As String
    Dim strIn, strOut As String
    Dim Count As Long
    
    strFilename = "N:\DailyFiles\QuoteAndBuy\Zurich_Retailer_Extract\dailyextractZurich_Survey.csv"
 
    Set rs = New Recordset
    strSQL = ""
    strSQL = " SELECT tab_BASS_BDA_MAIN_SQL.Name_char, tab_BASS_BDA_MAIN_SQL.Address_1_char, tab_BASS_BDA_MAIN_SQL.Address_2_char, tab_BASS_BDA_MAIN_SQL.County_char, tab_BASS_BDA_MAIN_SQL.Post_code_char, tab_BASS_BDA_MAIN_SQL.TEL_GENERAL_char, tabTempSurvey.Survey, First(tab_QMS_ContactList.Contact_char) AS FirstOfContact_char, tabTempSurvey.Ac, tabTempSurvey.PolID"
    strSQL = strSQL & " FROM (tab_BASS_BDA_MAIN_SQL INNER JOIN tabTempSurvey ON tab_BASS_BDA_MAIN_SQL.A_C_NO = tabTempSurvey.Ac) INNER JOIN tab_QMS_ContactList ON tab_BASS_BDA_MAIN_SQL.A_C_NO = tab_QMS_ContactList.Ac_No"
    strSQL = strSQL & " GROUP BY tab_BASS_BDA_MAIN_SQL.Name_char, tab_BASS_BDA_MAIN_SQL.Address_1_char, tab_BASS_BDA_MAIN_SQL.Address_2_char, tab_BASS_BDA_MAIN_SQL.County_char, tab_BASS_BDA_MAIN_SQL.Post_code_char, tab_BASS_BDA_MAIN_SQL.TEL_GENERAL_char, tabTempSurvey.Survey, tabTempSurvey.Ac, tabTempSurvey.PolID"
    strSQL = strSQL & " HAVING (((tabTempSurvey.Survey)='yes'));"

    rs.Open strSQL, gLocal, adOpenStatic, adLockOptimistic
    
    'Check if rs is not empty
    If rs.BOF Then Exit Sub
    rs.MoveFirst
    'open the file
    i_file = FreeFile()
    
    Open strFilename For Input As i_file

    'Set db = CurrentDb
     Counter = 0

        Do While Not EOF(i_file)
            Line Input #i_file, strIn
            Counter = Counter + 1
               If Counter = 1 Then
                   strOut = strOut & strIn & "," & Chr(34) & "Survey" & Chr(34) & "," & Chr(34) & "Broker Name" & Chr(34) & "," & Chr(34) & "Address_1" & Chr(34) & "," & Chr(34) & "Address_2" & Chr(34) & "," & Chr(34) & "County" & Chr(34) & "," & Chr(34) & "Post_Code" & Chr(34) & "," & Chr(34) & "Telephone_General" & Chr(34) & "," & Chr(34) & "Survey Contact" & Chr(34) & "," & Chr(34) & vbCrLf
               ElseIf (strIn <> "") Then
                    Dim strSplit() As String
                   'Do similar for data
                    strSplit = Split(strIn, ",")
                    
                    Set rs1 = New ADODB.Recordset
    
                    strSQL = " select * from tabTempSurvey where tabTempSurvey.PolID = " & strSplit(5)
                    rs1.Open strSQL, gLocal
                    
                    If rs1!polid = rs!polid Then
                        strOut = strOut & strIn & "," & Chr(34) & rs.Fields("Survey").Value & Chr(34) & "," & Chr(34) & rs.Fields("name_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("Address_1_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("Address_2_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("County_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("Post_code_char").Value & Chr(34) & "," & Chr(34) & rs.Fields("TEL_GENERAL_char").Value & Chr(34) & vbCrLf
                    End If
                    rs1.Close
               End If
            Loop

    Close #i_file
    CreateMSCSV strOut, "N:\DailyFiles\QuoteAndBuy\Zurich_Retailer_Extract\Zurich_Survey Report_RL01.csv"
    
End Sub

[si_the_geek]

I'm afraid I have no idea what you mean, can you give a clearer explanation?

[holly]

Ok, Here goes

I have a CSV file that contains approx 200 - 250 columns for a record, I need
to append the extra columns to the end of each and then do a select query
on the 5th column(policy number) but, because i'm reading it line by line it is
not getting to the end of the record and the 5th column is not always the
policy numbr !!

Hope this helps

Thanks

[si_the_geek]

but, because i'm reading it line by line it is
not getting to the end of the record

I'm not sure what you mean by that, because the code you have shown reads all of the data from the file.

and the 5th column is not always the policy numbr !!

That is rather awkward, and as you need to read that field, you need to find a way to work out where it is.

Pretending for a moment that you aren't writing a program... how would you work it out manually?