excel - VBA in Access - Comand: docmd.TransferText - Exporting table to CSV text file - Delimiting data with ; in place of , - S

admin管理员组

文章数量:1435208

good luck for all, :)

I'm trying to export an Access table to a CSV file, but the delimiter I want to use is a ; in spite of the default ,.

I have a table, tUsuarias, like this,

Well, for export that table to a CSV file, I use an VBA macro that is launched when a form button is pressed, like this,

Private sub cmdExportar_Microdatos_Click()
  Dim oCSV as String
  oCSV = CurrentProject.Path & "\microdatos_Usuarias.csv"
  doCmd.TransferText acExportDelim, , "tUsuarias", oCSV, True
  MsgBox "Exportacion realizada OK", vbInformation, "Microdatos exportados"
End Sub

And, in the same folder I want to place the resulting CSV file of export the said tUsuarias table, I made a schema.ini file trying to change the CSV format file for delimiting the data of all rows by ; in place of ,

[microdatos_Usuarias.csv]
ColNameHeader=True
Format=Delimited(;)
DecimalSymbol=","

But, when I export the table into the microdatos_Usuarias.csv file, the result is this,

"cId";"cApellidos";"cNombre";"cAltura"
1,"Garcia Hernando","Raul",172
2,"Martin Castro","Alicia",158
3,"Gonzalez Navarro","Ana",182
4,"Peyrona Perez","Alberto",165
5,"Garcia Fierro","Angel",175

....replacing the , by the ; only for the header row (the field names row)y, leaving the default CSV format (with the , delimiter) for the rest of the rows of the file (the "data" rows...).

But what I want, is to replace the , by the ; delimiter for all the rows. My answer is what I make incorrect for to obtain the result of the only header row format changing (and, in the case I omite the header row (with the ColNameHeader=False command...), none of the rows exported are format changed...), and how can I do for obtainig my desired result of changing all the rows format

Regards!!

good luck for all, :)

I'm trying to export an Access table to a CSV file, but the delimiter I want to use is a ; in spite of the default ,.

I have a table, tUsuarias, like this,

Well, for export that table to a CSV file, I use an VBA macro that is launched when a form button is pressed, like this,

Private sub cmdExportar_Microdatos_Click()
  Dim oCSV as String
  oCSV = CurrentProject.Path & "\microdatos_Usuarias.csv"
  doCmd.TransferText acExportDelim, , "tUsuarias", oCSV, True
  MsgBox "Exportacion realizada OK", vbInformation, "Microdatos exportados"
End Sub

And, in the same folder I want to place the resulting CSV file of export the said tUsuarias table, I made a schema.ini file trying to change the CSV format file for delimiting the data of all rows by ; in place of ,

[microdatos_Usuarias.csv]
ColNameHeader=True
Format=Delimited(;)
DecimalSymbol=","

But, when I export the table into the microdatos_Usuarias.csv file, the result is this,

"cId";"cApellidos";"cNombre";"cAltura"
1,"Garcia Hernando","Raul",172
2,"Martin Castro","Alicia",158
3,"Gonzalez Navarro","Ana",182
4,"Peyrona Perez","Alberto",165
5,"Garcia Fierro","Angel",175

....replacing the , by the ; only for the header row (the field names row)y, leaving the default CSV format (with the , delimiter) for the rest of the rows of the file (the "data" rows...).

But what I want, is to replace the , by the ; delimiter for all the rows. My answer is what I make incorrect for to obtain the result of the only header row format changing (and, in the case I omite the header row (with the ColNameHeader=False command...), none of the rows exported are format changed...), and how can I do for obtainig my desired result of changing all the rows format

Regards!!

• excel
• vba
• csv
• ms-access

Share Improve this question Follow asked Nov 18, 2024 at 13:20 Raul-Angel GarcíaRaul-Angel García 1122 bronze badges 2

• Try with doCmd.TransferText acExportDelim, , "tUsuarias", oCSV, False. If that works then maybe you will need to export twice (header then data) and merge the files after. – Bart McEndree Commented Nov 18, 2024 at 13:34
• stackoverflow/a/13483597/478884 has a different method which seems to work for me (tested using export from Excel, since I don't have Access) – Tim Williams Commented Nov 18, 2024 at 17:26

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

I suggest doing a manual export, and creating the export "spec" while doing as such.

Hence select the table in the nav page, and then from ribbon under Export, choose text file.

Hence this:

And then on next panel, hit advanced:

Change delimiter to a ";", and then hit save-as to save the export spec.

On next page, check the include field name on first row.

Hence:

Note very closely, that you are prompted again to update/save the spec.

Hence this:

Now, we have a saved export spec.

Hence this code, and we STILL require "true" for the has field names.

Dim sFile       As String

sFile = "c:\test\myhotels.txt"

DoCmd.TransferText acExportDelim, "myexport", "tblHotelsA", sFile, True

So, try using the export wizard, and then save the export spec. Once the manual process works as expected, and you answer "yes" to updating the export spec, then in VBA that export spec should respect your settings.

DoCmd.TransferText uses regional settings of Windows for CSV files

You can write a "manual" code to do DoCmd.TransferText

Private Sub cmdExportar_Microdatos_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim oCSV As String
    Dim fso As Object
    Dim fileStream As Object
    Dim i As Integer
    Dim header As String
    Dim row As String
    
    ' File path for the CSV
    oCSV = CurrentProject.Path & "\microdatos_Usuarias.csv"
    
    ' Open the table as a recordset
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tUsuarias", dbOpenSnapshot)
    
    ' Create a FileSystemObject to write to the file
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fileStream = fso.CreateTextFile(oCSV, True, True) ' True for overwrite, True for Unicode
    
    ' Write the header row
    header = ""
    For i = 0 To rs.Fields.Count - 1
        header = header & rs.Fields(i).Name & ";"
    Next i
    header = Left(header, Len(header) - 1) ' Remove the trailing semicolon
    fileStream.WriteLine header
    
    ' Write the data rows
    Do While Not rs.EOF
        row = ""
        For i = 0 To rs.Fields.Count - 1
            row = row & rs.Fields(i).Value & ";"
        Next i
        row = Left(row, Len(row) - 1) ' Remove the trailing semicolon
        fileStream.WriteLine row
        rs.MoveNext
    Loop
    
    ' Close the file and clean up
    fileStream.Close
    Set fileStream = Nothing
    Set fso = Nothing
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    
End Sub

本文标签：