Text import wizard bug

[J.W. Zondag]

Does anyone know when the Text wizard import bug is going to be fixed in
Excel 2007. I'm reffering to the fact that the 'Decimal Separator' and
'Thousands Separator' setting are being ignorred.

 

[Jim Rech]

MS probably hasn't announced a fix date for this if they've acknowledged it
at all. No sooner than Excel 14, if then, I'd guess.

 

[Joel]

You can write you own macro to read the data. It is not very hard. Below is
a sample to read csv data.



Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0



Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"

Newfolder = Application.GetOpenFilename("CSV (*.csv),*.csv")
If Not Newfolder = False Then
Folder = ""
Do While InStr(Newfolder, "\") > 0
Folder = Folder & Left(Newfolder, InStr(Newfolder, "\"))
Newfolder = Mid(Newfolder, InStr(Newfolder, "\") + 1)
Loop
'remove last character which is a \
Folder = Left(Folder, Len(Folder) - 1)
End If

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = LastRow + 1
First = True
Do
If First = True Then
filename = Dir(Folder & "\*.csv")
First = False
Else
filename = Dir()
End If
If filename <> "" Then
'open files
Set fread = fsread.GetFile(Folder & "\" & filename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
CommaPosition = InStr(InputLine, ",")
If CommaPosition > 0 Then
Data = Trim(Left(InputLine, CommaPosition - 1))
InputLine = Mid(InputLine, CommaPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
Loop
End Sub

 

[Air_Cooled_Nut]

LOL! It all depends upon your audience ;-)

 

[J.W. Zondag]

You can write you own macro to read the data. It is not very hard. Below is
a sample to read csv data.



Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0



Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"

Newfolder = Application.GetOpenFilename("CSV (*.csv),*.csv")
If Not Newfolder = False Then
Folder = ""
Do While InStr(Newfolder, "\") > 0
Folder = Folder & Left(Newfolder, InStr(Newfolder, "\"))
Newfolder = Mid(Newfolder, InStr(Newfolder, "\") + 1)
Loop
'remove last character which is a \
Folder = Left(Folder, Len(Folder) - 1)
End If

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
RowCount = LastRow + 1
First = True
Do
If First = True Then
filename = Dir(Folder & "\*.csv")
First = False
Else
filename = Dir()
End If
If filename <> "" Then
'open files
Set fread = fsread.GetFile(Folder & "\" & filename)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
CommaPosition = InStr(InputLine, ",")
If CommaPosition > 0 Then
Data = Trim(Left(InputLine, CommaPosition - 1))
InputLine = Mid(InputLine, CommaPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
Loop
End Sub

I know, thanks for the code.
The issue is, as stated by Toby, it depends on your audience.
I have a customer which is using Excel to analyse data, which is exported
from a legacy application. The application outputs dates, numbers and so on
in US format. Being in the Netherlands he has set up the Windows local for
the Netherlands, which is the default for Excel and as such the data is
messed up when imported into Excel 2007 (former versions of Excel don't have
this bug).

A bypass to the bug is setting the Windows local to en-us prior to importing
so that the import wizard of Excel uses that local formatting and I have
written 2 macro's fro Excel (setting the local to en-us and setting the local
back to nl-nl)

For the record, the bug does not occure in VBA, programmaticly OpenText
behaves just as it should. Where it not for the fact that the data my
customer uses varries and changes frequently we could write import functions.

Lastly, since discovering this bug it's been a growing annoyance. Paying for
a piece of software like Excel 2007, just to have this bug which is
introduced in Excel 2007, being a pain in the ...

 

[Jialiang Ge [MSFT]]

Hello J.W.Zondag,

Welcome to Microsoft Newsgroup Support Service! And Thank you for reporting
the "Decimal Separator", "Thousands Separator" problem to us. I am Jialiang
Ge from Microsoft, and will help you with the issue.

Microsoft Office development group has admitted the issue that separators
are unexpectedly ignored in Excel 2007 and they work fine in Office 2003 or
XP. We are looking at it for a fix in Office 2007 Service Pack 2. I cannot
promise the release date of SP2, but I can notify you as soon as it's
published. A workaround in short run is to use a macro to do the import
job, as Joel suggested.

Again, sorry for the inconveniences resulting from the Excel problem and
thank you for the great feedback. I believe that other community members
will also benefit from your experience sharing. Without the input of
numerous intelligent and warmhearted customers like you, our MSDN Managed
Newsgroup can never be as comprehensive and helpful as it is today. I
appreciate your time and efforts. If you have any other concerns or
questions feel free to let me know.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[J.W. Zondag]

Hello Jialiang,

Thanks for responding, I'm glad to heard that Microsoft is adressing the
problem.

JW

 

[Jialiang Ge [MSFT]]

Hello J.W.Zondag,

I can understand that you, and many other customers, are frustrated by the
'Separator' problem introduced by Excel 2007. I have sent emails to the
developers responsible for the issue and pushed them to fix it as soon as
possible. If there is anything else I can do for you, please do let me
know.

Again, I am sorry for the pains caused by it. I hope my eagerness to help
you can please you a little bit.

Regards,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================

 

[jomassoels]

Hello J.W.Zondag,

I can understand that you, and many other customers, are frustrated by the
'Separator' problem introduced by Excel 2007. I have sent emails to the
developers responsible for the issue and pushed them to fix it as soon as
possible. If there is anything else I can do for you, please do let me
know.

Again, I am sorry for the pains caused by it. I hope my eagerness to help
you can please you a little bit.

Regards,
Jialiang Ge  ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights..
=================================================

Hi Guys,

a solution to this:
just record the text import as a macro and
add this line to the script of the text import

.TextFileDecimalSeparator = "."

the whole thing looks like this and the macro opens all files:

fileToOpen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")


With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & fileToOpen _
, Destination:=Range("$A$3"))
.Name = fileToOpen
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = "'"
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 2, 2, 2, 2, 9, 9,
9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Works fine

Greetz
Jo