Excel 2007 import issue/bug

[abcef12345]

Hello

I have a .csv (comma separated) file with names, addresses and notes in.

When I double click it, the file opens file and all values are displayed in separate cells.

However, when I import it, the fields merge and split on their own. The commas seem to be ignored.

This seems to happen whenever the notes field has a paragraph in. So instead of displaying a new line in the same cell, Excel07 starts a new row in my spreadsheet!

I'm importing via "import types, text, choosing field delimited, comma."

Has anyone else had import problems? (Some of my customers need to import. I'm not just asking for the good of my health

Thanks!

 

[SkipVought]

Hi,

"However, when I import it, the fields merge and split on their own. The commas seem to be ignored."

When you use Data > Import External Data > IMPORT... the Import Wizard asks you for parsing specifications.

However, if you are using an existing querytable, some previous parsing spec may give you unreliable results.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abcef12345]

Hi Skip, thanks for the reply

Yes I select "comma" when the wizard gives me the options. But in the preview in the bottom the columns are already screwy and this doesn't sort them out.

It seems that as soon as I import the file the columns screw up. Does this happen for you?

 

[SkipVought]

Can you post several lines from your .csv file, that would demonstrate the issue?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

What is the list separator in your regional settings (for csv export/import excel uses it as "comma")?

combo

 

[abcef12345]

Thanks for your help so far guys.

Combo, My regional setting use a comma as a delimiter.

Skip, I've realised that when I insert multiple paragraphs into the database, when I download them and open them in wordpad they looks like this:

Here is an example:

"Journalist Name","Journalist Position","Journalist Email","Journalist Patch","Media Outlet Name","Publisher Name","Address","Address 2","Address 3","City","County","Postcode","Country","Website","Journalist Phone","Media Outlet Phone","Media Outlet Fax","Publisher Phone","Publisher Fax","News Desk","Circulation","Media Type","Category"
"Eric Cantona","","eric@mufc.com","paragraph1

paragraph2

paragraph3","","","","","","","","","United Kingdom","","","","","","","eric@mufc.com","","","Sport"

and then when I import, Excel interprets these newlines as a new row...!

I'm inputting data from my website into a database. I can then download this data as a CSV file.

 

[SkipVought]

"...when I import, Excel interprets these newlines as a new row"

When I copied and pasted your text example, I got SIX rows[tt]
1: Journalist Name,... (Parses A to W; 22 COMMAS)
2: Eric Cantona,... (Parses A to D: 3 COMMAS)
3:
4: paragraph2...
5:
6: paragraph3... (Parses A to T; 19 COMMAS)
[/tt]
Where is the problem?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abcef12345]

Yeah that's what I mean. All those paragraphs are between double quotes, so should therefore be in the same cell. This would mean that record would be on one row.

If I download a file that looks like this (with paragraphs), how can I use the Excel import method to get the data on one row?

With it starting new rows whenever there is a paragraph I'm getting incorrect data in columns.

 

[SkipVought]

Are you saying that the entire thing...
[tt]
"Journalist Name","Journalist Position","Journalist Email","Journalist Patch","Media Outlet Name","Publisher Name","Address","Address 2","Address 3","City","County","Postcode","Country","Website","Journalist Phone","Media Outlet Phone","Media Outlet Fax","Publisher Phone","Publisher Fax","News Desk","Circulation","Media Type","Category"
"Eric Cantona","","eric@mufc.com","paragraph1

paragraph2

paragraph3","","","","","","","","","United Kingdom","","","","","","","eric@mufc.com","","","Sport"
[/tt]
ought to be in ONE row? That's NOT how paragraphs work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abcef12345]

Oh crumbs!

So I want to do something that can't be done! Great haha

So in this situation there is no way to import the data how I'd like? It's weird, because if you just open it straight away that whole row will be on one line/row, not several.

 

[SkipVought]

I do not understand.

"...if you just open it straight away that whole row will be on one line/row, not several."

How can multiple PARAGRAPHS be in a whole row?

When I pasted YOUR EXAMPLE, as I described, it was SIX ROWS. Didn't matter is I pasted in Excel or Notepad. SIX ROWS!!!!!

Now it's possible to modify that, but it's not simple. It would take VBA code to do it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[abcef12345]

if you save that text as a csv file and open it in excel you'll see what i mean.

then on a new spreadsheet, import the same csv file and you see the difference.

this is hard to explain. thanks for the help

 

[SkipVought]

I COPIED your example into a Notepad.txt

I imported the file into excel. It parsed as expected.

I did a SaveAs as a .csv

I imported the .csv and it paresed as expected.

I OPENED the .csv and it parsed as expected.

I don't see the problem.

If you care to, you can email your examples & I'll take a look...

ii36250 at bellhelicopter dot textron dot com

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's the situation that the OP has been trying to describe.

The .csv input file contains this value that is behaving one way when the .csv file is OPENED in Excel and another way when the .csv file is IMPORTED via Data > Import external data...

The Value:
[tt]
"par1

par2

par3"
[/tt]
preceed and followed by a COMMA

When the .csv is OPENED, The Value is parsed into a single cell, whiich is as it should be, I believe. The remaining values following are parsed into the same row.

When the .csv is IMPORTED, The Value is placed on 7 separate rows, and the remaining values are parsed into the SEVENTH row. It seems that the Text qualifier, which is designated as "

, is ignored.

Can anyone provide some insight?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, this is not a 2007 issue. It also happens in 2003.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}