Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Set Default Import Data Delimiter to Comma

Posted on 2009-04-10
6
Medium Priority
?
1,399 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 2000 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

609 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