 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / February 2008CSV File Problem
Can any one explain why the following problem occurs please? A colleague has a small list of data which is a csv file exported from SQL. Column One is a serial number from 1 to 29. Column 2 is a number of transactions which have occurred.(it should look like example A below in Excel) 1 475 1,475 2 1732 21,732 3 1670 31,670 4 2176 42,176 5 608 5,608 6 1579 61,579 7 43101 743,101 8 54512 854,512 9 51258 951,258 10 61050 1,061,050 A B If I double click on the csv file, it opens in Excel but concatenates the serial number in front of the number of transactions.(like example B above). If I open Excel and use the Import Data route, then it creates 2 columns as expected. Anyones help would be appreciated, I would like to understand why this occurrs. Thank you in advance. -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- Hi Saruman, There is no comma between the first and the second iten. CSV stands for Comma Separated Values. Don't you get trouble with the 1,061,050 ? Many users find importing via the menu more convenient. You can also rename the .CSV file to a .TXT file; that will give you the menu anyway. Then you have complete control over how your data is interpreted.  SignatureKind regards, Niek Otten Microsoft MVP - Excel | Can any one explain why the following problem occurs please? | [quoted text clipped - 31 lines] | All Outgoing Mail Scanned By Symantec Antivirus 10 | --------------------------------------------------------------------------- Hi Niek, My apologies, I didnt quite explain properly. The original file was exported from SQL. The data in it when opened with notepad in the csv file appears like this: 1,475 2,1732 3,1670 4,2176 5,608 etc BUT, if I double click on the csv file so it opens in Excel, then it looks like this 1,475 21,732 31,670 42,176 5,608 61,579 743,101 854,512 951,258 1,061,050 The 1,061,050 should look like 10,61050 from the original text file. The first numbers on each line should be 1 to 10 inclusive followed by a comma, then followed by the qty of transactions. I hope I have explained it better this time. Thanks for your attention Saruman > Hi Saruman, > [quoted text clipped - 46 lines] > | All Outgoing Mail Scanned By Symantec Antivirus 10 > | --------------------------------------------------------------------------- You could rename the file to *.txt and then open it via File|Open. You'll see a text to columns wizard and you can specify that your fields be treated like text. Alternatively, you could use: Data|Import external data (xl2003) and see the same wizard. > Hi Niek, > My apologies, I didnt quite explain properly. [quoted text clipped - 87 lines] > > | All Outgoing Mail Scanned By Symantec Antivirus 10 > > | --------------------------------------------------------------------------- SignatureDave Peterson Saruman, You should give us an example of the text file, but not as it opened in Excel. Open it with something like Notepad and tell us what you see there. Then give us a corresponding example of how you want it parsed in the Excel columns. SignatureRegards from Virginia Beach, Earl Kiosterud www.smokeyl.com ----------------------------------------------------------------------- > Can any one explain why the following problem occurs please? > [quoted text clipped - 31 lines] > All Outgoing Mail Scanned By Symantec Antivirus 10 > --------------------------------------------------------------------------- Thank you all for your responses. I am an experienced user of Excel and was asking if anyone could explain the reason why Excel was acting in this fashion. The data in the original csv file is this: 1,475 2,1732 3,1670 4,2176 5,608 6,1579 7,43101 8,54512 9,51258 10,61050 11,39612 12,2219 13,1952 14,52939 15,73757 16,54246 17,41663 18,34684 19,662 20,1291 21,65284 22,75509 23,58580 24,69752 25,47558 26,1360 27,1638 28,62709 29,48425 But if I double click on the file, the data appears like this: 1,475 21,732 31,670 42,176 5,608 61,579 743,101 854,512 951,258 1,061,050 1,139,612 122,219 131,952 1,452,939 1,573,757 1,654,246 1,741,663 1,834,684 19,662 201,291 2,165,284 2,275,509 2,358,580 2,469,752 2,547,558 261,360 271,638 2,862,709 2,948,425 The first numbers on each line are the original serial numbers. I have never seen Excel do this before. Can anyone explain why it does this? If the data from the first list is pasted into notepad and saved as a csv file, when the csv file is double clicked then it opens in Excel as 2 columns. The original file does not act this way. Maybe it is something that SQL Server adds to the data? -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- > Saruman, > [quoted text clipped - 45 lines] > > --------------------------------------------------------------------------- > Thank you all for your responses. I am an experienced user of Excel and was > asking if anyone could explain the reason why Excel was acting in this [quoted text clipped - 70 lines] > ><snip> I played around with this a little... I replaced the comma with a "SINGLE LOW-9 QUOTATION", Alt+0130 on the keypad. Looks like a comma, but isn't. I couldn't get the bad character to change by a cut/paste into notepad. It stayed a "low quote", and the CSV imported as you report. I also tried playing with the Windows regional settings page. If the "List separator" isn't a comma, the data I cut/paste from your posting comes in incorrectly. If it is, then the data comes in correctly. Maybe something similar? Beege Beege, Thanks for the info, will make other investigations on original file. -- Saruman --------------------------------------------------------------------------- All Outgoing Mail Scanned By Symantec Antivirus 10 --------------------------------------------------------------------------- > > Thank you all for your responses. I am an experienced user of Excel and was > > asking if anyone could explain the reason why Excel was acting in this [quoted text clipped - 85 lines] > > Beege |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.