Answered by:
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.
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- Edited by Bill Mosca, MS MVP 2006-2016 Friday, September 14, 2018 8:45 PM
- Marked as answer by eg10013 Monday, September 17, 2018 2:37 PM
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- Edited by Bill Mosca, MS MVP 2006-2016 Friday, September 14, 2018 8:45 PM
- Marked as answer by eg10013 Monday, September 17, 2018 2:37 PM
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