CSV import overwriting the next column after the last one in the CSV file

[Tanquen]

The CSV file only has columns A through F but it is overwriting column G so G is blank after import.

If I open the CSV it stops at column F and I don't see anything in column G.

Sub Import_Azure_CSV_OLD()
    Dim ws As Worksheet
    Dim csvFilePath As Variant
    Dim lastRow As Long
    
    ' Set the worksheet where you want to paste the data
    Set ws = ThisWorkbook.Sheets("From Azure DB")
    
    ' Prompt the user to select the CSV file
    csvFilePath = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select CSV File")
    
    ' Check if the user selected a file or canceled the dialog
    If csvFilePath = False Then
        MsgBox "No file selected. Import canceled.", vbExclamation
        Exit Sub
    End If
    
    ' Clear existing data in the destination range starting from A9
    ws.Range("A9").CurrentRegion.Clear
    
    ' Import the CSV file starting at row 9
    With ws.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=ws.Range("A9"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFilePlatform = xlWindows
        .Refresh
    End With
    
    ' Make row 9 bold
    ws.Rows(9).Font.Bold = True
End Sub

 

[myall_blues]

What is in the worksheet before you import the CSV file?

If there are data in A9 through to Gxx, then this line:

ws.Range("A9").CurrentRegion.Clear

will wipe it all.

CurrentRegion includes any cells containing data out to bounding rows and columns that are completely blank. So in the following example, ws.Range("A9").CurrentRegion.Clear will clear the entire shaded area. Test it in your sheet by selecting A9 and pressing Ctrl+Shift+8.

If that's not the issue then I would open your CSV file in a text editor (like Notepad) and see if the lines have a trailing comma on the end.

 

[Tanquen]

Not sure I follow? I'm trying to stop it from deleting or overwriting anything other than the columns it actually has in the CSV.

I have data in column G that I need to keep that it is removing.

Opening the CSV in Excel there is nothing in column G and looking at it in notepad there is no extra comma at the end.

"System","Sub-System","Equipment","Point","Value","Timestamp"
"CP100","DataHall110","PDU7-02B-1","Output Active Energy Delivered","7282.056","2024-08-07T16:41:48.928Z"

I'm trying to get it to just overwrite columns A-F starting at row 9. Not sure why it deletes what is in column G.

 

[Tanquen]

I added the X in column H and I and now it deletes everything. It must be the Destination range of A9. I've tried changing it to A9:F9 but it still deletes everything.

 

[myall_blues]

I mean that the line

ws.Range("A9").CurrentRegion.Clear

Is probably the problem.
Try changing it to

ws.Range(“A9:F100”).Clear

and see what happens.
If that works we can worry about fixing up the 100 to get the last row instead.

 

[Tanquen]

I mean that the line

ws.Range("A9").CurrentRegion.Clear

Is probably the problem.
Try changing it to

ws.Range(“A9:F100”).Clear

and see what happens.
If that works we can worry about fixing up the 100 to get the last row instead.

Sorry, I was focused on the Destination range. This seems to work.

ws.Range("A9:F" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).Clear

This gives an error:

ws.Range(“A9:F100”).Clear

 

[myall_blues]

Good news. Glad you got it fixed.

 

[Joe4]

This gives an error:

ws.Range(“A9:F100”).Clear

Yes, VBA does not like slanted quotes, only the straight up and down ones.
So that should be:

ws.Range("A9:F100").Clear