Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
A customer has sent me a csv file which contains two sheets when opened in
Excel 2000. This was done because the first sheet contained the maximum number of records Excel can handle. I need to import these lists into Paradox (a database), so I need to separate the two sheets into separate files, each saved as its own csv. In one of the columns, I've got long strings of data as shown below. These are UPC codes, and will never be used as numbers - just text. 10323900002958 10323900002965 16500505693 10323900005409 10323900006147 10015400022436 When I separate the two sheets into their own notebooks, and save as csv files with new names, a problem arises. When I either import the data into Paradox, or reopen the sheets in Excel, I find that this is what's happened to the list shown above: 1.03239E+13 1.03239E+13 16500505693 1.03239E+13 1.03239E+13 1.00154E+13 The shortest value is untouched, but the rest convert to exponent format. So, I figured Excel was seeing these as numbers. I opened a new sheet, formatted all cells as text, and tried copying the data to that sheet, using Paste Special / Values. Same problem. I'm am definitely choosing the right file type before doing the Save As. Even stranger: If I view the new csv file in a text editor like WinEdit, these strings look fine. Help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
You should never reopen CSV files "just to check" them, this will
damage any number formatting you created when you saved the file. If you still have access to the original CSV files, the file should not be opened but imported, on the odd chance that you do need to check it before importing into the other program. 1. From the menu, select Data/Import External Data/Import Data 2. Select the Delimited radio button, then click "Next" 3. In Step 3, select the columns that you want to be treated as text (the UPC codes in your case) and in the Column Data Format box, choose 'Text' and hit Finish. HTH, JP On Nov 20, 3:15 pm, "JoeSpareBedroom" wrote: A customer has sent me a csv file which contains two sheets when opened in Excel 2000. This was done because the first sheet contained the maximum number of records Excel can handle. I need to import these lists into Paradox (a database), so I need to separate the two sheets into separate files, each saved as its own csv. In one of the columns, I've got long strings of data as shown below. These are UPC codes, and will never be used as numbers - just text. 10323900002958 10323900002965 16500505693 10323900005409 10323900006147 10015400022436 When I separate the two sheets into their own notebooks, and save as csv files with new names, a problem arises. When I either import the data into Paradox, or reopen the sheets in Excel, I find that this is what's happened to the list shown above: 1.03239E+13 1.03239E+13 16500505693 1.03239E+13 1.03239E+13 1.00154E+13 The shortest value is untouched, but the rest convert to exponent format. So, I figured Excel was seeing these as numbers. I opened a new sheet, formatted all cells as text, and tried copying the data to that sheet, using Paste Special / Values. Same problem. I'm am definitely choosing the right file type before doing the Save As. Even stranger: If I view the new csv file in a text editor like WinEdit, these strings look fine. Help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
Hang on a second here. I mentioned that the CSV from the customer contains
two sheets. As far as I know, that really can't be a CSV file. I mean, a CSV is nothing but a text file containing commas as delimiters. It can't have multiple sheets. Right? "JP" wrote in message ... You should never reopen CSV files "just to check" them, this will damage any number formatting you created when you saved the file. If you still have access to the original CSV files, the file should not be opened but imported, on the odd chance that you do need to check it before importing into the other program. 1. From the menu, select Data/Import External Data/Import Data 2. Select the Delimited radio button, then click "Next" 3. In Step 3, select the columns that you want to be treated as text (the UPC codes in your case) and in the Column Data Format box, choose 'Text' and hit Finish. HTH, JP On Nov 20, 3:15 pm, "JoeSpareBedroom" wrote: A customer has sent me a csv file which contains two sheets when opened in Excel 2000. This was done because the first sheet contained the maximum number of records Excel can handle. I need to import these lists into Paradox (a database), so I need to separate the two sheets into separate files, each saved as its own csv. In one of the columns, I've got long strings of data as shown below. These are UPC codes, and will never be used as numbers - just text. 10323900002958 10323900002965 16500505693 10323900005409 10323900006147 10015400022436 When I separate the two sheets into their own notebooks, and save as csv files with new names, a problem arises. When I either import the data into Paradox, or reopen the sheets in Excel, I find that this is what's happened to the list shown above: 1.03239E+13 1.03239E+13 16500505693 1.03239E+13 1.03239E+13 1.00154E+13 The shortest value is untouched, but the rest convert to exponent format. So, I figured Excel was seeing these as numbers. I opened a new sheet, formatted all cells as text, and tried copying the data to that sheet, using Paste Special / Values. Same problem. I'm am definitely choosing the right file type before doing the Save As. Even stranger: If I view the new csv file in a text editor like WinEdit, these strings look fine. Help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
Right.
-- David Biddulph "JoeSpareBedroom" wrote in message ... Hang on a second here. I mentioned that the CSV from the customer contains two sheets. As far as I know, that really can't be a CSV file. I mean, a CSV is nothing but a text file containing commas as delimiters. It can't have multiple sheets. Right? "JP" wrote in message ... You should never reopen CSV files "just to check" them, this will damage any number formatting you created when you saved the file. If you still have access to the original CSV files, the file should not be opened but imported, on the odd chance that you do need to check it before importing into the other program. 1. From the menu, select Data/Import External Data/Import Data 2. Select the Delimited radio button, then click "Next" 3. In Step 3, select the columns that you want to be treated as text (the UPC codes in your case) and in the Column Data Format box, choose 'Text' and hit Finish. On Nov 20, 3:15 pm, "JoeSpareBedroom" wrote: A customer has sent me a csv file which contains two sheets when opened in Excel 2000. This was done because the first sheet contained the maximum number of records Excel can handle. I need to import these lists into Paradox (a database), so I need to separate the two sheets into separate files, each saved as its own csv. In one of the columns, I've got long strings of data as shown below. These are UPC codes, and will never be used as numbers - just text. 10323900002958 10323900002965 16500505693 10323900005409 10323900006147 10015400022436 When I separate the two sheets into their own notebooks, and save as csv files with new names, a problem arises. When I either import the data into Paradox, or reopen the sheets in Excel, I find that this is what's happened to the list shown above: 1.03239E+13 1.03239E+13 16500505693 1.03239E+13 1.03239E+13 1.00154E+13 The shortest value is untouched, but the rest convert to exponent format. So, I figured Excel was seeing these as numbers. I opened a new sheet, formatted all cells as text, and tried copying the data to that sheet, using Paste Special / Values. Same problem. I'm am definitely choosing the right file type before doing the Save As. Even stranger: If I view the new csv file in a text editor like WinEdit, these strings look fine. Help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
OK, so the guy who's sending them is naming the file with a csv extension,
but not choosing the correct file type, which wouldn't work anyway since the first sheet is already at its limit of records. Onward: The data looks right in the sheet when I open it. If I open a brand new Excel sheet, format the cells as text, and copy the data to the new sheet using Paste Special/Values, why are those long strings turning into exponential format? "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Right. -- David Biddulph "JoeSpareBedroom" wrote in message ... Hang on a second here. I mentioned that the CSV from the customer contains two sheets. As far as I know, that really can't be a CSV file. I mean, a CSV is nothing but a text file containing commas as delimiters. It can't have multiple sheets. Right? "JP" wrote in message ... You should never reopen CSV files "just to check" them, this will damage any number formatting you created when you saved the file. If you still have access to the original CSV files, the file should not be opened but imported, on the odd chance that you do need to check it before importing into the other program. 1. From the menu, select Data/Import External Data/Import Data 2. Select the Delimited radio button, then click "Next" 3. In Step 3, select the columns that you want to be treated as text (the UPC codes in your case) and in the Column Data Format box, choose 'Text' and hit Finish. On Nov 20, 3:15 pm, "JoeSpareBedroom" wrote: A customer has sent me a csv file which contains two sheets when opened in Excel 2000. This was done because the first sheet contained the maximum number of records Excel can handle. I need to import these lists into Paradox (a database), so I need to separate the two sheets into separate files, each saved as its own csv. In one of the columns, I've got long strings of data as shown below. These are UPC codes, and will never be used as numbers - just text. 10323900002958 10323900002965 16500505693 10323900005409 10323900006147 10015400022436 When I separate the two sheets into their own notebooks, and save as csv files with new names, a problem arises. When I either import the data into Paradox, or reopen the sheets in Excel, I find that this is what's happened to the list shown above: 1.03239E+13 1.03239E+13 16500505693 1.03239E+13 1.03239E+13 1.00154E+13 The shortest value is untouched, but the rest convert to exponent format. So, I figured Excel was seeing these as numbers. I opened a new sheet, formatted all cells as text, and tried copying the data to that sheet, using Paste Special / Values. Same problem. I'm am definitely choosing the right file type before doing the Save As. Even stranger: If I view the new csv file in a text editor like WinEdit, these strings look fine. Help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
Try formatting the column as Number with zero decimal places (not
Text). --JP On Nov 20, 5:01 pm, "JoeSpareBedroom" wrote: OK, so the guy who's sending them is naming the file with a csv extension, but not choosing the correct file type, which wouldn't work anyway since the first sheet is already at its limit of records. Onward: The data looks right in the sheet when I open it. If I open a brand new Excel sheet, format the cells as text, and copy the data to the new sheet using Paste Special/Values, why are those long strings turning into exponential format? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with CSV file
That worked. I have no idea why. Thanks.
"JP" wrote in message ... Try formatting the column as Number with zero decimal places (not Text). --JP On Nov 20, 5:01 pm, "JoeSpareBedroom" wrote: OK, so the guy who's sending them is naming the file with a csv extension, but not choosing the correct file type, which wouldn't work anyway since the first sheet is already at its limit of records. Onward: The data looks right in the sheet when I open it. If I open a brand new Excel sheet, format the cells as text, and copy the data to the new sheet using Paste Special/Values, why are those long strings turning into exponential format? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem opening file | Excel Discussion (Misc queries) | |||
Problem with csv file | Excel Discussion (Misc queries) | |||
I got a problem when tried to open the file! | Excel Discussion (Misc queries) | |||
File Name Problem | Excel Discussion (Misc queries) | |||
CSV file problem | Excel Discussion (Misc queries) |