Solved

Please check to see if there are any characters that are invalid in my CSV

Posted on 2011-03-21
11
268 Views
Last Modified: 2012-05-11
Hi I'm getting an import error with my 3rd party program.

Can you please check to see if anything is in invalid in my attached CSV?
Cake.csv
0
Comment
Question by:Computer Guy
  • 5
  • 5
11 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 35178635
Hello,

What error are you getting with your 3rd party program?

The CSV imports fine into Excel. I see that most rows are just comma delimited, but the last field in some rows is encased in double quotes " to ensure that commas within the field are not treated as separate fields. That is standard practice.

What is the 3rd party tool?

cheers, teylyn
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 35178662
Pilotkaraoke.com
Songbook creator
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35178704
The ampersand & and the apostrophe ' are invalid characters in csv files.
Cake-updated.csv
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 35178713
Is there any other way around it?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35178964
Did you try to quote them?
Cake-updated-2.csv
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 3

Author Comment

by:Computer Guy
ID: 35179056
jimyX,

What ever you did worked!

I didn't see any double quotes in the CSV file you uploaded. Can you please be a little more specific on what you did so I can try it?

Thanks!
0
 
LVL 24

Accepted Solution

by:
jimyX earned 500 total points
ID: 35179088
I just quoted the fields that included & or '.

For example the following lines:
1,Bryan Adams,Cut's Like A Knife
1,Sonny & Cher,I Got You Babe

Became:

1,Bryan Adams,"Cut's Like A Knife"
1,"Sonny & Cher",I Got You Babe

And I updated all the records.
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 35179094
Is there a way to do that audomatically? of do I have to do it manually?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35179239
May be by using Excel (some codes) that should be possible. Anyway, I use different programming language and that is very much possible.
0
 
LVL 3

Author Comment

by:Computer Guy
ID: 35181542
I did notice when I added the " around the tags with ', when I imported them into my program, I had to remove the ", when I was in the program.

Is there a way to have the " hidden in excel so it won't be part of the text I import?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35182287
If you import by using a program that can't handle & or ' (unless they are quoted) then Excel can't help you because the csv file is a standard format and does not relate to excel. Excel is just a reader. It's better if you eliminate or replace the & and ' with another suitable symbol or character.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now