?
Solved

, within csv

Posted on 2011-10-13
8
Medium Priority
?
333 Views
Last Modified: 2012-05-12
when a csv file has commas in its values, how can we make sure that the field value is imported correctly.

for example:

Bush, George should come in as one field not two.
0
Comment
Question by:25112
8 Comments
 
LVL 7

Accepted Solution

by:
icenick earned 400 total points
ID: 36964651
Hello,

It depends on how you specify the delimiter. If you specify your delimiter as comma ',' then  it will be successful. Otherwise, if your delimiter is a semi colon or tab it won't work.

Make sure you specify the delimiter correctly.

Good Luck!
0
 
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 1200 total points
ID: 36964696
Either use a different separator or a fixed field width.
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 400 total points
ID: 36965960
Put quotes around the string field like this: "Bush, George"
It will open in Excel (and most other spreadsheet programs) without the quotes.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 1200 total points
ID: 36967507
Correct, you have 3 options:

1 - choose a different separator - you have to make sure your separator will not be present in data
2 - put quotes around your field - you still have to make sure that " will not be present in data
3 - fixed field lenght - you have to be sure that field lengths are not too short

All methods have advantages/disadvantages, my advice is: choose one and stick to that. For instance, if you choose a different separator like TAB or | or : - make sure that your client applications cannot enter these characters in a field.
0
 
LVL 5

Author Comment

by:25112
ID: 36972115
the tool requires only csv.. if you put another delimiter, it is not more a csv, right? (comma separated)

i am generating the csv file data from a sql query.. to assign quote or proper length spaces are both going to be a challenge. any thoughts of alternatives?
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36972915
I would go for option number 2, putting quotes around the field. In your query you can easily add the quotes between the fields like this:

select field1, '"' || field2 || '"', fiel3, field3
from table;

In the (oracle) sample above, field2 is your field that may contain spaces, output will look like this:

value1, "value2-last value2-first", value3

I'm trusting that your query already creates the , separators in the (spooled) output file.
0
 
LVL 38

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE earned 1200 total points
ID: 36972916
Typo, I meant:

select field1, '"' || field2 || '"', field3
from table;
0
 
LVL 5

Author Comment

by:25112
ID: 36977056
good idea.. thanks.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Progress
Starting up a Project

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question