How to Import (tab-delimited) .CSV files into msExcel in a single step?

[Shiperton Henethe]

I am trying to import a tab-delimited CSV file into msExcel (2002)
just by right clicking on the file. Is this possible?!

Background.
I need to look at raw data coming in several times a day
and it is a pain in the a*** to have to go through the:

Data > Import external data > Import data > [find the file]
and then step through 'Text Import Wizard' etc...
each time I need to look at the data.

- Does anyone know if there is a way of configuring msExcel
to automatically import a .CSV in a *single step*?

[Currently, if I right-click on a .CSV file it imports it
incorrectly, failing to recognise columns etc and putting lots
of horrid little squares all over the place]

FWIW, Excel seems to be able to import tab-delimited
..TXT files no problem!


Ship
Shiperton Henethe

 

[JM]

csv files are expected to be delimited by comma or semi-colon depending on
geography. Why don't you ask that the person or system that provides the csv
files either makes them comma separated or gives the txt extension?

 

[Shiperton Henethe]

Hmmm... not possible.
(We are half way through a major project and if we changed the
delimiter now the other office would have to change their
macros and also the data would then become inconsistent...!)

Also, what happens if users want to enter commas and/or semi-colons
in the source data? Are they expected to be "escaped" in some standard way?

I find this most strange. Excel and import a tab-delimited .txt file with
no fuss if you right click on it...!

Likewise a tab-delimited CSV *can* be imported, it's just
that it takes a load of steps.


Ship
Shiperton Henethe

csv files are expected to be delimited by comma or semi-colon depending on
geography. Why don't you ask that the person or system that provides the csv
files either makes them comma separated or gives the txt extension?

I am trying to import a tab-delimited CSV file into msExcel (2002)
just by right clicking on the file. Is this possible?!

Background.
I need to look at raw data coming in several times a day
and it is a pain in the a*** to have to go through the:

Data > Import external data > Import data > [find the file]
and then step through 'Text Import Wizard' etc...
each time I need to look at the data.

- Does anyone know if there is a way of configuring msExcel
to automatically import a .CSV in a *single step*?

[Currently, if I right-click on a .CSV file it imports it
incorrectly, failing to recognise columns etc and putting lots
of horrid little squares all over the place]

FWIW, Excel seems to be able to import tab-delimited
.TXT files no problem!


Ship
Shiperton Henethe

 

[Dave Peterson]

Couldn't you just rename the *.csv to *.txt and use the wizard to parse your
data?

If you don't want to do that, maybe you could try changing your windows settings
(everyones!) to have the same list separator.

In Win98, I can change it via:
Windows start button
Settings|control Panel|Regional Settings Applet
Number Tab
(at the bottom)
List Separator

========
Another approach that I like is to record a macro when you do it once. Continue
recording while you add your formatting/headers/print setup/filters/everything.

But for this to work, you'll still have to rename the .csv to something else
(.txt). VBA just ignores parts of your code when it sees the .csv.

Then whenever you want to open a similar file, just replay your macro. It might
even make your life a bit easier.

You'll probably want to adjust the code a little to make it more generic. When
you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 1))

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

Hmmm... not possible.
(We are half way through a major project and if we changed the
delimiter now the other office would have to change their
macros and also the data would then become inconsistent...!)

Also, what happens if users want to enter commas and/or semi-colons
in the source data? Are they expected to be "escaped" in some standard way?

I find this most strange. Excel and import a tab-delimited .txt file with
no fuss if you right click on it...!

Likewise a tab-delimited CSV *can* be imported, it's just
that it takes a load of steps.

Ship
Shiperton Henethe

csv files are expected to be delimited by comma or semi-colon depending on
geography. Why don't you ask that the person or system that provides the csv
files either makes them comma separated or gives the txt extension?

I am trying to import a tab-delimited CSV file into msExcel (2002)
just by right clicking on the file. Is this possible?!

Background.
I need to look at raw data coming in several times a day
and it is a pain in the a*** to have to go through the:

Data > Import external data > Import data > [find the file]
and then step through 'Text Import Wizard' etc...
each time I need to look at the data.

- Does anyone know if there is a way of configuring msExcel
to automatically import a .CSV in a *single step*?

[Currently, if I right-click on a .CSV file it imports it
incorrectly, failing to recognise columns etc and putting lots
of horrid little squares all over the place]

FWIW, Excel seems to be able to import tab-delimited
.TXT files no problem!


Ship
Shiperton Henethe

 

[JM]

Hmmm... not possible.
(We are half way through a major project and if we changed the
delimiter now the other office would have to change their
macros and also the data would then become inconsistent...!)

Also, what happens if users want to enter commas and/or semi-colons
in the source data? Are they expected to be "escaped" in some standard

way?

That's fine because in csv format, adding double quotes around text fields
is possible. These are stripped when re-read
eg .Text Field1,"Text Field2",123,"Text, Field1","Text ;Field2",456

All of these text fields can be read back without quotes.

I find this most strange. Excel and import a tab-delimited .txt file with
no fuss if you right click on it...!

That's because Excel exports txt files as tab delimited and csv files as
comma delimited. For better or worse, that was the protocol that was
adopted. You can follow the standard way or you can re-invent the wheel and
do things differently. Unfortunately you did the latter.

Likewise a tab-delimited CSV *can* be imported, it's just
that it takes a load of steps.

One way to solve this is to create a macro. You can set up a button so that
1. It opens the file dialogue set to look for csv files with the initial
directory set to the place you put them.
2. It opens the selected file without the wizard (ie it all ends up in
column A)
3. It asks you if you want to split and "splits to columns" if you say yes.
This has the same effect as using the wizard.

The downside is that this occurs from within Excel, you've asked for a
Explorer Shell type command.

 

[Shiperton Henethe]

If you don't want to do that, maybe you could try changing your windows settings
(everyones!) to have the same list separator.

In Win98, I can change it via:
Windows start button
Settings|control Panel|Regional Settings Applet
Number Tab
(at the bottom)
List Separator

Ah-ha. This seems to work!

I can now view a files list in MSIE,
click on the file in quiestion,
Click to say I wish to open it,
and hey presto it views the file!

(Although it took my PC about 30 seconds
to do this first time for some reason)

I guess what I then see is an msExcel frame
within MSIE! Fab.

My only complaint is that
I'd rather open msExcel (2002) cleanly with full Excel
menus etc i.e. in its own window,
rather than have the Internet Explorer (v6.0.28 /Win2000)
browser confuse the issue.


With thanks


Ship
Shiperton Henethe

P.S. Is there any way to get MSIE to fire up Excel
when it is looking at a list of .txt files?

 

[Dave Peterson]

There's a setting in Windows that you can change. (I use win98, so it might
slightly be different for you.)

In win98, I can do this:
Start Windows Explorer
View|Folder Options
File Types Tab
scroll down to MS Excel Worksheet
Select it
click the edit button
There's an option to "browse in same window". Uncheck it.

That's where you can toggle the "confirm open after download", too.

How to Configure Internet Explorer to Open Office
Documents in the Appropriate Office Program Instead of in Internet Explorer
http://support.microsoft.com/?scid=162059

======
First, I don't think I'd try to reset my .txt files to open with excel. I like
to just doubleclick on them (in windows explorer) and have my text editor
(Notepad?) open.

But if you want to take a chance (I didn't try it at all!), maybe you could tell
windows to assign Excel to the .txt extension.

But before I'd screw it up real bad, I think I'd try it with a made-up extension
to see if that worked.

You could change the application via the menu options in Windows Explorer, but I
like to do it this way:

First, open windows explorer
rightclick New
Text document
(type in anything you want)
save it as "deletemelater.txt" (w/o the quotes)
Close it

now rename it to something that should never exist.
deletemelater.poo (a nice scatological extension)

Now shift-Rightclick on that file.
Chose "Open With"
point at excel and make sure you check that box at the bottom
(always use this program to open that extension (or something like that))

Then try it out.

If it works and you like it (I surely don't!), do the same shift-rightclick
stuff with a .txt file.

Remember, you're altering a lot of stuff.

 

[WordVBAProgrammer]

Dave, you said:
something else
(.txt). VBA just ignores parts of your code when it sees the .csv.<<

I suspected someting like this, but could you be more specific. I
need to explain it to my supervisors why I changed our file names from
'csv' to 'txt.'

Thanks

Dan
================

Couldn't you just rename the *.csv to *.txt and use the wizard to parse your
data?

If you don't want to do that, maybe you could try changing your windows settings
(everyones!) to have the same list separator.

In Win98, I can change it via:
Windows start button
Settings|control Panel|Regional Settings Applet
Number Tab
(at the bottom)
List Separator

========
Another approach that I like is to record a macro when you do it once. Continue
recording while you add your formatting/headers/print setup/filters/everything.

But for this to work, you'll still have to rename the .csv to something else
(.txt). VBA just ignores parts of your code when it sees the .csv.

Then whenever you want to open a similar file, just replay your macro. It might
even make your life a bit easier.

You'll probably want to adjust the code a little to make it more generic. When
you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 1))

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

Hmmm... not possible.
(We are half way through a major project and if we changed the
delimiter now the other office would have to change their
macros and also the data would then become inconsistent...!)

Also, what happens if users want to enter commas and/or semi-colons
in the source data? Are they expected to be "escaped" in some standard way?

I find this most strange. Excel and import a tab-delimited .txt file with
no fuss if you right click on it...!

Likewise a tab-delimited CSV *can* be imported, it's just
that it takes a load of steps.

Ship
Shiperton Henethe

csv files are expected to be delimited by comma or semi-colon depending on
geography. Why don't you ask that the person or system that provides the csv
files either makes them comma separated or gives the txt extension?




I am trying to import a tab-delimited CSV file into msExcel (2002)
just by right clicking on the file. Is this possible?!

Background.
I need to look at raw data coming in several times a day
and it is a pain in the a*** to have to go through the:

Data > Import external data > Import data > [find the file]
and then step through 'Text Import Wizard' etc...
each time I need to look at the data.

- Does anyone know if there is a way of configuring msExcel
to automatically import a .CSV in a *single step*?

[Currently, if I right-click on a .CSV file it imports it
incorrectly, failing to recognise columns etc and putting lots
of horrid little squares all over the place]

FWIW, Excel seems to be able to import tab-delimited
.TXT files no problem!


Ship
Shiperton Henethe

 

[Dave Peterson]

Show them an example. Run it once with the extension .csv and once with .txt.

A picture(?) will be worth a thousand words.

Dave, you said:
something else
(.txt). VBA just ignores parts of your code when it sees the .csv.<<

I suspected someting like this, but could you be more specific. I
need to explain it to my supervisors why I changed our file names from
'csv' to 'txt.'

Thanks

Dan
================

Couldn't you just rename the *.csv to *.txt and use the wizard to parse your
data?

If you don't want to do that, maybe you could try changing your windows settings
(everyones!) to have the same list separator.

In Win98, I can change it via:
Windows start button
Settings|control Panel|Regional Settings Applet
Number Tab
(at the bottom)
List Separator

========
Another approach that I like is to record a macro when you do it once. Continue
recording while you add your formatting/headers/print setup/filters/everything.

But for this to work, you'll still have to rename the .csv to something else
(.txt). VBA just ignores parts of your code when it sees the .csv.

Then whenever you want to open a similar file, just replay your macro. It might
even make your life a bit easier.

You'll probably want to adjust the code a little to make it more generic. When
you recorded your macro, you got something that looked like:

Option Explicit
Sub Macro1()

Workbooks.OpenText Filename:="C:\myfile.txt", Origin:=437, StartRow:=1, _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(15, 1), _
Array(41, 1))

End Sub

Well, instead of having your filename in the code, you can give the user a
chance to pick it themselves (take a look at getopenfilename in VBA's help):

Sub macro1A()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

Hmmm... not possible.
(We are half way through a major project and if we changed the
delimiter now the other office would have to change their
macros and also the data would then become inconsistent...!)

Also, what happens if users want to enter commas and/or semi-colons
in the source data? Are they expected to be "escaped" in some standard way?

I find this most strange. Excel and import a tab-delimited .txt file with
no fuss if you right click on it...!

Likewise a tab-delimited CSV *can* be imported, it's just
that it takes a load of steps.

Ship
Shiperton Henethe

csv files are expected to be delimited by comma or semi-colon depending on
geography. Why don't you ask that the person or system that provides the csv
files either makes them comma separated or gives the txt extension?




I am trying to import a tab-delimited CSV file into msExcel (2002)
just by right clicking on the file. Is this possible?!

Background.
I need to look at raw data coming in several times a day
and it is a pain in the a*** to have to go through the:

Data > Import external data > Import data > [find the file]
and then step through 'Text Import Wizard' etc...
each time I need to look at the data.

- Does anyone know if there is a way of configuring msExcel
to automatically import a .CSV in a *single step*?

[Currently, if I right-click on a .CSV file it imports it
incorrectly, failing to recognise columns etc and putting lots
of horrid little squares all over the place]

FWIW, Excel seems to be able to import tab-delimited
.TXT files no problem!


Ship
Shiperton Henethe