Forum Discussion
need help with importing complex csv, sorting it, then adding to table
Hi all,
I'm reaching out with a somewhat complex issue related to a workbook called "surveyscores", which I'm hoping someone on this knowledgeable forum could assist me with. Here's what I'm dealing with:
The workbook consists of two tabs. The first one tracks our overall satisfaction scores for a specific category - a straightforward task. The second tab houses the data that I have formatted to meet my requirements using conditional formatting. The task at hand involves updating this workbook periodically. To do so, I export updated survey data from our main website every week, which includes both existing and new survey results. The goal is to import this CSV file into my current sheet, replacing old data with the new one, or perhaps only importing the new surveys.
To automate this process, I've ventured into using VBA, creating a button that imports the CSV file and handles the rest with a single click. However, I've hit a few roadblocks:
CSV Import: The current VBA script seems to struggle with complex boxes, particularly those containing extensive comments. These comments often get split into multiple cells due to the presence of commas in the text.
Data Integration: Despite successfully re-arranging the cells programmatically and deleting the unwanted columns, I've had considerable difficulty integrating the data into the table. I keep receiving an error message stating, "A table cannot overlap a range that contains a pivot table report, query results, protected cells or another table." Even attempts to resolve this, such as clearing the table data first or creating a new sheet/table programmatically, have yielded the same error.
You can find my current VBA code, along with the xlsm file and a test survey export, attached to this post.
Sub Button_Click()
' Step 1: Import CSV data by selecting the file
Dim fileDialog As fileDialog
Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
' Configure the file dialog properties
fileDialog.AllowMultiSelect = False
fileDialog.Title = "Select CSV File"
fileDialog.Filters.Clear
fileDialog.Filters.Add "CSV Files", "*.csv"
' Show the file dialog
Dim selectedFile As Variant
If fileDialog.Show = -1 Then
selectedFile = fileDialog.SelectedItems(1)
Else
Exit Sub ' User cancelled the file selection
End If
' Import CSV data into a new sheet
Dim importSheet As Worksheet
Set importSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
importSheet.Name = "Imported Data"
With importSheet.QueryTables.Add(Connection:="TEXT;" & selectedFile, Destination:=importSheet.Cells(1, 1))
.Name = "Imported CSV Data"
.FieldNames = True
.RowNumbers = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
' Step 2: Delete unwanted columns
Dim deleteColumns As Variant
deleteColumns = Array("A", "B", "G", "H", "L", "M", "N", "P", "Q", "R", "S", "T", "V", "W", "X", "Y", "Z", _
"AB", "AC", "AD", "AH", "AJ", "AK", "AL", "AM", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", _
"AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", _
"BK", "BN", "BO", "BP", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", _
"CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", _
"CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", _
"DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP", "DQ", "DR", "DS", "DT", "DU", "DV", _
"DW", "DX", "DY", "DZ", "EA", "EB", "EC", "ED", "EE", "EF", "EG", "EH", "EI", "EJ", "EK", _
"EL", "EM", "EN", "EO", "EP", "EQ", "ER", "ES", "ET", "EU", "EV", "EW", "EX", "EY", "EZ", _
"FA", "FB", "FC", "FD", "FE", "FF", "FG", "FH", "FI", "FJ", "FK", "FL", "FN", "FO", "FP", _
"FQ", "FR", "FS", "FT", "FU", "FV", "FW", "FX", "FY", "FZ", "GA", "GB", "GC", "GD", "GE", _
"GF", "GG", "GH", "GI", "GJ", "GK", "GL", "GM", "GN", "GO", "GP", "GQ", "GR", "GS", "GT", _
"GU", "GV", "GW", "GX", "GY", "GZ", "HA", "HB", "HC", "HD", "HE", "HF", "HG", "HH", "HI", _
"HJ", "HK", "HL", "HM", "HN", "HO", "HP", "HQ", "HR", "HS", "HY", "HZ", "IA", "IB", "IC", _
"ID", "IE", "IF", "IG", "IH", "II")
Dim col As Long
For col = UBound(deleteColumns) To LBound(deleteColumns) Step -1
importSheet.Columns(deleteColumns(col)).Delete
Next col
' Step 3: Move "AV Services met your needs" to column 1
Dim avServicesColumn As Range
Set avServicesColumn = importSheet.Columns("I")
avServicesColumn.Copy importSheet.Range("A1")
avServicesColumn.Delete
' Step 4: Clear the existing data in the "SurvTbl" table
Dim tblSheet As Worksheet
Dim tbl As ListObject
Set tblSheet = ThisWorkbook.Sheets("Surveys")
On Error Resume Next
Set tbl = tblSheet.ListObjects("SurvTbl")
On Error GoTo 0
If Not tbl Is Nothing Then
tbl.DataBodyRange.ClearContents
End If
' Step 5: Copy imported data to the "SurvTbl" table starting at A2
Dim importRange As Range
Set importRange = importSheet.UsedRange
importRange.Offset(1).Copy tblSheet.Range("A2")
' Delete the import sheet
Application.DisplayAlerts = False
importSheet.Delete
Application.DisplayAlerts = True
MsgBox "CSV data has been imported and updated in the table.", vbInformation
End Sub
Any guidance, insights, or recommendations you could provide would be incredibly appreciated. I've already spent two days trying to untangle this issue, and it has left me rather frustrated. I look forward to hearing your thoughts and potential solutions.
Thank you so much in advance for your time and expertise!
6 Replies
dsj7419 Well, you just discovered that VBA perhaps isn't the correct tool to extract and transform data from CSV files. Use Power Query instead. And from your file I note that you already tried that. And it produces clean data as far I could judge.
Some recommendations though. You seem to be wanting to delete most columns from the CSV. So do that first, then rename and reorder them and the last step could be to set the correct data types for the remaining columns. Your query, on the other hand, starts setting data types for all, then you rename one column, remove several columns, reorder the remaining ones and then remove most of the remaining ones. And then you load it to the Data Model and nothing more. Why not load it back to an Excel table and do the conditional formatting and analysis on that one? VBA is not needed in this case.
By the way, PQ allows you to connect to all files in a folder. So if you collect all CSV's in one folder, you can just dump new files in it, refresh the query and the table will be updated with the new data. It requires a bit of setting it up correctly, but it's not difficult.
- You're right, I did try that.. the problem is I got the same stupid error which is why I started down the vba path.
dsj7419 Can't replicate the issue or just don't understand. When I run your macro it imports garbage on many rows. PQ, on the other hand, gives me what seems to be a clean table with 107 rows. All nicely lined-up and long comments with with commas in them are returned in one single cell on the correct row. See example in the picture below:
