Portal for State upload of CSV files Has Duplicate Column Headers (1 Viewer)

[AngelSpeaks]

Our state has a portal to upload csv files, one for each job. We average 25-50 jobs a week. My Access database splits my table into separate csv files. In their infinite wisdom, 20 of the almost 100 columns in the template have duplicate column names, ie Address, City, etc.

1) Can I create the csv files with duplicate column names or
2) Any ideas on using VBA to read all csv files in the folder and rename the columns?

Thanks!

 

[Pat Hartman]

In their infinite wisdom, 20 of the almost 100 columns in the template have duplicate column names, ie Address, City, etc.

Don't you want to just shoot these bozos? I had similar problems with the people running the state applications I had to interface with in Connecticut.

I ended up having to create the .csv files via code. I couldn't use any of the Access tools. In my case, the problem was that they decided that their "standard" csv file would have quotes around ALL columns, not just the text ones and that isn't the standard Access follows. They also sent me .csv files with column names that started with a space. Do you have any idea what this does to Access?? They weren't interested in fixing it so I had to write code to open the file using Excel and fix the offending column header before I could import it. I could go on. to the three million dollar fiasco of a web app that could have been done with Access for < $100,000 AND which would not have required my agency and 4 others to purchase iPads and hotspots for their phones for hundreds of thousands of dollars of one time expense plus 10's of thousands of dollars for the annual hotspot fees. All because they were obsessed with bright, shiny things rather than making use of what they already had.............

Anyway - you should be able to modify this code to do what you want. I had to export a bunch of different files so I had code that opened the recordset for each of them and then called this export code passing in the recordset and the name of the export file.

You will need to modify the code that creates the column headers. A simple way might be to just make a table so you can use the unique column name to look up the common name and substitute that.

Just ignore the other stuff specific to my exports.

Call ExportToCSV(rs, sFileName, False)

Public Function ExportToCSV(rs As DAO.Recordset, strFile As String, Headers As Boolean)

    Dim fldLoop As DAO.Fields
    Dim fld As DAO.Field
    Dim strRec As String
    Dim FSO As FileSystemObject
    Dim fsoFile As TextStream
    Dim ExportCount As Long
    Dim RecLen As Integer
    Dim HoldEMS As String
    Dim holdAssessDate As String   
   
   On Error GoTo Err_Proc
   
'''fso stuff
    Set FSO = New FileSystemObject
    Set fsoFile = FSO.CreateTextFile(strFile, True)
    strRec = ""
   
'''initialize fields collection
    Set fldLoop = rs.Fields
   
    'write headers if headers flag = true
    If Headers = True Then
        For Each fld In fldLoop
            strRec = strRec & """" & fld.name & ""","
        Next fld
        RecLen = Len(strRec)
        strRec = Left(strRec, RecLen - 1)       'chop off last comma
        fsoFile.WriteLine strRec
    End If
   
    'write data records
    Do While Not rs.EOF
        ExportCount = ExportCount + 1
        strRec = ""
        For Each fld In fldLoop
            strRec = strRec & """" & fld.Value & ""","
            If fld.name = "ems" Then
                HoldEMS = fld.Value
            End If
            If fld.name = "AssessDate" Then
                holdAssessDate = fld.Value
            End If
        Next fld
        RecLen = Len(strRec)
        strRec = Left(strRec, RecLen - 1)       'chop off last comma
        fsoFile.WriteLine strRec
        rs.MoveNext
    Loop
'''fso stuff
    fsoFile.Close
    Debug.Print ExportCount
Exit_Proc:
   On Error GoTo 0
   Exit Function

Err_Proc:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportToCSV of Module mExportToText"
            MsgBox "EMS = " & HoldEMS & " AND AssessDate = " & holdAssessDate, vbOKOnly
    End Select
    Resume Exit_Proc
    Resume
End Function

 

[bastanu]

In addition to Pat's code you might find this link useful: https://www.thespreadsheetguru.com/blog/vba-guide-text-files

You could export the csv's based on the non-duplicated fields from your table then use the code in the link to replace the entire first line containing the column names with the desired target one (both of which you could store in memo fields in a table).

sOriginal="ID","Street","City","Street1","City1"
sFinal="ID","Street","City","Street","City"
Replace(sYourCSVFile,sOriginal,sFinal)

Cheerts,

 

[AngelSpeaks]

You guys are the best. I will try them out. Thanks

 

[AngelSpeaks]

In addition to Pat's code you might find this link useful: https://www.thespreadsheetguru.com/blog/vba-guide-text-files

You could export the csv's based on the non-duplicated fields from your table then use the code in the link to replace the entire first line containing the column names with the desired target one (both of which you could store in memo fields in a table).

sOriginal="ID","Street","City","Street1","City1"
sFinal="ID","Street","City","Street","City"
Replace(sYourCSVFile,sOriginal,sFinal)

Cheerts,

This is the code I use to extract the data to csv files.

 Do While Not rsJobs.EOF
        Job = rsJobs!Job
        Dim rsExportSQL As String

        sDate = Format(dteStart, "mmddyyyy")
        
        rsExportSQL = "SELECT * FROM tblCPUpload " _
         & "WHERE (ProjectNumber= " & "'" & Job & "'  AND ProjectState = " & "'" & gstrState & "')"
        Dim rsExport As DAO.QueryDef

        Set rsExport = CurrentDb.CreateQueryDef("myExportQueryDef", rsExportSQL)

        DoCmd.TransferText acExportDelim, , "myExportQueryDef", gExportPath & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.csv", True   ' true means to use column names as first row

       ' DoCmd.TransferText acExportDelim, , "myExportQueryDef", gExportPath & "\State " & gstrState & " Job " & Job & " Start Date " & sDate & " - CP Upload.csv", False
        CurrentDb.QueryDefs.Delete rsExport.Name
        rsJobs.MoveNext
    Loop

Would I insert your code after the DoCmd.TransferText?

Thanks

 

[arnelgp]

you can Rename your column in the SQL, example:

rsExportSQL = "select ID As EmpID, anotherField As Field2, ... From tblCPUpload Where..."

 

[AngelSpeaks]

you can Rename your column in the SQL, example:

rsExportSQL = "select ID As EmpID, anotherField As Field2, ... From tblCPUpload Where..."

Unfortunately, there are 20 columns with duplicate column names. I receive a duplicate error when I tried that. Thanks anyway.

 

[bastanu]

Would I insert your code after the DoCmd.TransferText?

Not that easy I'm afraid but please have a look at the attached sample, you should be able to implement this approach into your existing db.

Cheers,
Vlad

 

[AngelSpeaks]

Thanks Vlad.

 

[bastanu]

You're very welcome, please let me know if you run into any issues implementing it!
Cheers,

 

[AngelSpeaks]

Vlad, this is GREAT! Thanks so much!

 

[bastanu]

You're very welcome, good luck with your project!

Cheers,

 

[arnelgp]

you can also create a Mapping table and rename the column (Output Name)
in the datasheet.

 

[bastanu]

Not sure what you're saying Arnel as it seems you do not address the original issue, there need to be duplicate column names in the exported file, not just "renamed" but still unique. A querydef's SQL statement will not allow you to have two (or more) "Phone" or "City" fields, so you need to that in the resultant csv file after it is created.
Cheers,

 

[arnelgp]

need to that in the resultant csv file after it is created.

so the OP need duplicate column name?
but what you gave will Replace all occurrence, not just the column header.
here is a modified, will only replace the Column Header in csv (and not in any place).

 

[AngelSpeaks]

Yes Arnel, the stupid state of Illinois requires that. I have contact first, middle, and last name, postal address, city, state, zip5, zip4, county, county fips, primary and secondary phone for the project, contractor, public body, and employee all the same!

Thanks for your suggestions.

 

[bastanu]

The solution I gave would replace a string made up of concatenated field names wrapped in double-quotes with another using alternative field names wrapped in double-quotes:


sOriginal="ID","Street","City","Street1","City1"
sFinal="ID","Street","City","Street","City"


That will only be encountered on the header line of the csv file, because after that you have the actual data. Chances that you will have an ID with the value=ID and a Street named Street are pretty small in my opinion.

Cheers,