dbl.quotes in a text file data

[Marc Miller]

Hello everyone,

First I'll address this as a textfile manipulation question and if there is
no answer
there, then I'll need to ask it as a vb ADO question.

I have a text file that I'm reading into a dataset, then looping thru the
dataset and
storing the field values to vars and then doing an 'INSERT INTO' an SQL
Server table.
The text file that has records containing single quotes and double quote
within the data (not qualifiers).
Is there any way to mass change


The text file that has records containing single quotes within the data (not
qualifiers).
I solved that with :
cDesc = tb.Rows(i).Item(4).ToString.Replace("'", "''")

But then I also found records containing double quotes within the data (not
qualifiers).
So I tried (fyi - chr(22) is a double quote):
cDesc = tb.Rows(i).Item(4).ToString.Replace("'", "''")
cDesc = cDesc.Replace(Chr(22), Chr(22) & Chr(22))

But the only thing I get in the dataset is the beginning of the string: SOME
There are additional columns after this in the real data, and they get
filled with DBNULLs.

Below is my connection and my schema.ini for the text file.


Dim oConn As OleDbConnection = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
Data Source=c:\temp\;Extended
Properties=""text;HDR=YES;FMT=Delimited(;"";")

Thanks for any and all help,

Marc Miller


schema.ini
[css.csv]
ColNameHeader=TRUE
Format=Delimited(
MaxScanRows=150
Col1=Sup_Name Text Width 24
Col2=Emp_Name Text Width 24
Col3=Emp_ID Text Width 4
Col4=ScreenID Text Width 8
Col5=Desc Text Width 79
CharacterSet=OEM

 

[Guest]

I have a text file that I'm reading into a dataset, then looping thru
the dataset and
storing the field values to vars and then doing an 'INSERT INTO' an
SQL Server table.
The text file that has records containing single quotes and double
quote within the data (not qualifiers).
Is there any way to mass change

If you're going to be loading data this way... you should use SQL
Parameters - this will format the strings correctly for you.

Otherwise, take a look at:

BCP/Bulk Insert/Bulk Load
DTS
SQL Server Integration Servers

DTS and SSIS are the preferred methods of loading large data files.