Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text fields with numbers in .csv file
Hi,
I'm programming an application that exports table data to a file with extension csv with columns separated by seicolumns (or xls separated by tab). The problem is that I have a field that holds a SSCC number (18 digit number) that is handled as a text field. When I open then file in Excel that field is converted to a cientific number like 3,56E+17. And is not all, in fact the number stored in cell is 356000000200000000 when then number that is in original file (if I open it with notepad) is 356000000200000013! It is rounding the number! I tried to add an apostrophe before the number but it didn't solved te problem too because it shows up in the field as part of the value, and that create me problems when I have to compare ou print values. Does any one knows a solution for this problem? Is there a char that I can add before the field that Excel interprets as a text field but know that that first char does not belong to the field value? Thanks, Pedro Gonçalves |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text fields with numbers in .csv file
Pedro,
If you record a macro whilst you DataGet External DataImport Text File, you get the chance to specify the data type of the columns. NickHK "Pedro Gonçalves" wrote in message ... Hi, I'm programming an application that exports table data to a file with extension csv with columns separated by seicolumns (or xls separated by tab). The problem is that I have a field that holds a SSCC number (18 digit number) that is handled as a text field. When I open then file in Excel that field is converted to a cientific number like 3,56E+17. And is not all, in fact the number stored in cell is 356000000200000000 when then number that is in original file (if I open it with notepad) is 356000000200000013! It is rounding the number! I tried to add an apostrophe before the number but it didn't solved te problem too because it shows up in the field as part of the value, and that create me problems when I have to compare ou print values. Does any one knows a solution for this problem? Is there a char that I can add before the field that Excel interprets as a text field but know that that first char does not belong to the field value? Thanks, Pedro Gonçalves |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text fields with numbers in .csv file
Hi Nick,
I don't see where I can define the type of each column. I can define a all column formatted as text, but when I get data from external it gets it already in cientific format, and with the numbers rounded. If I add the apostrophe before the data field the field shows up as text but with the ' visible. To convert the field I have to edit all of them one by one. Just need to edit and Enter. Regards, Pedro Gonçalves "NickHK" wrote: Pedro, If you record a macro whilst you DataGet External DataImport Text File, you get the chance to specify the data type of the columns. NickHK "Pedro Gonçalves" wrote in message ... Hi, I'm programming an application that exports table data to a file with extension csv with columns separated by seicolumns (or xls separated by tab). The problem is that I have a field that holds a SSCC number (18 digit number) that is handled as a text field. When I open then file in Excel that field is converted to a cientific number like 3,56E+17. And is not all, in fact the number stored in cell is 356000000200000000 when then number that is in original file (if I open it with notepad) is 356000000200000013! It is rounding the number! I tried to add an apostrophe before the number but it didn't solved te problem too because it shows up in the field as part of the value, and that create me problems when I have to compare ou print values. Does any one knows a solution for this problem? Is there a char that I can add before the field that Excel interprets as a text field but know that that first char does not belong to the field value? Thanks, Pedro Gonçalves |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Text fields with numbers in .csv file
Pedro,
In the wizard, when you see the list of columns, select the SSCC header. Now change the datatype from General to Text. NickHK "Pedro Gonçalves" wrote in message ... Hi Nick, I don't see where I can define the type of each column. I can define a all column formatted as text, but when I get data from external it gets it already in cientific format, and with the numbers rounded. If I add the apostrophe before the data field the field shows up as text but with the ' visible. To convert the field I have to edit all of them one by one. Just need to edit and Enter. Regards, Pedro Gonçalves "NickHK" wrote: Pedro, If you record a macro whilst you DataGet External DataImport Text File, you get the chance to specify the data type of the columns. NickHK "Pedro Gonçalves" wrote in message ... Hi, I'm programming an application that exports table data to a file with extension csv with columns separated by seicolumns (or xls separated by tab). The problem is that I have a field that holds a SSCC number (18 digit number) that is handled as a text field. When I open then file in Excel that field is converted to a cientific number like 3,56E+17. And is not all, in fact the number stored in cell is 356000000200000000 when then number that is in original file (if I open it with notepad) is 356000000200000013! It is rounding the number! I tried to add an apostrophe before the number but it didn't solved te problem too because it shows up in the field as part of the value, and that create me problems when I have to compare ou print values. Does any one knows a solution for this problem? Is there a char that I can add before the field that Excel interprets as a text field but know that that first char does not belong to the field value? Thanks, Pedro Gonçalves |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import a txt file with text fields that have meaningful leading sp | Excel Discussion (Misc queries) | |||
load a CSV file and have it take all fields as text | Excel Discussion (Misc queries) | |||
adding cells after stripping numbers out of text fields | Excel Discussion (Misc queries) | |||
Text file import using ADO - XL converts fields to dates | Excel Programming | |||
automatically open .txt file with all fields as text? | Excel Discussion (Misc queries) |