Forum Discussion
need help with importing complex csv, sorting it, then adding to table
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.
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:
- did you import it into survtbl? or you made a new table? I need it to update/replace all data on survtbl is the issue
dsj7419 Why is the 'survtbl' important? Your formulas contain direct references to cells in the Survey sheet that go beyond the bounds of the table range. So you are not using structured table references.
Anyway, see if the attached file works for you. It points to the CSV on your system and should work right away.
I picked the columns that you seem to be wanting with PQ and loaded them into a set of pivot tables producing the statistics close to yours. Can't tell why they are not matching, as I didn't look into your formulas in detail. With Power Pivot you can probably do it all in one pivot table, but I didn't go so far.
The message I want to convey is that PQ and a few pivot tables (or one Power Pivot) can probably do what you need without the need of VBA and rather complicated formulas.