Solved

Set Default Import Data Delimiter to Comma

Posted on 2009-04-10
6
1,377 Views
Last Modified: 2012-05-06
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
Comment
Question by:dastrw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 2

Expert Comment

by:pgunda
ID: 24117862
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
 
LVL 2

Author Comment

by:dastrw
ID: 24117999
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
 
LVL 2

Accepted Solution

by:
pgunda earned 500 total points
ID: 24118222
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:dastrw
ID: 24118852
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
 
LVL 2

Expert Comment

by:pgunda
ID: 24118929
yes
0
 
LVL 2

Author Comment

by:dastrw
ID: 24119116
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question