EDN Admin
Well-known member
<p align=left><font face=Arial size=2>I am working on a project that involes reading an Excel file, picking through it, then creating a flat CSV file. One of the the data elements I am picking up is a 9 character string that may be numeric, or contain one or more alpha chatacters. Excel has a nasty habit of converting long numerics to scientific notaion. I can overcome this by converting the "general" string to "TEXT".</font>
<p align=left>
<p align=left>However, there is one instance where I can not get the function to work...the string is "90333E108" ( the CUSIP for USU). Regardless, the text string is saved properly into the CSV file. When opened in a text editor, it reads correctly.
<p align=left>
<p align=left>When opened with Excel, it is in scientific notation. Changing the field to "text" from "scientific" still displays "9.03E+112".
<p align=left>
<p align=left>Any ideas how to create an csv file that Excel will open and read correctly?
<p align=left>
<p align=left>You can test this by creating an Excel workbook, format all cells as TEXT and add the following data:
<p align=left>
<p align=left>
<p align=left>
<table style="width:162pt;border-collapse:collapse" cellspacing=0 cellpadding=0 width=216 border=0>
<colgroup>
<col style="width:54pt" span=3 width=72>
<tbody>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;width:54pt;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" width=72 height=17><font size=2>90333E108</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:54pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" width=72><font size=2>123</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:54pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" width=72><font size=2>2</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>abc</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>90333E108</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>r1312sda</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>123</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>90333E108</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>321</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>123</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>231</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>313</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>123</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2> </font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td></tr></tbody></table>
<p align=left>
<p align=left>then use the following function:
<p align=left>ActiveWorkbook.SaveAs Filename:="test.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False
<p align=left>
<p align=left>("xlCVS" instead of "xlCSVMSDOS") makes no difference.
<p align=left>
<p align=left>Open the file "test.csv" in note pad, you have :
<p align=left> <font size=2>
90333E108,123,2
abc,90333E108,r1312sda
123,abc,90333E108
321,123,abc
231,313,123 </font>
<p align=left>
<p align=left>Open in Excel and you have :
<p align=left>
<p align=left>
<table style="width:159pt;border-collapse:collapse" cellspacing=0 cellpadding=0 width=210 border=0>
<colgroup>
<col style="width:53pt" span=3 width=70>
<tbody>
<tr style="height:12.75pt" height=17>
<td class=xl22 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;width:53pt;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right width=70 height=17><font size=2>9.03E+112</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:53pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right width=70><font size=2>123</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:53pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right width=70><font size=2>2</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>abc</font></td>
<td class=xl22 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>9.03E+112</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>r1312sda</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right height=17><font size=2>123</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td>
<td class=xl22 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>9.03E+112</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right height=17><font size=2>321</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>123</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right height=17><font size=2>231</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>313</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>123</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2> </font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td></tr></tbody></table>
<p align=left>
<p align=left>
<p align=left>Any ideas?
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
View the full article
<p align=left>
<p align=left>However, there is one instance where I can not get the function to work...the string is "90333E108" ( the CUSIP for USU). Regardless, the text string is saved properly into the CSV file. When opened in a text editor, it reads correctly.
<p align=left>
<p align=left>When opened with Excel, it is in scientific notation. Changing the field to "text" from "scientific" still displays "9.03E+112".
<p align=left>
<p align=left>Any ideas how to create an csv file that Excel will open and read correctly?
<p align=left>
<p align=left>You can test this by creating an Excel workbook, format all cells as TEXT and add the following data:
<p align=left>
<p align=left>
<p align=left>
<table style="width:162pt;border-collapse:collapse" cellspacing=0 cellpadding=0 width=216 border=0>
<colgroup>
<col style="width:54pt" span=3 width=72>
<tbody>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;width:54pt;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" width=72 height=17><font size=2>90333E108</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:54pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" width=72><font size=2>123</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:54pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" width=72><font size=2>2</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>abc</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>90333E108</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>r1312sda</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>123</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>90333E108</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>321</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>123</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>231</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>313</font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>123</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2> </font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td>
<td class=xl24 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td></tr></tbody></table>
<p align=left>
<p align=left>then use the following function:
<p align=left>ActiveWorkbook.SaveAs Filename:="test.csv", FileFormat:=xlCSVMSDOS, CreateBackup:=False
<p align=left>
<p align=left>("xlCVS" instead of "xlCSVMSDOS") makes no difference.
<p align=left>
<p align=left>Open the file "test.csv" in note pad, you have :
<p align=left> <font size=2>
90333E108,123,2
abc,90333E108,r1312sda
123,abc,90333E108
321,123,abc
231,313,123 </font>
<p align=left>
<p align=left>Open in Excel and you have :
<p align=left>
<p align=left>
<table style="width:159pt;border-collapse:collapse" cellspacing=0 cellpadding=0 width=210 border=0>
<colgroup>
<col style="width:53pt" span=3 width=70>
<tbody>
<tr style="height:12.75pt" height=17>
<td class=xl22 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext 0.5pt solid;width:53pt;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right width=70 height=17><font size=2>9.03E+112</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:53pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right width=70><font size=2>123</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext 0.5pt solid;border-left:windowtext;width:53pt;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right width=70><font size=2>2</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2>abc</font></td>
<td class=xl22 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>9.03E+112</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>r1312sda</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right height=17><font size=2>123</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td>
<td class=xl22 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>9.03E+112</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right height=17><font size=2>321</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>123</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2>abc</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" align=right height=17><font size=2>231</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>313</font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent" align=right><font size=2>123</font></td></tr>
<tr style="height:12.75pt" height=17>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext 0.5pt solid;border-bottom:windowtext 0.5pt solid;height:12.75pt;background-color:transparent" height=17><font size=2> </font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td>
<td class=xl23 style="border-right:windowtext 0.5pt solid;border-top:windowtext;border-left:windowtext;border-bottom:windowtext 0.5pt solid;background-color:transparent <font size=2> </font></td></tr></tbody></table>
<p align=left>
<p align=left>
<p align=left>Any ideas?
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
<p align=left>
View the full article