Export to csv but change header column names (1 Viewer)

Adyk

New member
Local time
Today, 02:03
Joined
Feb 12, 2018
Messages
1
Hopefully someone can point me in the right direction

I am very much a beginner when it comes to vba ..... I need to export from a table or query to a cvs file.

It gets a little more tricky as I need to change the column names in cvs file

What I have is a structure like this

Mandate First name Last name
————————————————————-
Miu345 John Doe
X346a Jane Doe

I need to alter it to

Mandate.id Main.name Family.name
——————————————————————
Miu345 John Doe
X346a Jane Doe

The reason is the software that I need to import the generated csv file must have the header line in this format. Access will not allow me to use the id or period In the field name and throws an error if trying to use this method.

Appreciate any help or if someone can point me where to look

Regards

Adyk
 

isladogs

MVP / VIP
Local time
Today, 09:03
Joined
Jan 14, 2017
Messages
18,186
Welcome to AWF
Several possible solutions

1. Open the table in design view & rename the 3 fields. Then export....
BUT this change will apply throughout your db & may stop queries, forms, reports etc working until also modified. If you have name autocorrect turned on, these should change automatically BUT any VBA code will need manual editing

2. Create a query based on this table and use aliases for each of the 3 fields
i.e. MandateID: Mandate ; [Main.name]: First name; [Family.name]: Last name
The use of full stops means you need to enclose in square brackets but that may also cause issues
HOPEFULLY you can then export from that (those I've not tested it)

3. Similar to 2 but try to do the renaming when stepping through the Export wizard.

You may need to test a few times to get it right. When you have done so, save your export specification
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:03
Joined
Feb 19, 2002
Messages
42,970
Access does not allow periods in object names. You can do this by creating a union query. Create a table with a single row. In the row, you would put the actual column names you want.

Then create a union query that selects the one row from the header table and union it with a select of the data rows.

When you export to Excel, specify NO HEADERS. Your header row will serve as the column headers.

The drawback to this is that you will probably also have to format any numeric or date data in your data fields to be strings because unions expect all columns to be consistant in that each row has to have the same data type as the data in the other rows. So if row 1 (the headers row) has only text values then all subsequent rows must also be all text.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:03
Joined
Jan 20, 2009
Messages
12,849
Access does not allow periods in object names.

Access won't let you do it but will do it by itself under some circumstances. (Do as I say not as I do.;))

It happens where two tables are in a query and both have the same field names. If you don't alias them, Access will prepend the tablename and a dot before the field name to disambiguate.

The resulting fieldnames are displayed as tablename.fieldname but are actually [tablename.fieldname] . It can be quite confusing because the first time it is encountered, most interpret it as [tablename].[fieldname]

Many never get to the bottom of what is going on and simply alias them to avoid the issue.

How one would go about using this trick to get the OP desired results I don't know.
 

sxschech

Registered User.
Local time
Today, 02:03
Joined
Mar 2, 2010
Messages
791
If the field list is fixed-same fields are exported each time and you don't mind having data first saved to excel and then save to csv, you can use vba to edit the cells. If field names and number of fields are different each time, it could be done, but would require more code. Here is some code I use, it does a few different things so would need to be modified for your needs, but leaving the other stuff in there in case is useful down the road. Even though it is called ExcelToText, it currently is not creating a text file, originally it was set up to convert excel columns to text format to help import excel data without access making assumptions about whether a field is numeric, date, etc. It may be possible to do directly with csv using this code, as there is some code for handling csv files that is commented out, but as haven't used that option in years, not sure if it still works.

As an example: for your name stuff in the "cells" section change to your header values.
Change:
.Cells(1, 2) = "Acronym"
To
.Cells(1, 2) = "Family.Name"

Code:
Public Sub ExcelToText(ByVal stfilepath As String)
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28277710.html
'http://www.developark.com/1529_17747527/
'http://www.mrexcel.com/forum/excel-questions/235867-using-column-numbers-define-range-visual-basic-applications.html
'Change the formatting of an excel file to text for all used cols (last col)
'Save as to another name (filename + txt.xlsx)
'Need to set a reference to Excel (tools references)
'20150228 ss
'Incorporated Version Check so file does not need to be reopened before import
'20150521
'Changed so no longer needs to set reference to excel
'Found that last col wasn't getting all data, switched to .cells without
'range in order to capture entire sheet.
'Changed to convert all reformatted files into Excel 2007-2013 format xlsx.
'-previously kept reformat in original version, but was causing issues down
'the road.  Simplifies import process as all imported files are now the
'same format.
'201506??
'Added option to select a specific tab when doing a direct import
'need to reset sheet number to zero in the calling function when
'complete so that it won't use the wrong sheet number for normal
'operations
'http://stackoverflow.com/questions/15072098/saving-individual-sheet-in-excel-vba
'http://stackoverflow.com/questions/20632514/issue-on-saving-active-sheet-as-new-worksheet
'20160226
'Added code to trim the data to be imported
'20160227
    Dim objapp As Object
    Dim wb As Object
    Dim lastCol As Long
    Dim ExcelVersion As Long
    Dim sheetIndex As Integer
    Dim stReplace As String
    Dim stReplacement As String
    Dim stsql As String
    Dim stSheetName As String
    Dim stFileName As String
    Dim stTableName As String
    
    If globalintSheetIndex <> 0 Then
        sheetIndex = globalintSheetIndex
    Else
        sheetIndex = 1
    End If
    
    On Error Resume Next
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    
    If Dir(stfilepath) <> "" Then
        Set wb = objapp.Workbooks.Open(stfilepath, True, False)
'    ElseIf Right(Dir(stfilepath), 3) = "csv" Then
'        Set wb = objApp.Workbooks.Open(FilePath(stfilepath) & FileNameNoExt(stfilepath) & ".csv", True, False)
'    ElseIf Right(Dir(stfilepath), 3) = "txt" Then
'        Set wb = objApp.Workbooks.Open(FilePath(stfilepath) & FileNameNoExt(stfilepath) & ".txt", True, False)
    End If
    With wb.Sheets(sheetIndex)
        .Activate
        'lastcol = .cells(1, .Columns.Count).End(xlToLeft).Column
        '.Range(.Columns(1), .Columns(lastcol)).NumberFormat = "@"
        .Cells.NumberFormat = "@"
        'wb.Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        'wb.Cells.Replace What:=Chr(34), Replacement:="", LookAt:=xlPart, MatchCase:=False
        
        'Sub RemoveCarriageReturns()
    
        lastrow = .Range("A1").CurrentRegion.Rows.Count
        lastCol = .Range("A1").CurrentRegion.Columns.Count
        
   
   '--Can Delete this section 
        '******************************************************
        'This stopped working on 14-AUG so commented it out and
        'hope it doesn't cause other problems
        '.Name.Delete    'Delete Named range
        '******************************************************
        
        
        'SurveyMonkey macro code from Excel moved here to automate
        'import of data from JSONtoCSV desktop conversion
        '20170407
        If (InStr(stfilepath, "DetailsMatrix") Or InStr(stfilepath, "RespondentData")) > 0 Then
            objapp.DisplayAlerts = False
            'If this is DetailsMatrix or RespondentData has no comments (col M), append as query
            'to avoid col positional errors otherwise add Long Text to avoid truncation on import
             If .Range("M1").value <> "data__pages__questions__answers__text" Then
                If InStr(stfilepath, "DetailsMatrix") > 0 Then
                    stTableName = "SMDetailsMatrix"
                Else
                    stTableName = "SMRespondentData"
                End If
                stSheetName = FileNameNoExt(stfilepath)
                stFileName = FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx"
                'Query built here, but is run in frmSurveys
                stsql = "INSERT INTO " & stTableName & " " & _
                        "SELECT T1.* " & _
                        "FROM [Excel 12.0;HDR=YES;IMEX=1;Database=" & stFileName & "].[" & stSheetName & "$A1:BB10000] AS T1;"
                Forms!frmsurveys.txtSQL = stsql
            Else
                .Range("M2").value = "THIS IS LONG: Copy this into Cell M2 header should be data__pages__questions__answers__text   !!!Do Not Delete!!! This needs to be done because otherwise if there are comments by the attendees that exceed 255 characters, the data will be truncated when pasted into Access."
            End If
            'Clean up header row by removing extra underscores and pipe symbols
            stReplace = "__"
            stReplacement = "_"
            .Rows("1:1").Replace What:=stReplace, Replacement:=stReplacement, SearchOrder:=xlByRows
            stReplace = "_|"
            stReplacement = ""
            .Rows("1:1").Replace What:=stReplace, Replacement:=stReplacement, SearchOrder:=xlByRows
            objapp.DisplayAlerts = True
        End If
'--Until Here
        
        If InStr(stfilepath, "Date_Range_Courses") > 0 Then
            .Cells(1, 1) = "UniqueKey"
            .Cells(1, 2) = "Acronym"
            .Cells(1, 3) = "EventCode"
            .Columns(4).NumberFormat = "m/d/yyyy"
            .Cells(1, 4) = "StartDate"
            .Columns(5).NumberFormat = "m/d/yyyy"
            .Cells(1, 5) = "EndDate"
            .Columns(6).NumberFormat = "h:mm AM/PM"
            .Cells(1, 6) = "StartTime"
            .Columns(7).NumberFormat = "h:mm AM/PM"
            .Cells(1, 7) = "EndTime"
            .Cells(1, 8) = "EventTitle"
            .Cells(1, 9) = "GeoArea"
            .Cells(1, 10) = "INSTRUCTORS"
            .Cells(1, 11) = "LocationName"
            .Cells(1, 12) = "Registered"
        ElseIf InStr(stfilepath, "LB_Event_Instructors_Fdn_Courses") > 0 Then
            .Cells(1, 1) = "UniqueKey"
            .Cells(1, 2) = "RecordNumber"
            .Cells(1, 3) = "SortName"
            .Cells(1, 4) = "FullName"
            .Cells(1, 5) = "PrimaryEmail"
            .Cells(1, 6) = "EventCode"
            .Cells(1, 7) = "Acronym"
            .Cells(1, 8) = "EventTitle"
            .Columns(9).NumberFormat = "m/d/yyyy"
            .Cells(1, 9) = "StartDate"
            .Columns(10).NumberFormat = "h:mm AM/PM"
            .Cells(1, 10) = "StartTime"
            .Columns(11).NumberFormat = "m/d/yyyy"
            .Cells(1, 11) = "EndDate"
            .Columns(12).NumberFormat = "h:mm AM/PM"
            .Cells(1, 12) = "EndTime"
            .Cells(1, 13) = "CustomerID"
            .Cells(1, 14) = "FirstName"
            .Cells(1, 15) = "MiddleName"
            .Cells(1, 16) = "LastName"
        End If
        
    End With
        'wb.Sheets(1).Range("A:BB").NumberFormat = "@"
        'http://www.rondebruin.nl/win/s5/win001.htm
        'filefomatstuff 20150405
        'wb.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xls", FileFormat:=56
        'ElseIf Right(stfilepath, 1) = "v" Then
        'wb.SaveAs FileName:=FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
'    ExcelVersion = wb.FileFormat
    If globalintSheetIndex <> 0 Then
        wb.ActiveSheet.Copy
        objapp.ActiveWorkbook.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
        wb.Close savechanges:=False
    Else
        wb.SaveAs FilePath(stfilepath) & FileNameNoExt(stfilepath) & "txt.xlsx", FileFormat:=51
        wb.Close 'savechanges:=False
    End If
    
    objapp.DisplayAlerts = True
    objapp.Quit

    Set objapp = Nothing
'    Select Case ExcelVersion
'        Case 39
'            ExcelVersion = 5 'excel7
'        Case 50, 51
'            ExcelVersion = 9 'excel12 (2007-2013, xlsx)
'        Case 56
'            ExcelVersion = 8 'excel8 (97-2003 format in Excel 2007-2013, xls)
'    End Select
'    globalintExcelVer = ExcelVersion
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:03
Joined
Feb 19, 2002
Messages
42,970
@Galaxiom,
I wouldn't go there. On a form created by the wizard, Access named the control using the dot. However, you can't actually use the dot when you reference the control using VBA. Look how Access substitutes the underscore for the dot in the lower picture.
 

Attachments

  • BadNames.JPG
    BadNames.JPG
    48.4 KB · Views: 1,305

Users who are viewing this thread

Top Bottom