• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1407
  • Last Modified:

Set Default Import Data Delimiter to Comma

When importing data manually from text files using the Text Import Wizard the default delimiter is 'tab'.  I would like to set my Excel default to 'comma' so that I can just bring up the import dialog and click 'Finish' rather than having to click to change the delimiter.  Also, if it is possible to select delimited rather than fixed width as a default, and to set whether or not to treat multiple delimiters together as one that would be even better.

Showing me a way to do this manually through the interface would be best.  But if it is something I can run as an addin or start-up macro that would be OK too.

This is just to improve efficiency.  There is no way to automate this because I want this to be the default for a wide variety of files.  Some of the files have a .csv extension while others have a .txt extension.  The files are generally number tables so commas work fine.  There is no reason to do a string replace on all the files to convert commas to tabs.

Is this possible in Excel 2007?
0
dastrw
Asked:
dastrw
  • 3
  • 3
1 Solution
 
pgundaCommented:
If all the file contains is a comma separated values, you could change the extension of the file to .CSV and open in Excel.
0
 
dastrwAuthor Commented:
I don't just want to import data into a blank workbook.  I also want to import data into an existing workbook.

Changing the extension won't work in these cases.
0
 
pgundaCommented:
Select the column you want to convert, and run the following macro by pressing a shortcut key assigned.

You can store this macro in your  "personal macro workbook" so it is available from any excel workbook. You can assign a short cut key to this.
Sub Macro2()
'
' Macro2 Macro
'

'
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub

If you need more help on using this macro.. let me know. But this works. I just tried this.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
dastrwAuthor Commented:
So basically you're saying is that I just:

1.  Import the file with the default Tab delimiters
2.  Since there are no Tabs in my file everything will end up in a single column
3.  Then do Text To Columns by macro with comma delimiters

Is that right?
0
 
pgundaCommented:
yes
0
 
dastrwAuthor Commented:
That worked.

I'm going to keep this Q open a bit longer to see if there are any ways to get the import to work correctly without having to do a Text-to-column.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now