Click here to Skip to main content
13,087,797 members (82,315 online)
Click here to Skip to main content
Add your own
alternative version


26 bookmarked
Posted 12 Mar 2007

Schema.ini override and bulk upload using SQLXML

, 15 Mar 2007
Rate this:
Please Sign up or sign in to vote.
Schema.ini override and bulk upload using SQLXML


This article shows how to override Schema.ini file on the fly to facilitate bulk uploads and also shows how to use SQLXML feature to do bulk upload into SqlServer 2000 table.


Ever had issues in your web application with excel bulk uploads where the jet engine was not treating the column values as text?

In this article we will see how we can overcome this issue and also see a sample code for uploading bulk data into a SQL Server 2000 table, using the SQLXML features (though there are hundred different ways to do this same thing!).

Using the code

I get this huge file once every two weeks which I need to upload to SQL Server table. The columns have values with leading zeros.

The file with sample data looks something like this:

// sample Provider_file.txt
provider ID,License
0000018, 079376
0000019, 076185
0000021, 034733
0000024, 032921
0000025, 034551
0000043, 079331
0000107, 035911
0000272, 075555
0000295, 074591
0000331, 057518
0000342, 074673
0000371, 079491

Now, I could make use of the OPENROWSET functionality or DTS to upload this file to the target table. But, I have this little file upload interface in my web application that I use to upload the file. This would also enable my clients to go online and upload data whenever they wish.

What I do in the code is save this file with a date time stamp on it to make sure that I do not overwrite the previous file.

<INPUT id=txtFileToUpload type=file name=txtFileToUpload 

runat="server" /> 
<INPUT id=btnUpLoad onclick=doProcess(); 

       type=button value=Process name=btnUpLoad 

       Runat="server" />     

Then read the file contents into a data set.

Dim cnn As OleDb.OleDbConnection

Dim dsCSV As New DataSet("Providers") cnn = 
    New OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data

Source=" + filePath + ";Extended Properties=Text")
Dim mySelect As String = String.Empty

mySelect = "Select * from [" & fileName & "]"

Dim da As New OleDb.OleDbDataAdapter(mySelect, cnn)

Now, while selecting the data into a dataset I observed that the column having data with leading zeros was not treated like a text column type, despite changing the column type to Text in excel and then converting the file to a CSV file. This obviously means that when data is read into a dataset, the leading zeros would vanish. Then I figured out that I needed to put the Schema.ini file with column data types into the folder containing the data file, in order for the jet engine know the data types of the data columns specifically. Otherwise Jet engine would interpret the column as numeric based on the first few rows of data.

Schema.ini override

ColNameHeader = True
Format = CSVDelimited
Col1=IfProv Char Width 20
Col2=License Char Width 20

The first line of the Schema.ini file contains the target file name including the file extension on which the schema needs to be applied. In the second line, mentions whether the column headers are present in the target file or not. The third line contains the format of the target file. Fourth line contains the character set. After the fourth line, you can start declaring the column names and their data types. Note that the Schema.ini file should be present in the same folder as the target data file you are trying to create.

In my application, since the upload happens biweekly, and since I needed the previously uploaded file to be untouched, I had to come up with a way to change the file name (first line in the schema file) in the Schema.ini file. So I decided to create the Schema.ini file on the fly every time the upload happens by feeding in the first line. And the code for that would look something like this:

fileStream = New FileStream(fileName, 

writer = New StreamWriter(fileStream)
writer.WriteLine("[" & UploadfileName & "]")
writer.WriteLine("ColNameHeader = True")
writer.WriteLine("Format = CSVDelimited")
writer.WriteLine("Col1=IfProv Char Width 20")
writer.WriteLine("Col2=License Char Width 20")

Once the data is read to a dataset, rename table and the column names (if need be) before getting the XML output.

If Not dsCSV Is Nothing Then
   dsCSV.Tables(0).TableName = "Provider"
dsCSV.Tables(0).Columns(0).ColumnName = "IfProv"
   dsCSV.Tables(0).Columns(1).ColumnName = "License"
End If

And the XML data would look something like this after renaming the table names and columns:


Send the XML data to Stored Procedure

Pass on the XML to the stored proc to process.

SqlHelper.ExecuteNonQuery(ConnectionString, _
        CommandType.StoredProcedure, _
        New SqlParameter() { _
        New SqlParameter("@PROVDATA", xmlProviderData), 

returnParameter _

Uploading data using SQLXML

Create a table with two columns as shown below.



The stored procedure to upload the XML data in to the table using SQLXML feature would be something like this:


        TBL_PROVIDER (IfProv,License)
              (@IDOC, '/Providers/Provider')  WITH (IfProv
                    VARCHAR(10)   'IfProv', License
        VARCHAR(10) 'License') WHERE IfProv IS NOT NULL AND License IS NOT _ 

Wrapping it all up

I have always enjoyed using SQLXML features of SQL Server and I hope that the Schema.ini override along with this SQLXML features in this article help a lot of people by resolving their day-to-day bulk upload issues.

Disclaimer: The source code contains Microsoft's SQLHelper helper class for making database calls which I use in most of my applications (I'm sure many readers use this!) and it is not written by me. I included it in the source code zip file just to make sure that the code works after downloading it.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Aravind Rajagopal K
Software Developer (Senior) UMASS Medical School
United States United States
A Certified .NET MCSD (Microsoft Certified Solution Developer)that specializes in Microsoft technologies architecting/designing/developing applications using .NET (C# or VB), ASP.NET, SQL Server and BizTalk.
I have contributed as a designer/developer for benchmarked products in the finance industry benchmarked for best transaction engine design and products that offer end to end solution to research based content. Currently working with UMASS Medical School at Worcester MA.

Hobbies include music,reading books and playing cricket.

You may also be interested in...


Comments and Discussions

GeneralRewrite intro Pin
Dave Kreskowiak6-Jan-10 2:01
mvpDave Kreskowiak6-Jan-10 2:01 
GeneralRe: Rewrite intro Pin
Aravind Rajagopal K6-Jan-10 9:25
memberAravind Rajagopal K6-Jan-10 9:25 
GeneralCreate .csv file programmatically using C# Pin
Jan Palmer11-Sep-07 19:23
memberJan Palmer11-Sep-07 19:23 
QuestionBulk insert not recognizing end of file Pin
searching for solution.....6-May-13 0:32
membersearching for solution.....6-May-13 0:32 
GeneralUTF Pin
astaykov17-Aug-07 21:30
memberastaykov17-Aug-07 21:30 
QuestionProblem with characterset Pin
klamu20-Jun-07 23:45
memberklamu20-Jun-07 23:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 15 Mar 2007
Article Copyright 2007 by Aravind Rajagopal K
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid