locked
Use Access 2010 VBA to modify "fields" in a CSV file. RRS feed

  • Question

  • I used a SQL statement to create a export file. Now, it is required to replaces some fields with abbreviation such replace “Universal Access” to “UA”, “Non-Universal Access” to “NUA”… What I did is keep the export file, then write a VBA procedure to replace the fields with the abbreviation. I have the following code to open the existing CSV file:

    '///////////////////////////////////////////////////////////////////////////////

    'MODIFY THE CSV FILE

    '///////////////////////////////////////////////////////////////////////////////

    Dim fs As Object

    Dim tsIn As Object

    Dim sFileIn As String

    Dim n As Integer

    sFileIn = filepath + file1                                                                     ‘this is the file path from the before procedure

    Set fs = CreateObject("Scripting.FileSystemObject")

    Open sFileIn For Input As #1

    n = 0

    Do Until EOF(1)

          n = n + 1

          MsgBox (n)

    Loop

    Close #1

    I only have 27 lines in the file, but the n become un-limited. I have no idea why. This is the first time I am working on VBA for a CSV file. I need to go through each row the change some “fields” value in fields “Program_Name”, “DHCI_Date” and “Posture_Case”. Can someone please provides some sample code on how to accomplish that. Many thanks.

    Thursday, September 13, 2018 6:18 PM

Answers

  • SELECT IIf([Program_Name]="Universal Access", "UA", IIf([Program_Name]="N0n-Universal Access","NUA",Program_Name))
    The IIf block uses 3 parts: The expression, the response if true, and the response if false. So by nesting one IIf inside another you can get what you want.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Friday, September 14, 2018 8:43 PM

All replies

  • I used a SQL statement to create a export file.

    Hi,

    Are all that code really necessary? If you used a SQL statement to create the CSV file, then why not just assign an "alias" to each column in the SQL statement?

    Just curious...

    Thursday, September 13, 2018 6:23 PM
  • I do not know how to do that. Can you show me how? Here is my original code. All these fields, "Program_Name" and "Proceeding_Type" all turn to empty. I thing this have to do with row modification. SQL selection is a batch process?

            'query Brooklyn
            testSQL = "SELECT 'CAMBA' + RegexReplaceAll([Main Table].[CLS File Number],'[^0-9]+','') AS id, "
            'program name
            Dim PN
            PN = DLookup("[Program_Name]", "[Main Table]")
            'PN = [Main Table].[Program_Name]
            Select Case PN
                Case "Universal Access"
                    testSQL = testSQL & "'UA' AS Program_Name, "
                Case "Non-Universal Access"
                    testSQL = testSQL & "'NON-UA' AS Program_Name, "
                Case "Anti-harassment Tenant Protection"
                    testSQL = testSQL & "'AHTP' AS Program_Name, "
                Case Else
                    testSQL = testSQL & "'' AS Program_Name, "
            End Select
            'testSQL = testSQL & "[Main Table].[Program_Name] AS Program_Name, "  'NEW COLUMN
            testSQL = testSQL & "[Main Table].[First Name] AS firstname, "
            testSQL = testSQL & "[Main Table].[Last Name] AS lastname , "
            testSQL = testSQL & "Format([Main Table].[Social Security], '000-00-0000') AS SSN, "
            testSQL = testSQL & "[Main Table].[TANF/SNA Case Number] AS HRAcase_num , "
            testSQL = testSQL & "[Main Table].[Date of Birth] AS DOB, "
            testSQL = testSQL & "[Main Table].Age1823 + [Main Table].Age2444 + [Main Table].Age4555 + [Main Table].Age56 AS num_adults, "
            testSQL = testSQL & "[Main Table].Age05 + [Main Table].Age611 + [Main Table].Age1217 AS num_children, "
            testSQL = testSQL & "Left([Main Table].[Street Address], InStr([Main Table].[Street Address], ' ') - 1) AS street_number, "
            testSQL = testSQL & "Right([Main Table].[Street Address], Len([Main Table].[Street Address]) - Len(street_number) - 1) AS street, "
            testSQL = testSQL & "[Main Table].Apartment AS unit, "
            testSQL = testSQL & "City.City AS city, "
            testSQL = testSQL & "[Main Table].Zip AS zip, "
            testSQL = testSQL & "[Main Table].RentAmt AS rent, "
           
            'get Proceeding type
            Dim LT
            LT = DLookup("[L/T]", "[Main Table]")
            Select Case LT                                             'NEW SECTION
                Case 1
                    testSQL = testSQL & "'NP' AS Proceeding_Type, "
                Case 2
                    testSQL = testSQL & "'HO' AS Proceeding_Type, "
                Case 3
                    testSQL = testSQL & "'HP' AS Proceeding_Type, "
                Case 7
                    testSQL = testSQL & "'IL' AS Proceeding_Type, "
                Case 4
                    testSQL = testSQL & "'TT' AS Proceeding_Type, "
                Case 23
                    testSQL = testSQL & "'EA' AS Proceeding_Type, "
                Case 6
                    testSQL = testSQL & "'EJ' AS Proceeding_Type, "
                Case 12
                    testSQL = testSQL & "'DA' AS Proceeding_Type, "
                Case 5
                    testSQL = testSQL & "'7A' AS Proceeding_Type, "
                Case 18
                    testSQL = testSQL & "'78' AS Proceeding_Type, "
                Case 21
                    testSQL = testSQL & "'S8' AS Proceeding_Type, "
                Case 20
                    testSQL = testSQL & "'FC' AS Proceeding_Type, "
                Case 24
                    testSQL = testSQL & "'OA' AS Proceeding_Type, "
                Case 25
                    testSQL = testSQL & "'OS' AS Proceeding_Type, "
                Case 22
                    testSQL = testSQL & "'OO' AS Proceeding_Type, "
                Case Else
                    testSQL = testSQL & "'' AS Proceeding_Type, "
            End Select
           

    Thursday, September 13, 2018 8:53 PM
  • SELECT IIf([Program_Name]="Universal Access", "UA", IIf([Program_Name]="N0n-Universal Access","NUA",Program_Name))
    The IIf block uses 3 parts: The expression, the response if true, and the response if false. So by nesting one IIf inside another you can get what you want.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Friday, September 14, 2018 8:43 PM
  • >I only have 27 lines in the file, but the n become un-limited. 

    In your code you just Open a file and check EOF. You didn't read anything from the file - EOF remain false.

    >This is the first time I am working on VBA for a CSV file. 

    There suppose to be a way to use CSV much easer.

    I'm not very familiar with VBA, but following code can be easy converted to VBA to deal with CSV

    Set cn = CreateObject("ADODB.Connection")
    strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
    cn.Open strcon
    strSQL = "SELECT * FROM SAMPLE.csv;"
    
    Dim rs As Recordset
    
    Set rs = cn.Execute(strSQL)

    As result you will have RS as two-dimensional array

    > Can someone please provides some sample code on how to accomplish that. 

    Hmmm... Sorry, need to read documentation to do this...


    Sincerely, Experienced coding mankey.

    Friday, September 14, 2018 9:09 PM
  • I already tried the way use second script to modified the csv files. But the IIF with selection statement should work for me (it should be simpler). Thanks you.
    Monday, September 17, 2018 2:40 PM
  • Thank you. Andrey. I will give a try later.
    Monday, September 17, 2018 2:42 PM
  • I already tried the way use second script to modified the csv files. But the IIF with selection statement should work for me (it should be simpler).

    Hi eg10013,

    With a small definition table in which you store the real fieldname and the alias you want to use, it is very simple to make a VBA-routine for a versatile way to make almost any csv-output.

    In the VBA-routine:

    - open a file for output
    - loop through the table to construct a csv-string with the aliasses as header line. You even can make this line optional.
    - loop through the recordset to construct for each record a csv-string with the field values
    - close the file.

    You can extend the definition table with a selection field to select only a number of fields, etc.

    Imb.

    Monday, September 17, 2018 9:06 PM