Excel loading CSV problem

  • Thread starter Thread starter ewdtang
  • Start date Start date
E

ewdtang

My workplace just upgraded the software on our computers, now my coworkers
and I have trouble loading CSV files. Using Office 11, (I believe we used
to use Office 10) when we load a column of data that is formatted #-#,
where
a-b
a : 1-31
b : 1-12
this becomes a day-month date
a : 1-12
b : 0,31+
this becomes a month-year date

We open CSV files (autogenerated by other software) by double clicking on
them.

Is there any way to turn off this behaviour and treat it as text? The
previous setup we had, MS Excel treated this as text, and there was no
autoconversion on load problem.

Is the only option to import the data? That's a considerably longer
process than what we do now.


The column is an internal part identification, and it'd be hard to change
it's representational format.

Thanks.
 
I am surprised to hear your previous version of Excel did not produce the same
results when opening the CSV files.

You might try changing the extension on the CSV files to *.TXT

When opened, these will pop up the Text Import Wizard where, in the third step
you can designate Column Data Format as Text.


Gord Dibben MS Excel MVP
 
Hello

We were surprised by the change in functionality after the upgrade.

When I attempt your suggestion, I do not get the Text Import Wizard,
instead the CSV data is dumped into Column A as a single column.

(which will mean I would use the Text-to-column tool to bring up the Text
Import Wizard after selecting Column A)

Well it does involve less steps that using Data Import.


Is there any way to change the default cell type to TEXT instead of
GENERAL?


Thanks.
 
See in-line........

Hello

We were surprised by the change in functionality after the upgrade.
Click to expand...

Excel has treated 1-31 and 1-23 as dates for as long as I can remember through
about 4 or 5 versions. If your previous version did not then something else was
preventing the change to a date funtion.
When I attempt your suggestion, I do not get the Text Import Wizard,
instead the CSV data is dumped into Column A as a single column.
Click to expand...

TIW opens for me in 2003 when I open a *.csv file renamed to *.txt
(which will mean I would use the Text-to-column tool to bring up the Text
Import Wizard after selecting Column A)
Yes

Well it does involve less steps that using Data Import.
Again............yes


Is there any way to change the default cell type to TEXT instead of
GENERAL?
Click to expand...
Not that I know of. When d-clicking to open a CSV file, it opens in the
deafault Excel workbook.

I know of no way to get it to open in a customized workbook template.


Gord
 
TIW opens for me in 2003 when I open a *.csv file renamed to *.txt
Click to expand...

If you start Excel 2003 and use the File > Open menu option, then you do,
but if you right-click a txt file and choose "Open With..." and select
Microsoft Excel, you don't get the Text import wizard, it just dumps
everything into column A, as the OP says. So there is some inconsistency in
the behaviour.
 
Is there any way to turn off this behaviour and treat it as text?
Click to expand...

How about this HTA?

<!-- FileName:ReadAsText.hta -->
<head><meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<hta:application ID="HTA" windowstate="minimize">
<script language=vbs>
Option Explicit
Dim Arg, pPath
Arg = Mid(HTA.commandLine, Len(document.urlunencoded) + 4)
If Arg = "" Then
pPath = document.urlunencoded: Reg_UnReg
Else
OpenCSVasText
End If
window.Close
'
Sub OpenCSVasText()
Dim I, Format(255), Buf, ShName, xlApp
If LCase(Right(Arg, 4)) <> ".csv" Then Exit Sub
For I = 0 To 255: Format(I) = Array(I + 1, 2): Next
With CreateObject("Scripting.FileSystemObject")
ShName = .GetBaseName(Arg)
With .OpenTextFile(Arg): Buf = .ReadAll: .Close: End With
End With
With CreateObject("htmlfile")
.ParentWindow.ClipBoardData.SetData "Text", Buf
End With
With CreateObject("Excel.Application")
.Visible = True: .WorkBooks.Add(1).Sheets(1).Name = ShName
.ActiveSheet.Paste
.Selection.TextToColumns , 1, , , , , True, , , , Format
End With
End Sub
'
Sub Reg_UnReg
Const TKey = "HKCR\Excel.CSV\shell\", Menu = "ReadAsText"
Dim EN
With CreateObject("WScript.Shell")
.SendKeys "%{ESC}"
On Error Resume Next
.RegRead TKey & Menu & "\"
EN = Err.Number
On Error GoTo 0
If EN <> 0 Then
.RegWrite TKey, Menu & ",Open"
.RegWrite Tkey & Menu & "\", Menu
.RegWrite Tkey & Menu & "\command\", "mshta """ & pPath & """ %L"
.PopUp "Registered to the context menu.", 1
Else
.RegDelete Tkey & Menu & "\command\"
.RegDelete Tkey & Menu & "\"
.RegWrite TKey, "Open"
.PopUp "Deleted from the context menu.", 1
End If
End With
End Sub
</script></head></html>
 
Stephen Bye said:
If you start Excel 2003 and use the File > Open menu option, then you do,
but if you right-click a txt file and choose "Open With..." and select
Microsoft Excel, you don't get the Text import wizard, it just dumps
everything into column A, as the OP says. So there is some inconsistency in
the behaviour.
Click to expand...

Yes, that is what I did,
right-clicked and used open-with.
 
Unfortunately that script requires write access to the registry, which
we're banned from accessing.

Thanks anyways.
 
Unfortunately that script requires write access to the registry, which
we're banned from accessing.
Click to expand...

The script can be invoked by drag & drop the csv file to the script file
(or the shortcut to the script file).
If you are forbidden to access the registry, try this.
 
Sorry, I forgot to show the additional script in previous post.
Here it is.

' FileName : ReadAsText.vbs
Option Explicit
' modify this line according to hta file's location
Const HTAPath = "file://C:\Program Files\VBS\ReadAsText.hta"
Dim Args
Set Args = WScript.Arguments
If Args.Count <> 1 Then WScript.Quit
If LCase(Right(Args(0), 4)) <> ".csv" Then WScript.Quit
With CreateObject("WScript.Shell")
.Run "mshta """ & HTAPath & """ " & Args(0)
End With

and the following HTA is the improved version.

<!-- FileName:ReadAsText.hta -->
<head><meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<hta:application ID="HTA" windowstate="minimize">
<script language=vbs>
Option Explicit
Dim Arg, pPath
Arg = Mid(HTA.commandLine, Len(document.urlunencoded) + 4)
If Arg = "" Then
pPath = document.urlunencoded: Reg_UnReg
Else
OpenCSVasText
End If
window.Close
'
Sub OpenCSVasText()
Dim I, Format(255), Buf, ShName, xlApp
If LCase(Right(Arg, 4)) <> ".csv" Then Exit Sub
For I = 0 To 255: Format(I) = Array(I + 1, 2): Next
With CreateObject("Scripting.FileSystemObject")
ShName = .GetBaseName(Arg)
With .OpenTextFile(Arg): Buf = .ReadAll: .Close: End With
End With
With CreateObject("htmlfile")
.ParentWindow.ClipBoardData.SetData "Text", Buf
End With
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then Set xlApp = CreateObject("Excel.Application")
On Error GoTo 0
With xlApp
.Visible = True: .WorkBooks.Add(1).Sheets(1).Name = ShName
.ActiveSheet.Paste
.Selection.TextToColumns , 1, , , , , True, , , , Format
.Cells(1).TextToColumns , 1, , , , , False, , , , Format
End With
Set xlApp = Nothing
With CreateObject("WScript.Shell")
.AppActivate "Microsoft Excel"
.SendKeys "% x"
End With
End Sub
'
Sub Reg_UnReg
Const TKey = "HKCR\Excel.CSV\shell\", Menu = "ReadAsText"
Dim EN
With CreateObject("WScript.Shell")
.SendKeys "%{ESC}"
On Error Resume Next
.RegRead TKey & Menu & "\"
EN = Err.Number
On Error GoTo 0
If EN <> 0 Then
.RegWrite TKey, Menu
.RegWrite Tkey & Menu & "\", Menu
.RegWrite Tkey & Menu & "\command\", "mshta """ & pPath & """ %L"
.PopUp "Registered to the context menu.", 1
Else
.RegDelete Tkey & Menu & "\command\"
.RegDelete Tkey & Menu & "\"
.RegWrite TKey, ""
.PopUp "Deleted from the context menu.", 1
End If
End With
End Sub
</script></head></html>
 
Miyahn said:
in message news:%[email protected]

The script can be invoked by drag & drop the csv file to the script file
(or the shortcut to the script file).
If you are forbidden to access the registry, try this.
Click to expand...

Dragging and dropping a CSV file onto the VBS file results in a "Bad file
name or number" VBScript runtime error at line 20 (in the .HTA file)
 
Dragging and dropping a CSV file onto the VBS file results in a "Bad file
name or number" VBScript runtime error at line 20 (in the .HTA file)
Click to expand...

Probably, you've dropped the protocol portion "file://" from HTAPath.
 
Hello,

Miyahn said:
in message news:ed%[email protected]

Probably, you've dropped the protocol portion "file://" from HTAPath.
Click to expand...

No, it runs the VBscript, and executes the HTA. The error occurs from the
HTA,


MS Script Editor says that the following produces the error:
With .OpenTextFile(Arg): Buf = .ReadAll: .Close: End With


Thanks.
 
No, it runs the VBscript, and executes the HTA. The error occurs from the
HTA,
Click to expand...

Well, debug the script in the HTA with insert alert commands like this.

----------------------------------------
<script language=vbs>
Option Explicit
Dim Arg, pPath

alert HTA.commandline
alert document.urlunencoded
Arg = Mid(HTA.commandLine, Len(document.urlunencoded) + 4)

alert Arg
If Arg = "" Then
pPath = document.urlunencoded: Reg_UnReg
Else
OpenCSVasText
End If
window.Close
 
Back
Top