All geek questions in one place

Import CSV to Excel - automatically “Text to Columns” and “Insert Table”

I would like to open a CSV file (comma delimited) in Excel 2010 and automatically convert the text to columns, then select all active cells and insert a table with headers.

Can I add a button to my feed that will do all this for me?

I often work with CSV files of different sizes, and I find it a little painful to do this manually each time.

+4
source share
1 answer

A bit late for this, but I just ran into a question ...

This is for selecting specific files from the collector:

Sub OpenCSV() Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) fd.AllowMultiSelect = True fd.Show For Each fileItem In fd.SelectedItems Workbooks.OpenText Filename:= _ fileItem _ , Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True Next End Sub

This will open all CSV files in the selected folder:

 Sub OpenCSVFolder() Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFolderPicker) fd.AllowMultiSelect = True fd.Show For Each folderItem In fd.SelectedItems fileItem = Dir(folderItem & "\" & "*.csv") While fileItem <> "" Workbooks.OpenText Filename:= _ folderItem & "\" & fileItem _ , Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True fileItem = Dir Wend Next End Sub

Please note that these files are set to Tab Delimited - change the separator by updating the Tab:=True or Comma:=False settings.

+2
source

Source: https://habr.com/ru/post/1388927/

More articles:


All Articles