CODEWITHSUNDEEP
vbams-accessms-access-2010
OldNick
OldNick

Reputation: 43

Getting rid of Double quotes around data in csv file in MS Access/VBA

I want to read the entire contents of the .csv file and then remove the double quotes around the data. I'm not sure what most efficient way is, preferably without first writing the contents of the file into an array and using replace

TOG2021-0012;"Sandbank AC145";63,8;"2021-01-13 06:32";"2021-01-13 06:49"

 Sub csv_import()

Dim strSQL1 As String
Dim strSQL2 As String
Dim intNAP As Integer
Dim intIDMaßnahme As Integer
Dim strFieldlist As String
Dim strDateipfad As String
Dim strMsg As String
Dim db As DAO.Database
Dim rsCSV As DAO.Recordset
Dim rsTemp As DAO.Recordset
Dim rsTennet As DAO.Recordset
Dim rsNeueMaßnahmen As DAO.Recordset
Dim rsNeueRegelungsstufe As DAO.Recordset
Dim rsImportRegelstufe_roh As DAO.Recordset
Dim rsImportRegelstufe As DAO.Recordset
Dim intRSCount As Integer
Dim i As Integer
strDateipfad = Dateiname
If strDateipfad = vbNullString Then Exit Sub

DoCmd.TransferText acImportDelim, "CSVImport_Tabelle", "tblCSVtemp", strDateipfad, HasFieldNames:=1
Set db = CurrentDbC

'###    erstellt temporäre Tabelle  ###

If TableExists("tblRSStemp") Then DoCmd.DeleteObject acTable, "tblRSStemp"

strFieldlist = "txtMaßnahmenNR String, txtNAP String, dblRegelungsstufe Float, txtDatStart String, datDatStart Date, fkIDNAP Integer"
db.Execute "CREATE TABLE tblRSStemp (" & strFieldlist & ")"

On Error GoTo ErrHandler
Set rsCSV = db.OpenRecordset("tblCSVtemp")
Set rsTemp = db.OpenRecordset("tblRSStemp")

'###    zählt die Datensätze der Abfrage Location    ###
    If Not rsCSV.EOF Then
        rsCSV.MoveLast
        intRSCount = rsCSV.RecordCount
        rsCSV.MoveFirst
    Else
        intRSCount = 0
    End If
    
'###    writes the CSV in a temporary tabel   ###
Do While Not rsCSV.EOF
    With rsTemp
         
 --->       '##### here first get rid of double quotes for the entire file ######
        If Left(rsCSV![Einspeiser/Netzbetreiber], 6) = "xyz" Then
        Debug.Print "ja"
        .AddNew
        !txtMaßnahmenNr = rsCSV![Massnahmen-Nr]
        !txtNAP = rsCSV![Einspeiser/Netzbetreiber]
        !dblRegelungsstufe = rsCSV!Feld3
        !txtDatStart = rsCSV!Start
        !datDatStart = CDate(rsCSV!Start)
            If Right(rsCSV![Einspeiser/Netzbetreiber], 1) = "6" Then
                    !fkIDNAP = 1
            Else
                    !fkIDNAP = 2
            End If
        .Update
        End If
    End With
    rsCSV.MoveNext
Loop

Upvotes: 0

Views: 277

Answers (1)

Applecore
Applecore

Reputation: 4099

One option may be to open the whole file into memory, do a find and replace, and then save as a new file for further processing. Something like:

Sub sReplaceInFile(strInFile As String, strOutFile As String, strOldChar As String, strNewChar As String)
    On Error GoTo E_Handle
    Dim intInFile As Integer
    Dim intOutFile As Integer
    Dim strInput As String
    intInFile = FreeFile
    Open strInFile For Input As intInFile
    strInput = Input(LOF(intInFile), intInFile)
    strInput = Replace(strInput, strOldChar, strNewChar)
    intOutFile = FreeFile
    Open strOutFile For Output As intOutFile
    Print #intOutFile, strInput
sExit:
    On Error Resume Next
    Close #intInFile
    Close #intOutFile
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sReplaceInFile", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

You can then call this procedure like:

call sReplaceInFile("C:\test\quote.txt","C:\test\no-quote.csv",chr(34),"")

Regards,

Upvotes: 2

Related Questions