Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
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 "J.W. Zondag" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
LOL! It all depends upon your audience ;-)
-- Toby Erkson http://excel.icbm.org/ "Joel" wrote: You can write you own macro to read the data. It is not very hard... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
"Joel" wrote:
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 "J.W. Zondag" wrote: 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. 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 ... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
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 , 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: . ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
Hello Jialiang,
Thanks for responding, I'm glad to heard that Microsoft is adressing the problem. JW |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
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 , 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: . This posting is provided "AS IS" with no warranties, and confers no rights. ================================================= |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text import wizard bug
On 6 apr, 22:52, (Jialiang Ge [MSFT])
wrote: 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 , 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: . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Start Excel in Text Import Wizard for data import | Setting up and Configuration of Excel | |||
Text Import Wizard | Excel Programming | |||
Text Import Wizard | Excel Discussion (Misc queries) | |||
Text Import Wizard | Excel Discussion (Misc queries) | |||
Text Import Wizard from vba | Excel Programming |