CSV File Problem

[Saruman]

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.

 

[Niek Otten]

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.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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
| ---------------------------------------------------------------------------
|
|

 

[Saruman]

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

 

[Earl Kiosterud]

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.

 

[Dave Peterson]

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.

 

[Saruman]

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?

 

[Beege]

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?

<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

 

[Saruman]

Beege,

Thanks for the info, will make other investigations on original file.