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

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.

Answer 1

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...

Answer 2

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
       

Answer 3

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

Answer 4

>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.

Answer 5

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.

Answer 6

Thank you. Andrey. I will give a try later.

Answer 7

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.