Portal for State upload of CSV files Has Duplicate Column Headers

[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!

 

[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,