将CSV导入到Excel - 自动将“文字转换为列”和“插入表”

问题描述

我想在Excel 2010上打开我的CSV（逗号分隔）文件，并自动将文本转换为列，然后选择所有活动单元格并插入带标题的表格。

是否可以在我的功能区中添加一个按钮来为我执行所有这些操作？

我经常使用不同大小的CSV文件，

解决方案

这有点晚了，但我只是跑过了问题...

这是从选择器中选择特定文件：

  Sub OpenCSV（）
 Dim fd As FileDialog 
设置fd = Application.FileDialog（msoFileDialogFilePicker）
 fd.AllowMultiSelect = True 
 fd.Show 
 fileItem In fd.SelectedItems 
 Workbooks.OpenText文件名：= _ 
 fileItem _ 
，原产地：= 65001，StartRow：= 1，DataType：= xlDelimited，TextQualifier：= _ 
 xlDoubleQuote，ConsecutiveDelimiter：= False，Tab：= True，Semicolon：= False，_ 
 Comma：= False，Space：= False，Other：= False，TrailingMinusNumbers：= True 
 Next 
 End Sub 
  

这将打开所选文件夹中的所有CSV文件：

Sub OpenCSVFolder（）
Dim fd As FileDialog
设置fd = Application.FileDialog（msoFileDialogFolderPicker）
fd.AllowMultiSelect = True
fd.Show
对于每个folderItem在fd.SelectedItems
fileItem = Dir（folderItem& \& * .csv）
当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

请注意，这些文件设置为 Tab Delimited - 更改分隔符更新 Tab：= True 或 Comma：= False 参数。

I'd like to open my CSV (comma-delimited) file on Excel 2010 and automagically convert text to columns and then select all active cells and insert table with headers.

Is it possible to add a button to my ribbon that would do all this for me?

I quite often work with CSV files with different sizes and I find it to be bit of a pain to do this manually each time.

解决方案

A bit late for this, but I just ran across the question...

This is for selecting specific files from a picker:

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 a chosen 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

Note that these files are set to Tab Delimited - change the delimiter by updating the Tab:=True or Comma:=False parameters.

这篇关于将CSV导入到Excel - 自动将“文字转换为列”和“插入表”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！