Using SQL Bulk Load to Upload Fixed Width Text Files

Test

Introduction The class needs a table on a SQL Server somewhere to import into, a connection to that database, a file to load, the names of the fields to load the data into and the possessions in the file to load. I set this up so that everything can come from application settings, and put a quick WPF file browser front end together to demonstrate how to use the class.
Background Everywhere I have worked, it has been important to be able to load fixed width files from mainframe reports that come from data sources that can't or at-least aren't provided by web services, ODBC or other sources. Every time there is a new version of Visual Studio, this gets a little easier to write. This is my latest class to do the import.
Using the Code The simplest way to use my BulkLoad class is to make setting strings for what needs to be loaded and pass them to the BulkLoadFromStrings function. In this example I have set up everything as app settings.
This is what the config file looks like:

1. <configuration>
   
2.   <configsections>
   
3.     <sectiongroup name=""userSettings"" publickeytoken="b77a5c561934e089""
   
4.           culture="neutral," version="2.0.0.0,"
   
5.           type=""System.Configuration.UserSettingsGroup,">
   
6.       <section name=""Bulk_Import.Properties.Settings""
   
7.           publickeytoken="b77a5c561934e089"" culture="neutral," version="2.0.0.0,"
   
8.           type=""System.Configuration.ClientSettingsSection,"
   
9.           requirepermission=""false"" allowexedefinition=""MachineToLocalUser"">
   
10.     </sectiongroup>
    
11.   </configsections>
    
12.   <usersettings>
    
13.     <bulk_import.properties.settings>
    
14.       <setting name=""TableName"" serializeas=""String"">
    
15.         <value>drcAlden</value>
    
16.       </setting>
    
17.       <setting name=""FieldNamesCSV"" serializeas=""String"">
    
18.         <value>CLSNumber,YrPeriod,Vendor,Department,BoxCode,StoreNumber,StoreName,
    
19.          TypeScan,UPC   , Manufac,Description,Qty     ,Base   ,Credit  ,GSNumber</value>
    
20.       </setting>
    
21.       <setting name=""DefaultDir"" serializeas=""String"">
    
22.         <value>C:\</value>
    
23.       </setting>
    
24.       <setting name=""DefaultFile"" serializeas=""String"">
    
25.         <value>FixedWidthFile.txt</value>
    
26.       </setting>
    
27.       <setting name=""FormatCSV"" serializeas=""String"">
    
28.         <value>4-8     ,10-14    ,15-22  ,23-26    ,27-36   ,38-48      ,48-77   ,
    
29.            78-80    ,80-94  ,94-103 ,103-123   ,124-133 ,133-146 ,147-160,161-170</value>
    
30.       </setting>
    
31.     </bulk_import.properties.settings>
    
32.   </usersettings>
    
33.   <connectionstrings>
    
34.     <add name=""DamageReclaimEntities"" security="True;MultipleActiveResultSets=False"""
    
35.         catalog="Database;Integrated" source="Server;Initial"
    
36.         string=""Data" providername=""System.Data.EntityClient""
    
37.         connectionstring=
    
38.         ""metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/
    
39.         Model1.msl;provider=System.Data.SqlClient;provider">
    
40.     <add name=""Bulk_Import.Properties.Settings.DamageReclaimConnectionString""
    
41.         security="True"" catalog="Database;Integrated"
    
42.         source="DBServer;Initial" providername=""System.Data.SqlClient""
    
43.         connectionstring=""Data">
    
44.   </connectionstrings>
    
45. </configuration>

复制代码

The app settings were just added by right clicking on the project file though, so, don't worry about having to retype a bunch of perfectly valid XML unless you are into all that.
This BulkLoadFromStrings function basically calls the class's parse routines on these strings and uses them to Load the file.

1. /// <summary>
   
2. /// Does bulk load based on settings for file and table to be loaded.
   
3. /// </summary>
   
4. /// <param name=""TableName"">Name of table to be loaded.</param>
   
5. /// <param name=""FieldNamesCSV"">Comma Separated list of field names </param>
   
6. /// <param name=""FieldPositionCSV"">Comma separated list of field positions
   
7. /// separated by dashes</param>
   
8. /// <param name=""FileNameAndPath"">Path to fixed width file with data to be
   
9. /// loaded.</param>
   
10. /// <param name=""SQLConnectionString"">Connection string for DB to load
    
11. /// data in.</param>
    
12. /// <returns></returns>
    
13. public int BulkLoadFromStrings (   string TableName_in
    
14.                                 ,   string FieldNamesCSV_in
    
15.                                 ,   string FieldPositionCSV_in
    
16.                                 ,   string FileNameAndPath_in
    
17.                                 ,   string SQLConnectionString_in
    
18.                                 )
    
19. {
    
20.     int iRecordsLoadedOut = 0;
    
21.     BulkLoadFields blfldsToLoad;
    
22.     BulkLoadTable bltblToLoad;
    
23.     SqlConnection con = null;
    
24.     Stream strmToLoad = null;
    
25.     DataTable dtToLoad = null;
    
26.     int iRecordsPreLoaded = 0;
    
27.     con = MakeConnection(SQLConnectionString_in);
    
28.     blfldsToLoad = new BulkLoadFields(FieldNamesCSV_in, FieldPositionCSV_in);
    
29.     bltblToLoad = MakeBulkLoadTable(TableName_in, con, blfldsToLoad.FieldNames);
    
30.     strmToLoad = FileToStream(FileNameAndPath_in);
    
31.     dtToLoad = bltblToLoad.Table;
    
32.     iRecordsPreLoaded = LoadTable(ref dtToLoad, blfldsToLoad.FieldPositions,
    
33.         strmToLoad);
    
34.     strmToLoad.Close();
    
35.     strmToLoad = null;
    
36.     bltblToLoad.Table = dtToLoad;
    
37.     iRecordsLoadedOut = DoBulkLoad(bltblToLoad, con);
    
38.     AddExportDate(FileNameAndPath_in, con.Database.ToString(),
    
39.         con.DataSource.ToString());
    
40.     return iRecordsLoadedOut;
    
41. }

复制代码

There are 2 structures that the class uses to pass things around. The BulkLoadFields structure is where the collection of field positions and names are stored, and the BulkLoadTable is simply a datatable with these SQLBulkInsert maps set up for each of its columns. These maps turned out to be extremely important to getting the Bulk insert to work.
Because the SQLBulkCopy object seemed to be so sensitive to datatype and data size differences, the best solution seemed to be to create a table to load based on an existing table. I saw other examples wherein data readers did something similar, and it seems like there should be a better way (or at least one less specific to SQL Server) to get a table definition, but selecting the description from SQL's system tables has worked well for me.


By Alden John Snow

http://www.codeproject.com/KB/WPF/SQLBulkLoad_Fixed_Wdh_TXT.aspx

Test

1. /// <summary>
   
2. /// Creates BulkLoad table from a table definition from SQL system
   
3. /// objects and the list of column names
   
4. /// </summary>
   
5. /// <param name=""TableName"">Name of table to be loaded or copied from</param>
   
6. /// <param name=""con"">Database to get table def from</param>
   
7. /// <param name=""aFilesColumns"">Columns to define.</param>
   
8. /// <returns>BulkLoad table from a table definition from SQL system
   
9. /// objects</returns>
   
10. public BulkLoadTable MakeBulkLoadTable  (   string TableName
    
11.                                     ,   System.Data.SqlClient.SqlConnection con
    
12.                                     ,   string[] aFilesColumns
    
13.                                     )
    
14. {
    
15.     BulkLoadTable bltOut;
    
16.     bltOut = new BulkLoadTable();
    
17.     DataTable dtOut = null;
    
18.     SqlBulkCopyColumnMappingCollection sbcMapsOut = null;
    
19.     System.Data.SqlClient.SqlBulkCopy sbc = null;
    
20.     SortedList slFields = null;
    
21.     string[] aFilesFormat = null;
    
22.     string strFieldDFormatCSV = string.Empty;
    
23.     string strColumnName = string.Empty;
    
24.     string strColumnType = string.Empty;
    
25.     string strFromFile = string.Empty;
    
26.     string strFile = string.Empty;
    
27.     SqlDataReader sdr = null;
    
28.     SqlDbType FieldType = SqlDbType.Variant;
    
29.     SqlCommand sqlCmd = null;
    
30.     int iCharacterMaximumLength = 0;
    
31.     StringBuilder sbInsertFields = null;
    
32.     StringBuilder sbSelectFields = null;
    
33.     //Get a data reader to peek at the column types that we will be
    
34.     //adding rows to.
    
35.     sqlCmd = new SqlCommand(" select Column_NAme, Data_Type, ISNULL(
    
36.         Character_Maximum_Length, 0) Max " +
    
37.                             " from information_schema.columns " +
    
38.                             " where table_name like '" + TableName + "'", con);
    
39.     sqlCmd.CommandType = CommandType.Text;
    
40.     if (sqlCmd != null)
    
41.     {
    
42.         aFilesFormat = strFieldDFormatCSV.Split(char.Parse(","));
    
43.         if (aFilesFormat != null)
    
44.         {
    
45.             if (con.State != ConnectionState.Open)
    
46.             {
    
47.                 con.Open();
    
48.             }
    
49.             sdr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
    
50.             if (sdr != null)
    
51.             {
    
52.                 sbSelectFields = new StringBuilder();
    
53.                 foreach (string strColName in aFilesColumns)
    
54.                 {
    
55.                     if (sbSelectFields.Length!=0)
    
56.                     {
    
57.                         sbSelectFields.Append(",");
    
58.                     }
    
59.                     sbSelectFields.Append("[");
    
60.                     sbSelectFields.Append(strColName.Trim());
    
61.                     sbSelectFields.Append("]");
    
62.                 }
    
63.                 SqlDataAdapter sdaForWrite = new SqlDataAdapter(
    
64.                     "Select " + sbSelectFields.ToString() + " from " +
    
65.                     TableName + " where 1= -1", con);
    
66.                 sbc = new System.Data.SqlClient.SqlBulkCopy(con);
    
67.                 sbcMapsOut = sbc.ColumnMappings;
    
68.                 sbInsertFields = new StringBuilder("INSERT INTO ");
    
69.                 sbInsertFields.Append(TableName);
    
70.                 sbInsertFields.Append(" (");
    
71.                 sbInsertFields.Append(sbSelectFields.ToString());
    
72.                 sbInsertFields.Append(") VALUES (");
    
73.                 sbInsertFields.Append(sbSelectFields.Replace(
    
74.                     "[", "@").Replace("]", ""));
    
75.                 sbInsertFields.Append(")");
    
76.                 sdaForWrite.InsertCommand = new SqlCommand();
    
77.                 sdaForWrite.InsertCommand.CommandText = sbInsertFields.ToString();
    
78.                 sbInsertFields = null;
    
79.                 slFields = new SortedList();
    
80.                 if (sdr.HasRows)
    
81.                 {
    
82.                     if (sdr.Read())
    
83.                     {
    
84.                         do
    
85.                         {
    
86.                             strColumnName = sdr["Column_NAme"].ToString().Trim();
    
87.                             if (HasColumn(aFilesColumns, strColumnName))
    
88.                             {
    
89.                                 strColumnType = sdr["Data_Type"].ToString();
    
90.                                 if (sdr["Max"] != null)
    
91.                                 {
    
92.                                     iCharacterMaximumLength = (int)sdr["Max"];
    
93.                                 }
    
94.                                 else
    
95.                                 {
    
96.                                     iCharacterMaximumLength = 0;
    
97.                                 }
    
98.                                  switch (strColumnType)
    
99.                                 {
    
100.                                     case "bigint":
     
101.                                         FieldType = SqlDbType.BigInt;
     
102.                                         break;
     
103.                                     case "int":
     
104.                                         FieldType = SqlDbType.Int;
     
105.                                         break;
     
106.                                     case "smallint":
     
107.                                         FieldType = SqlDbType.SmallInt;
     
108.                                         break;
     
109.                                     case "tinyint":
     
110.                                         FieldType = SqlDbType.TinyInt;
     
111.                                         break;
     
112.                                     case "bit":
     
113.                                         FieldType = SqlDbType.Bit;
     
114.                                         break;
     
115.                                     case "decimal":
     
116.                                         FieldType = SqlDbType.Decimal;
     
117.                                         break;
     
118.                                     case "money":
     
119.                                         FieldType = SqlDbType.Money;
     
120.                                         break;
     
121.                                     case "smallmoney":
     
122.                                         FieldType = SqlDbType.SmallMoney;
     
123.                                         break;
     
124.                                     case "nchar":
     
125.                                         FieldType = SqlDbType.NChar;
     
126.                                         break;
     
127.                                     case "ntext":
     
128.                                         FieldType = SqlDbType.NText;
     
129.                                         break;
     
130.                                     case "nvarchar":
     
131.                                         FieldType = SqlDbType.NVarChar;
     
132.                                         break;
     
133.                                     case "datetime":
     
134.                                         FieldType = SqlDbType.DateTime;
     
135.                                         break;
     
136.                                     case "smalldatetime":
     
137.                                         FieldType = SqlDbType.SmallDateTime;
     
138.                                         break;
     
139.                                     case "char":
     
140.                                         FieldType = SqlDbType.Char;
     
141.                                         break;
     
142.                                     case "varchar":
     
143.                                         FieldType = SqlDbType.VarChar;
     
144.                                         break;
     
145.                                 }
     
146.                                 sdaForWrite.InsertCommand.Parameters.Add(
     
147.                                     "@" + strColumnName, FieldType,
     
148.                                     iCharacterMaximumLength, strColumnName);
     
149.                                 sbcMapsOut.Add(new SqlBulkCopyColumnMapping(
     
150.                                     strColumnName, strColumnName));
     
151.                             }
     
152.                         }
     
153.                         while (sdr.Read());
     
154.                     }
     
155.                 }
     
156.                 //Done with the data reader now that we have the field types.
     
157.                 sdr.Close();
     
158.                 DataSet ds = new DataSet();
     
159.                 sdaForWrite.Fill(ds, TableName);
     
160.                 dtOut = ds.Tables[TableName];
     
161.                 sdr = null;
     
162.                 bltOut = new BulkLoadTable(dtOut, sbcMapsOut);
     
163.             }
     
164.         }
     
165.     }
     
166.     return bltOut;
     
167. }

复制代码

Test

With a table and fields defined, the next step is to load the data from the flat file. Having already loaded the file into a stream, I basically split it up by the end of line character and then loop through the resulting array of strings to parse out the fields from each line.

1. /// <summary>
   
2. /// Loads a datatable by parsing a fixed width file.
   
3. /// </summary>
   
4. /// <param name=""TableToLoad"">Data table to load</param>
   
5. /// <param name=""FieldPositions_in"">Field positions that the file will
   
6. /// be parsed with</param>
   
7. /// <param name=""FiletoLoad"">Stream to read data from.</param>
   
8. /// <returns></returns>
   
9. public int LoadTable    (   ref DataTable TableToLoad
   
10.                         ,   SortedList<string,> FieldPositions_in
    
11.                         ,   Stream FiletoLoad
    
12.                         )
    
13. {
    
14.     int iRecordsLoadedOut = 0;
    
15.     string strSQLCon = string.Empty;
    
16.     string strTableName = string.Empty;
    
17.     DataRow dr = null;
    
18.     string[] astrFile = null;
    
19.     string strFromFile = string.Empty;
    
20.     string strFld = string.Empty;
    
21.     string strFile = string.Empty;
    
22.     int iStart = 0;
    
23.     int iEnd = 0;
    
24.     int iFileLen = 0;
    
25.     StreamReader sr = null;
    
26.     iFileLen = (int)FiletoLoad.Length;
    
27.     sr = new StreamReader(FiletoLoad,System.Text.Encoding.UTF8,false,iFileLen);
    
28.     if (FiletoLoad != null)
    
29.     {
    
30.         if (sr != null)
    
31.         {
    
32.             //FiletoLoad.Seek(1, SeekOrigin.Begin);
    
33.             strFile = sr.ReadToEnd();
    
34.             sr.Close();
    
35.             sr = null;
    
36.             if (strFile.Contains("\r"))
    
37.             {
    
38.                 strFile.Replace("\n", "");
    
39.                 astrFile = strFile.Split(Char.Parse("\r"));
    
40.             }
    
41.             if (astrFile != null)
    
42.             {
    
43.                 foreach (string strLine in astrFile)
    
44.                 {
    
45.                     if (strLine != "\n")
    
46.                     {
    
47.                         dr = TableToLoad.NewRow();
    
48.                         foreach (DataColumn col in TableToLoad.Columns)
    
49.                         {
    
50.                             strFld = col.ColumnName;
    
51.                             dr[strFld.Trim()] = DBNull.Value;
    
52.                             iStart = FieldPositions_in[strFld].Start;
    
53.                             iEnd = FieldPositions_in[strFld].End;
    
54.                             if ((iStart <= strLine.Replace("\n", "").Length) &
    
55.                                 (iStart + (iEnd - iStart) <=
    
56.                                 strLine.Replace("\n", "").Length))
    
57.                             {
    
58.                                 strFromFile = strLine.Replace("\n",
    
59.                                     "").Substring(iStart, iEnd - iStart).Trim();
    
60.                                 if (strFromFile.Length > 0)
    
61.                                 {
    
62.                                     if (TableToLoad.Columns[strFld.Trim()] != null)
    
63.                                     {
    
64.                                         if (strFromFile != string.Empty)
    
65.                                         {
    
66.                                             strFromFile = strFromFile.Replace(
    
67.                                                 "'", "''");
    
68.                                             if (strFromFile != ".")
    
69.                                             {
    
70.                                                 dr[strFld.Trim()] =
    
71.                                                     strFromFile.Trim().ToString();
    
72.                                             }
    
73.                                         }
    
74.                                     }
    
75.                                 }
    
76.                             }
    
77.                          }
    
78.                         TableToLoad.Rows.Add(dr);
    
79.                     }
    
80.                 }
    
81.             }
    
82.         }
    
83.     }
    
84.     //TableToLoad.AcceptChanges();
    
85.     return iRecordsLoadedOut;
    
86. }

复制代码

The datatable that is loaded by LoadTable is then passed back into the BulkLoadTable structure so that its columns can stay married up to the corresponding SqlBulkCopyColumnMappingCollection, and after WriteToServer is called, the records appear in the new table.

1. /// <summary>
   
2. /// Bulk loads a BulkLoadTable with SQL Server's BCP components.
   
3. /// </summary>
   
4. /// <param name=""bct_in"">Table to load</param>
   
5. /// <param name=""Con_in"">Open Connection to use.</param>
   
6. /// <returns></returns>
   
7. public int DoBulkLoad(BulkLoadTable bct_in, SqlConnection Con_in)
   
8. {
   
9.     int iRecordsLoadeOut = 0;
   
10.     SqlBulkCopy sbc = null;
    
11.     SqlBulkCopyColumnMappingCollection sbcMaps = null;
    
12.     DataTable dtToLoad =null;
    
13.     string strTableName = null;
    
14.     dtToLoad = bct_in.Table;
    
15.     strTableName = dtToLoad.TableName;
    
16.     sbc = new SqlBulkCopy(Con_in);
    
17.     sbcMaps = bct_in.Maps;
    
18.     foreach (SqlBulkCopyColumnMapping map in sbcMaps)
    
19.     {
    
20.         sbc.ColumnMappings.Add(map);
    
21.     }
    
22.     sbc.BatchSize = dtToLoad.Rows.Count;
    
23.     sbc.DestinationTableName = strTableName;
    
24.     sbc.BulkCopyTimeout = 0;
    
25.     if (Con_in.State != ConnectionState.Open)
    
26.     {
    
27.         Con_in.Open();
    
28.     }
    
29.     sbc.WriteToServer(dtToLoad, DataRowState.Added);
    
30.     if (Con_in.State != ConnectionState.Closed)
    
31.     {
    
32.         Con_in.Close();
    
33.     }
    
34.     iRecordsLoadeOut = dtToLoad.Rows.Count;
    
35.     return iRecordsLoadeOut;
    
36. }

复制代码

Points of InterestI broke the load function out of the rest of this process so that if there are some special validations and/or transformations that need to happen on the individual fields, this can be done right after the data is loaded into the ADO data table, but, before it is sent to the server. Also, at some point, it might make sense to override the load or make table functions for special tables or other file formats.
Though this example walks through parsing all the settings from strings, there is no reason another app might want to load the field possessions structure differently, or use an FTP sockets stream to get a file instead of reading it from a disk.
Hopefully in a future ADO release, this gets easier and tables will get better at knowing their own schema information.