schema.ini not working

T

tshad

I have been using a schema.ini file to define the columns of my .csv files
that has been working fine.

But I just tried one where it doesn't work.

I have the following schema.ini file:

***********************************************************
[CGLConversion.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char

[SGLConversion.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char
Col6=F6 Char

[tempInvoice.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char
Col6=F6 Char
Col7=F7 Char
Col8=F8 Char
********************************************************

The first 2 files work fine. But the tempInvoice.csv is giving me an error
that says it expects column 3 to be numeric????
Here is the error:
*************************************************************
ex.Message "System.FormatException: Input string was not in a correct
format.
at System.Number.ParseDouble(String s, NumberStyles style,
NumberFormatInfo info)
at System.Double.Parse(String s, NumberStyles style, IFormatProvider
provider)
at System.Convert.ToDouble(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDouble(IFormatProvider provider)
at System.Convert.ToDouble(Object value)
at System.Data.Common.DoubleStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't
store <004O> in F3 Column. Expected type is Double." String
***************************************************************

The .csv file is:
***********************************************************
"8005","C COMPANIES INC","000","GROSS WAGES","1,7.11",
"","","001","FICA-OASDI",108.37,
"","","002","FICA-MEDICARE",25.34,
"","","003","FUTA",1.87,
"","","004","SUTA",13.82,
"","","004O","OTHER STATE TAX",0.24,
"","","005","WORKERS COMPENSATION",0.00,
************************************************************

If I take out the line that has "004O" out, it works fine. But if I have
defined all the columns as Char, then why the error?

The code looks like:

***********************************************************
Dim path As String = "C:\ImportFile\"
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)

If f.Exists(pathfile) Then
Try
Dim da As New OleDb.OleDbDataAdapter("Select * from " &
"tempInvoice.csv", conn)
da.Fill(DataSetObj)
Catch ex As Exception
TextBox3Error.Text = ex.Message
Exit Sub
End Try
End If
**********************************************************

It gets to the .fill line then the Catch section.

Thanks,

Tom
 
C

Charlie Brown

I have been using a schema.ini file to define the columns of my .csv files
that has been working fine.

But I just tried one where it doesn't work.

I have the following schema.ini file:

***********************************************************
[CGLConversion.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char

[SGLConversion.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char
Col6=F6 Char

[tempInvoice.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char
Col6=F6 Char
Col7=F7 Char
Col8=F8 Char
********************************************************

The first 2 files work fine. But the tempInvoice.csv is giving me an error
that says it expects column 3 to be numeric????
Here is the error:
*************************************************************
ex.Message "System.FormatException: Input string was not in a correct
format.
at System.Number.ParseDouble(String s, NumberStyles style,
NumberFormatInfo info)
at System.Double.Parse(String s, NumberStyles style, IFormatProvider
provider)
at System.Convert.ToDouble(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDouble(IFormatProvider provider)
at System.Convert.ToDouble(Object value)
at System.Data.Common.DoubleStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't
store <004O> in F3 Column. Expected type is Double." String
***************************************************************

The .csv file is:
***********************************************************
"8005","C COMPANIES INC","000","GROSS WAGES","1,7.11",
"","","001","FICA-OASDI",108.37,
"","","002","FICA-MEDICARE",25.34,
"","","003","FUTA",1.87,
"","","004","SUTA",13.82,
"","","004O","OTHER STATE TAX",0.24,
"","","005","WORKERS COMPENSATION",0.00,
************************************************************

If I take out the line that has "004O" out, it works fine. But if I have
defined all the columns as Char, then why the error?

The code looks like:

***********************************************************
Dim path As String = "C:\ImportFile\"
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)

If f.Exists(pathfile) Then
Try
Dim da As New OleDb.OleDbDataAdapter("Select * from " &
"tempInvoice.csv", conn)
da.Fill(DataSetObj)
Catch ex As Exception
TextBox3Error.Text = ex.Message
Exit Sub
End Try
End If
**********************************************************

It gets to the .fill line then the Catch section.

Thanks,

Tom
Click to expand...

Tom, as a test, try putting a character into this line and running
again. change 000 to 000F

"8005","C COMPANIES INC","000F","GROSS WAGES","1,7.11",

It may be looking at the first datatype to get entered in that column
and setting the columns datatype that way. It's been a while since i
switched from csv to xml so its just a guess.
 
T

tshad

Charlie Brown said:
I have been using a schema.ini file to define the columns of my .csv
files
that has been working fine.

But I just tried one where it doesn't work.

I have the following schema.ini file:

***********************************************************
[CGLConversion.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char

[SGLConversion.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char
Col6=F6 Char

[tempInvoice.csv]
Format=CSVDelimited
ColNameHeader=False
Col1=F1 Char
Col2=F2 Char
Col3=F3 Char
Col4=F4 Char
Col5=F5 Char
Col6=F6 Char
Col7=F7 Char
Col8=F8 Char
********************************************************

The first 2 files work fine. But the tempInvoice.csv is giving me an
error
that says it expects column 3 to be numeric????
Here is the error:
*************************************************************
ex.Message "System.FormatException: Input string was not in a correct
format.
at System.Number.ParseDouble(String s, NumberStyles style,
NumberFormatInfo info)
at System.Double.Parse(String s, NumberStyles style, IFormatProvider
provider)
at System.Convert.ToDouble(String value, IFormatProvider provider)
at System.String.System.IConvertible.ToDouble(IFormatProvider
provider)
at System.Convert.ToDouble(Object value)
at System.Data.Common.DoubleStorage.Set(Int32 record, Object value)
at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't
store <004O> in F3 Column. Expected type is Double." String
***************************************************************

The .csv file is:
***********************************************************
"8005","C COMPANIES INC","000","GROSS WAGES","1,7.11",
"","","001","FICA-OASDI",108.37,
"","","002","FICA-MEDICARE",25.34,
"","","003","FUTA",1.87,
"","","004","SUTA",13.82,
"","","004O","OTHER STATE TAX",0.24,
"","","005","WORKERS COMPENSATION",0.00,
************************************************************

If I take out the line that has "004O" out, it works fine. But if I have
defined all the columns as Char, then why the error?

The code looks like:

***********************************************************
Dim path As String = "C:\ImportFile\"
Dim ConStr As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
Dim conn As New OleDb.OleDbConnection(ConStr)

If f.Exists(pathfile) Then
Try
Dim da As New OleDb.OleDbDataAdapter("Select * from " &
"tempInvoice.csv", conn)
da.Fill(DataSetObj)
Catch ex As Exception
TextBox3Error.Text = ex.Message
Exit Sub
End Try
End If
**********************************************************

It gets to the .fill line then the Catch section.

Thanks,

Tom
Click to expand...

Tom, as a test, try putting a character into this line and running
again. change 000 to 000F

"8005","C COMPANIES INC","000F","GROSS WAGES","1,7.11",

It may be looking at the first datatype to get entered in that column
and setting the columns datatype that way. It's been a while since i
switched from csv to xml so its just a guess.
Click to expand...

I assumed that was the case, but that is not supposed to be the case if you
are using a schema.ini file where you define the columns.

I think I may have figured out the problem. In my example, I am filling a
DataSet. If I change the code to fill a DataTable and then add that table
to the DataSet, that seems to solve the problem.

It seems to be because I already have 2 tables in the DataSet. If I have no
tables in the DataSet already, then it works fine. But if there is a table
there, what seems to be happening is that it is doing an insert into the
table of DataSet.Tables(0) which has no name (except for the "Table1" given
it when I filled the first table).

I can also do the same line that fills the DataSet if I give it a name:

da.Fill(DataSetObj,"Invoice")

Thanks,

Tom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Missing leading zeros on parsed CSV File - Schema.ini not working for me 0
schema.ini (again) sure Access is not reading it. 1
How can be done? Can be done? 1
Function HELP! 1
Importing text with TransferText. It can't see my Schema.ini file 1
Help Import / export txt file in VB 1
DateTime format is not supported in ADO.NET 7
dbl.quotes in a text file data 1

Top