Discussion:
Problem with " when Importing large text file
(too old to reply)
Alpha
2005-03-14 19:57:03 UTC
Permalink
Hi,
I have a large text file that Excel won't load up completely. The file is
CSV but has extra " embedded in a string that causes error when I import it.
Is there a way to eliminate these embedded " with some tool? Below is a
sample of my importing text:

"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
TRANSPORT @ 2570 BUSH STREET, SAN FRANCISCO, CA 94115 THANK
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
TRANSPORT @ 2570 BUSH STREET, SAN FRANCISCO, CA 94115 THANK
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
TRANSPORT @ 2570 BUSH STREET, SAN FRANCISCO, CA 94115 THANK
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
TRANSPORT @ 2570 BUSH STREET, SAN FRANCISCO, CA 94115 THANK
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""


Thanks, Alpha
Michael C#
2005-03-14 20:20:44 UTC
Permalink
I don't think this is a SQL Server issue... Sounds like an Excel issue.

But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can have items
like this:

Please make your payment payable to ALLIED MEDICAL
TRANSPORT @ 2570 BUSH STREET, SAN FRANCISCO, CA 94115 THANK
YOU

And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
Perhaps one of your items is something like this:

8"x11"

In which case the internal quotes are going to cause problems when the app
tries to read them:

"8"x11""

If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The file is
CSV but has extra " embedded in a string that causes error when I import it.
Is there a way to eliminate these embedded " with some tool? Below is a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Alpha
2005-03-14 20:35:03 UTC
Permalink
The problem is the extra " embedded in a string. For example, "This is the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file format that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files are given
to me and I can't load it in Excel completely to change
the delimiter to tab.

Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can have items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when the app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The file is
CSV but has extra " embedded in a string that causes error when I import it.
Is there a way to eliminate these embedded " with some tool? Below is a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Michael C#
2005-03-14 20:56:26 UTC
Permalink
That was my recommendation - as I said, "if you can arrange it" - convert it
to Tab-delimited. But, that aside, they didn't give you a true CSV file if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to read the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.

Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send it in
Tab-delimited, pipe-delimited or some other format. That or get cracking on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This is the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file format that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files are given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can have items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when the app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The file is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool? Below is a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Alpha
2005-03-14 21:07:03 UTC
Permalink
Thank you. I think I will break the file into several files and then import
it to Excel to clean it up and then import them one by one. I think
that will work.

Thanks,
Alpha
Post by Michael C#
That was my recommendation - as I said, "if you can arrange it" - convert it
to Tab-delimited. But, that aside, they didn't give you a true CSV file if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to read the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.
Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send it in
Tab-delimited, pipe-delimited or some other format. That or get cracking on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This is the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file format that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files are given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can have items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when the app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The file is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool? Below is a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
shriop
2005-03-14 23:17:26 UTC
Permalink
If you find that you have to handle these files on a routine basis, you
might see what my csv parser can do for you. It is not free, but it is
made to attempt to handle what would normally be considered invalid
unescaped double quotes inside text qualified/double quote delimited
data specifically because I've heard odd rumors, and I think at one
point was even able to recreate this problem using FoxPro.
http://www.geocities.com/shriop/index.html You could use it to loop
over the data and either export it back out in proper csv that sql
server would handle, or change the delimiter all together.
Michael C#
2005-03-14 23:29:16 UTC
Permalink
You could write a program that attempts to figure it out yourself.
Basically you would:

1) Read in a line
2) Scan it character by character
2a) If the following conditions are true, then you are at the end of a
field: a) the current character is a quote, the next character is a comma,
and the next character after that is a quote or an end-of-line character
2b) If the following conditions are true, then you are at the beginning
of a field: a) the current character is a quote, the last character is a
comma and the character before that is a quote; or this quote character is
the first character of the line
3) Once you've determined the beginning and end of a field, write the field
out to a new file sans the extraneous quotes

Using this simple logic *should* cover 99% of your screwy CSV file. You
could then choose to write the file out to a Tab-Delimited, or other
delimited format if you want to keep the internal quotes; or you could write
it back out to a correct CSV format by eliminating the internal quotes.

Like I said, if I were you, I would definitely see if I could get clean
data - if you expect to receive any future data from these folks that is.
Will save you some headaches in the long run.
Post by Alpha
Thank you. I think I will break the file into several files and then import
it to Excel to clean it up and then import them one by one. I think
that will work.
Thanks,
Alpha
Post by Michael C#
That was my recommendation - as I said, "if you can arrange it" - convert it
to Tab-delimited. But, that aside, they didn't give you a true CSV file if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to read the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.
Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send it in
Tab-delimited, pipe-delimited or some other format. That or get cracking on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This is the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file format that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files are given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have
double-quotes
around comma-delimited elements. The reason being that you can have items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when the app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The
file
is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool? Below
is
a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Alpha
2005-03-14 23:57:02 UTC
Permalink
Yes, I will give that a try. Thank you very much.
Post by Michael C#
You could write a program that attempts to figure it out yourself.
1) Read in a line
2) Scan it character by character
2a) If the following conditions are true, then you are at the end of a
field: a) the current character is a quote, the next character is a comma,
and the next character after that is a quote or an end-of-line character
2b) If the following conditions are true, then you are at the beginning
of a field: a) the current character is a quote, the last character is a
comma and the character before that is a quote; or this quote character is
the first character of the line
3) Once you've determined the beginning and end of a field, write the field
out to a new file sans the extraneous quotes
Using this simple logic *should* cover 99% of your screwy CSV file. You
could then choose to write the file out to a Tab-Delimited, or other
delimited format if you want to keep the internal quotes; or you could write
it back out to a correct CSV format by eliminating the internal quotes.
Like I said, if I were you, I would definitely see if I could get clean
data - if you expect to receive any future data from these folks that is.
Will save you some headaches in the long run.
Post by Alpha
Thank you. I think I will break the file into several files and then import
it to Excel to clean it up and then import them one by one. I think
that will work.
Thanks,
Alpha
Post by Michael C#
That was my recommendation - as I said, "if you can arrange it" - convert it
to Tab-delimited. But, that aside, they didn't give you a true CSV file if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to read the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.
Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send it in
Tab-delimited, pipe-delimited or some other format. That or get cracking on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This is the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file format that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files are given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can have items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when the app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The
file
is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool? Below
is
a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Alpha
2005-03-14 23:59:03 UTC
Permalink
A different question, comma (,) is the delimiter currently used and double
quote is the text qualifier. If I have a string
"Please send the check, thanks", would the comma enclosed in the double
quote caused a problem when I'm importing to a table?

Thanks.
Post by Michael C#
You could write a program that attempts to figure it out yourself.
1) Read in a line
2) Scan it character by character
2a) If the following conditions are true, then you are at the end of a
field: a) the current character is a quote, the next character is a comma,
and the next character after that is a quote or an end-of-line character
2b) If the following conditions are true, then you are at the beginning
of a field: a) the current character is a quote, the last character is a
comma and the character before that is a quote; or this quote character is
the first character of the line
3) Once you've determined the beginning and end of a field, write the field
out to a new file sans the extraneous quotes
Using this simple logic *should* cover 99% of your screwy CSV file. You
could then choose to write the file out to a Tab-Delimited, or other
delimited format if you want to keep the internal quotes; or you could write
it back out to a correct CSV format by eliminating the internal quotes.
Like I said, if I were you, I would definitely see if I could get clean
data - if you expect to receive any future data from these folks that is.
Will save you some headaches in the long run.
Post by Alpha
Thank you. I think I will break the file into several files and then import
it to Excel to clean it up and then import them one by one. I think
that will work.
Thanks,
Alpha
Post by Michael C#
That was my recommendation - as I said, "if you can arrange it" - convert it
to Tab-delimited. But, that aside, they didn't give you a true CSV file if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to read the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.
Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send it in
Tab-delimited, pipe-delimited or some other format. That or get cracking on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This is the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file format that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files are given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can have items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading the file
will see 3 different comma-delimited elements when in fact it's only one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when the app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely. The
file
is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool? Below
is
a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to ALLIED MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
shriop
2005-03-15 05:27:17 UTC
Permalink
No, the comma won't cause a problem if you're using the sql server
import data wizard or a dts package. It will cause problems if you're
using bcp or bulk insert.
Michael C#
2005-03-15 14:10:10 UTC
Permalink
No, that's the purpose of the quotes, so you can embed the delimiter
character in your string. If, however, you have something like this:

"Please send the "check," thanks"

It will wreak all kinds of havoc on your import.
Post by Alpha
A different question, comma (,) is the delimiter currently used and double
quote is the text qualifier. If I have a string
"Please send the check, thanks", would the comma enclosed in the double
quote caused a problem when I'm importing to a table?
Thanks.
Post by Michael C#
You could write a program that attempts to figure it out yourself.
1) Read in a line
2) Scan it character by character
2a) If the following conditions are true, then you are at the end of a
field: a) the current character is a quote, the next character is a comma,
and the next character after that is a quote or an end-of-line character
2b) If the following conditions are true, then you are at the beginning
of a field: a) the current character is a quote, the last character is a
comma and the character before that is a quote; or this quote character is
the first character of the line
3) Once you've determined the beginning and end of a field, write the field
out to a new file sans the extraneous quotes
Using this simple logic *should* cover 99% of your screwy CSV file. You
could then choose to write the file out to a Tab-Delimited, or other
delimited format if you want to keep the internal quotes; or you could write
it back out to a correct CSV format by eliminating the internal quotes.
Like I said, if I were you, I would definitely see if I could get clean
data - if you expect to receive any future data from these folks that is.
Will save you some headaches in the long run.
Post by Alpha
Thank you. I think I will break the file into several files and then import
it to Excel to clean it up and then import them one by one. I think
that will work.
Thanks,
Alpha
Post by Michael C#
That was my recommendation - as I said, "if you can arrange it" -
convert
it
to Tab-delimited. But, that aside, they didn't give you a true CSV
file
if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to
read
the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.
Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send
it
in
Tab-delimited, pipe-delimited or some other format. That or get
cracking
on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This
is
the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file
format
that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files
are
given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can
have
items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading
the
file
will see 3 different comma-delimited elements when in fact it's
only
one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when
the
app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely.
The
file
is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool?
Below
is
a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE INVOICE
FORM","20030221","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Alpha
2005-03-15 17:29:04 UTC
Permalink
Got it! Thank you very much and have a great day.

Alpha
Post by Michael C#
No, that's the purpose of the quotes, so you can embed the delimiter
"Please send the "check," thanks"
It will wreak all kinds of havoc on your import.
Post by Alpha
A different question, comma (,) is the delimiter currently used and double
quote is the text qualifier. If I have a string
"Please send the check, thanks", would the comma enclosed in the double
quote caused a problem when I'm importing to a table?
Thanks.
Post by Michael C#
You could write a program that attempts to figure it out yourself.
1) Read in a line
2) Scan it character by character
2a) If the following conditions are true, then you are at the end of a
field: a) the current character is a quote, the next character is a comma,
and the next character after that is a quote or an end-of-line character
2b) If the following conditions are true, then you are at the beginning
of a field: a) the current character is a quote, the last character is a
comma and the character before that is a quote; or this quote character is
the first character of the line
3) Once you've determined the beginning and end of a field, write the field
out to a new file sans the extraneous quotes
Using this simple logic *should* cover 99% of your screwy CSV file. You
could then choose to write the file out to a Tab-Delimited, or other
delimited format if you want to keep the internal quotes; or you could write
it back out to a correct CSV format by eliminating the internal quotes.
Like I said, if I were you, I would definitely see if I could get clean
data - if you expect to receive any future data from these folks that is.
Will save you some headaches in the long run.
Post by Alpha
Thank you. I think I will break the file into several files and then import
it to Excel to clean it up and then import them one by one. I think
that will work.
Thanks,
Alpha
Post by Michael C#
That was my recommendation - as I said, "if you can arrange it" -
convert
it
to Tab-delimited. But, that aside, they didn't give you a true CSV
file
if
they embedded extra quotes inside the quoted strings and there's not a whole
heckuva lot you can do about that, other than 1) write a program to
read
the
file, recognize which fields have extra quotes, and modify them or 2) figure
out which lines have bad data and manually fix them.
Of course if you receive more of these files on a regular basis you'll more
than likely have the same problem. I would contact the sender - *if
possible* - and ask them to send clean data, or ask if they could send
it
in
Tab-delimited, pipe-delimited or some other format. That or get
cracking
on
a program that can read their data the way it was meant, as opposed to how
it was sent.
Post by Alpha
The problem is the extra " embedded in a string. For example, "This
is
the
carrier"s responsibility."
Excel is just to help me find the extra " but it's the text file
format
that
I'm importing. Are you suggesting for me to change
it to Tab delimited? I don't have a way of doing this. The files
are
given
to me and I can't load it in Excel completely to change
the delimiter to tab.
Thanks,
Alpha
Post by Michael C#
I don't think this is a SQL Server issue... Sounds like an Excel issue.
But since you're here: by definition, CSV files can have double-quotes
around comma-delimited elements. The reason being that you can
have
items
Please make your payment payable to ALLIED MEDICAL
YOU
And without double-quotes delimiting it, the application reading
the
file
will see 3 different comma-delimited elements when in fact it's
only
one
element. It sounds like you might have an extra " in there somewhere.
8"x11"
In which case the internal quotes are going to cause problems when
the
app
"8"x11""
If your data already contains quotes in it somewhere, and you can arrange
it, TAB-delimited format might work better for you...
Post by Alpha
Hi,
I have a large text file that Excel won't load up completely.
The
file
is
CSV but has extra " embedded in a string that causes error when I
import
it.
Is there a way to eliminate these embedded " with some tool?
Below
is
a
"ExtBillID","extTID","special_request","trip_ticket","extiid","invoice","extschedid","cycle","formid","form","formdate","standard_message","message","extsid","source","who_printed","date_printed","Printed","cancelled","why_cancelled","date_cancelled"
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE
INVOICE
FORM","20030221","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001601","","A3001601","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A1","Transfer","20030327","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE
INVOICE
FORM","20030323","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030328","Y","","","",""
"","","","A3001601","","A3001601","301","03","A-P INV","PRIVATE
INVOICE
FORM","20030331","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030417","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE
INVOICE
FORM","20030429","A12","CPMC HOSPITAL BILLING FOR END THE MONTH
WHEELCHAIR/VAN SERVICE BY ALLIED MEDICAL
TRANSPORT.","","10A1","Transfer","20030502","Y","","","",""
"","","","A3001601","","A3001601","301","01","A-P INV","PRIVATE
INVOICE
FORM","20030515","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030516","Y","","","",""
"","","","A3001601","","A3001601","301","02","A-P INV","PRIVATE
INVOICE
FORM","20030620","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A1","Transfer","20030710","Y","","","",""
"","","","A3001601","","A3001601","301","","D-HARDC","TICKET HARDCOPY
8x11","20030811","028","FILE TO CLOSED ACCTS
","","10A1","Transfer","20030829","Y","","","",""
"","","","A3001719","","A3001719","301","01","A-P INV","PRIVATE
INVOICE
FORM","20030221","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A1","Transfer","20030221","Y","","","",""
"","","","A3001719","","A3001719","301","","A-P INV","PRIVATE INVOICE
FORM","20030228","A01","Please make your payment payable to
ALLIED
MEDICAL
YOU.","","10A2","Transfer","20030327","Y","","","",""
"","","","A3001719","","A3001719","301","02","A-P INV","PRIVATE
INVOICE
FORM","20030321","A04","Please be advised we do ***NOT*** BILL ANY
INSURANCE
COMPANIES. This amount is now due and payable to: Allied Medical
Transport.","","10A2","Transfer","20030328","Y","","","",""
"","","","A3001719","","A3001719","301","","D-HARDC","TICKET HARDCOPY
8x11","20030407","028","FILE TO CLOSED ACCTS
","","10A2","Transfer","20030829","Y","","","",""
Thanks, Alpha
Continue reading on narkive:
Loading...