problem with analysis server connection string

i have just installed a new version of analysis services 2000 on a test machine. when i try to connect to the local host i get the error "Datasource name is too long". I have set the datasource string to

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\serverName\MsOLAPRepository$\msmdrep.mdb

This is the same string as i have on other analysis servers, bar the server name, but it still does not work. any ideas?

The olap service is running correctly, just in case its a detail you need to know.

I assume you use Analysis Manager or DSO program to connect, and you change the "Repository Connection String" property from Analysis Manager. Can you check whether you can open msmdrep.mdb repository file from Access ? Or try to migrate repository to SQL Server - will you get the same error ?

Problem with an IIF Statement

In my group footer I build a string based on the number of rows in that
group plus the field the group was based on.
But if the group was based on a an empty string I get an #error in the
textbox.
So I wrapped the code with an IIF statement. If the field is empty do
nothing otherwise build the string.
= IIF(Fields!AdjustmentCode.Value = "","No Adjustment
Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
Description: " & Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower)FYI this is the error: The value expression for the textbox
'textbox35' contains an error: Object variable or With block
variable not set.|||VB.Net evaluates all parts of the IIF which is why you get the error. It
does not just evaluate the true portion. You could use code behind report to
do this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128709777.373098.129000@.g49g2000cwa.googlegroups.com...
> In my group footer I build a string based on the number of rows in that
> group plus the field the group was based on.
> But if the group was based on a an empty string I get an #error in the
> textbox.
> So I wrapped the code with an IIF statement. If the field is empty do
> nothing otherwise build the string.
> = IIF(Fields!AdjustmentCode.Value = "","No Adjustment
> Code",countrows("grpAdjustmentDesc") & " Records for Adjustment Code
> Description: " & Fields!AdjustmentCode.Value.trim & " - " &
> Fields!AdjustmentDescription.Value.tolower)
>|||Can you point me in some direction for a Code Behind example. I can't
find anything yet.
Thanks.|||Look in books on line for the phrase code block
They have an example with that. I tend to develop my code in vb.net, test it
and then copy it over.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128712212.284978.143180@.z14g2000cwz.googlegroups.com...
> Can you point me in some direction for a Code Behind example. I can't
> find anything yet.
> Thanks.
>|||All i see is XML. This is going to be a lot of work to just handle the
fact the the Rpt can't handle a blank value.
So your saying I can put vb code in this.
<Textbox Name="textbox35">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Left</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>25</ZIndex>
<rd:DefaultName>textbox35</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= countrows("grpAdjustmentDesc") &
" Records for Adjustment Code Description: " &
Fields!AdjustmentCode.Value.trim & " - " &
Fields!AdjustmentDescription.Value.tolower</Value>
</Textbox>|||Can you post the hyperlink from the RSBook online address bar to where
the sample is.|||I put this in the code window
<Code>
Public Function test() As String
Dim strTest As String
strTest = "BLAH BLAH BLAH"
Return strTest
End Function
</Code>
I put this in a textbox
= code.test()
d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
custom code: [BC32035] Attribute specifier is not a complete statement.
Use a line continuation to apply the attribute to the following
statement.
And this is my error|||You should not be having to modify the rdl at all (which is what you are
doing).
Search Books Online for the phrase: writing custom code
Also these two links:
ms-help://MS.RSBOL80.1033/RSCREATE/htm/rcr_creating_expressions_v1_84f9.htm
ms-help://MS.RSBOL80.1033/RShowto/htm/hrs_designer_v1_1nfp.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"PAPutzback" <phillip_putzback@.insightbb.com> wrote in message
news:1128719715.084263.60890@.g43g2000cwa.googlegroups.com...
>I put this in the code window
> <Code>
> Public Function test() As String
> Dim strTest As String
> strTest = "BLAH BLAH BLAH"
> Return strTest
> End Function
> </Code>
>
> I put this in a textbox
> = code.test()
>
> d:\phfxclaims\Pending Claims Detail.rdl There is an error on line 0 of
> custom code: [BC32035] Attribute specifier is not a complete statement.
> Use a line continuation to apply the attribute to the following
> statement.
> And this is my error
>|||Thanks for the links. I finally got it to work
I had cut and pasted the code from this article
http://www.15seconds.com/issue/041110.htm and I had the tags in there
which were getting placed in the XML definition
Thanks for the quick responses

Problem with a String formula

I need to change the following string formula to be Right Justified it is currently print out as Left. Can anyone help me change it.

//firstpay e-name 40 digits

StringVar T1;
StringVar T2;
StringVar ExStr;
NumberVar L;
NumberVar X;
T1:=' ';
X:=Length(T1);
ExStr:=left({CHECK_MSTR.NAME},40);
L:=Length(ExStr);
T2:=left(T1, X-L)+ExStr;
T2;right(replicatestring(' ', 40) & left(({CHECK_MSTR.NAME},40), 40)

problem with 2005 xml datasource that is xml render of a 2000 report

Hi,
I'm trying to setup an xml datasource in ssrs 2005. The xml datasource is
an xml render from a ssrs 2000 report.
The connection string is a url which works fine when I put it into a
standalone browser (prompts me to save the xml output).
However when I try to then do a basic Query String for a dataset, I get an
error back from ssrs 2000
"The value provided for the report parameter 'EndDate' is not valid for its
type. (rsReportParameterTypeMismatch) "
My url is url encoded, and has the name value pair
"rs:ParameterLanguage=en-GB" defined
Any ideas on troubleshooting this?
Thanks
MartinTurns out I wasn't affecting the url when I thought I was.
"Martin" <x@.y.z> wrote in message
news:%23pgeok9OHHA.3268@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I'm trying to setup an xml datasource in ssrs 2005. The xml datasource is
> an xml render from a ssrs 2000 report.
> The connection string is a url which works fine when I put it into a
> standalone browser (prompts me to save the xml output).
> However when I try to then do a basic Query String for a dataset, I get an
> error back from ssrs 2000
> "The value provided for the report parameter 'EndDate' is not valid for
> its type. (rsReportParameterTypeMismatch) "
> My url is url encoded, and has the name value pair
> "rs:ParameterLanguage=en-GB" defined
> Any ideas on troubleshooting this?
> Thanks
> Martin
>

Problem with " when Importing large text file

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_ticke t","extiid","invoice","extschedid","cycle","formid ","form","formdate","standard_message","message"," extsid","source","who_printed","date_printed","Pri nted","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
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...
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> 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_ticke t","extiid","invoice","extschedid","cycle","formid ","form","formdate","standard_message","message"," extsid","source","who_printed","date_printed","Pri nted","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
|||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
"Michael C#" wrote:
[vbcol=seagreen]
> 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...
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...

>
>
|||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.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...[vbcol=seagreen]
> 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
> "Michael C#" wrote:
[vbcol=seagreen]
|||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
"Michael C#" wrote:
[vbcol=seagreen]
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
d"
>
>
|||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.
|||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.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...[vbcol=seagreen]
> 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
> "Michael C#" wrote:
ed"[vbcol=seagreen]
|||Yes, I will give that a try. Thank you very much.
"Michael C#" wrote:
[vbcol=seagreen]
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
lled"
>
>
|||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.
"Michael C#" wrote:
[vbcol=seagreen]
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
lled"
>
>
|||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.

Problem with " when Importing large text file

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, AlphaI 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...
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> 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|||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
"Michael C#" wrote:
> 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...
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> > 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
>
>|||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.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
> 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
> "Michael C#" wrote:
>> 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...
>> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
>> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
>> > 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
>>|||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
"Michael C#" wrote:
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
> > 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
> >
> > "Michael C#" wrote:
> >
> >> 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...
> >>
> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> >> > 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
> >>
> >>
> >>
>
>|||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.|||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.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
> 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
> "Michael C#" wrote:
>> 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.
>> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
>> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
>> > 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
>> >
>> > "Michael C#" wrote:
>> >
>> >> 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...
>> >>
>> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
>> >> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>|||Yes, I will give that a try. Thank you very much.
"Michael C#" wrote:
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
> > 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
> >
> > "Michael C#" wrote:
> >
> >> 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.
> >>
> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
> >> > 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
> >> >
> >> > "Michael C#" wrote:
> >> >
> >> >> 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...
> >> >>
> >> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> >> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||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.
"Michael C#" wrote:
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
> > 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
> >
> > "Michael C#" wrote:
> >
> >> 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.
> >>
> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
> >> > 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
> >> >
> >> > "Michael C#" wrote:
> >> >
> >> >> 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...
> >> >>
> >> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> >> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||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.|||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.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:EE4CE1F7-EAEE-4CB5-BA4C-65900C821E2C@.microsoft.com...
>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.
> "Michael C#" wrote:
>> 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.
>> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
>> news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
>> > 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
>> >
>> > "Michael C#" wrote:
>> >
>> >> 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.
>> >>
>> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
>> >> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
>> >> > 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
>> >> >
>> >> > "Michael C#" wrote:
>> >> >
>> >> >> 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...
>> >> >>
>> >> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
>> >> >> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
>> >> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Got it! Thank you very much and have a great day.
Alpha
"Michael C#" wrote:
> 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.
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:EE4CE1F7-EAEE-4CB5-BA4C-65900C821E2C@.microsoft.com...
> >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.
> >
> > "Michael C#" wrote:
> >
> >> 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.
> >>
> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> news:E4C5A196-AC27-4459-9359-2CF7E2667FCD@.microsoft.com...
> >> > 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
> >> >
> >> > "Michael C#" wrote:
> >> >
> >> >> 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.
> >> >>
> >> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> >> news:393A48C7-10FC-4DC2-85C0-9468FABE8A6F@.microsoft.com...
> >> >> > 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
> >> >> >
> >> >> > "Michael C#" wrote:
> >> >> >
> >> >> >> 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...
> >> >> >>
> >> >> >> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> >> >> >> news:5ACB9501-43FC-4315-95C6-0548D370A844@.microsoft.com...
> >> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>