SQL BulkCopy using IDataReader - how can you cast the fields?
-
I'm looking for a method/example/pseudo-code of importing a CSV file into a SQL Server table with several requirements: 1) I do not know the quantity of columns, column header nor data type of each column prior to the program running. Along with the header fields, I have another file which describes the type and size of each column (like integer, decimal or string) and I can already create the destination table based on this input. 2) Because I don't know the format of the input table before running the program (i.e. the input table changes every time), I can't create a class specifically designed to describe the contents of each record from the CSV file. 3) I already have a method working where a DataTable gets fully populated and the SQLBulkCopy variable uses the .WriteToServer() method to insert all of the records. HOWEVER, there's a problem when the source file gets too large (over 500mb or so) and the result is an OutOfMemory exception. 4) The AdHocGeek has a partial solution which works with strings only but doesn't address integers, decimals, dates, et.al. The problem is that I can't see a way to use an IDataReader into a complex (i.e. many field types) table on the fly. I've added the .ColumnMappings() method to determine the name of the columns with which to map the input columns into the destination SQL Server table. When the application is run however, it gets an error indicating "The given value of type String from the data source cannot be converted to type decimal of the specified target column". I can't determine why the DataTable works and the DataReader doesn't and can't find any examples to prove otherwise. Any ideas or examples would be appreciated.
-
I'm looking for a method/example/pseudo-code of importing a CSV file into a SQL Server table with several requirements: 1) I do not know the quantity of columns, column header nor data type of each column prior to the program running. Along with the header fields, I have another file which describes the type and size of each column (like integer, decimal or string) and I can already create the destination table based on this input. 2) Because I don't know the format of the input table before running the program (i.e. the input table changes every time), I can't create a class specifically designed to describe the contents of each record from the CSV file. 3) I already have a method working where a DataTable gets fully populated and the SQLBulkCopy variable uses the .WriteToServer() method to insert all of the records. HOWEVER, there's a problem when the source file gets too large (over 500mb or so) and the result is an OutOfMemory exception. 4) The AdHocGeek has a partial solution which works with strings only but doesn't address integers, decimals, dates, et.al. The problem is that I can't see a way to use an IDataReader into a complex (i.e. many field types) table on the fly. I've added the .ColumnMappings() method to determine the name of the columns with which to map the input columns into the destination SQL Server table. When the application is run however, it gets an error indicating "The given value of type String from the data source cannot be converted to type decimal of the specified target column". I can't determine why the DataTable works and the DataReader doesn't and can't find any examples to prove otherwise. Any ideas or examples would be appreciated.
Well I would say you are screwed, only because you are trying to transform the data in your load. IMHO transforms are the biggest time waster ever inflicted on the load process. I would split the operations to a load process and then a transform. Caveat I am not addressing the size issue as I have never had the problem (and 500mb is very small beer in our environment). I would do the following. Read in the data file and get the header record. Create a staging table in sql server exactly matching the column headers - every column to be varchar or nvarchar if needed Convert the csv file to a datatable (This article [^]may help). Use BulkCopy to load the data (everything is varchar so it WILL load) Use a stored proc to do the transforms from the staging table to the target table. If you have to use a dictionary file then you are going to have to take that into account and it will be a challenge (probably a crap load of dynamic sql). Drop the staging table
Never underestimate the power of human stupidity RAH
-
Well I would say you are screwed, only because you are trying to transform the data in your load. IMHO transforms are the biggest time waster ever inflicted on the load process. I would split the operations to a load process and then a transform. Caveat I am not addressing the size issue as I have never had the problem (and 500mb is very small beer in our environment). I would do the following. Read in the data file and get the header record. Create a staging table in sql server exactly matching the column headers - every column to be varchar or nvarchar if needed Convert the csv file to a datatable (This article [^]may help). Use BulkCopy to load the data (everything is varchar so it WILL load) Use a stored proc to do the transforms from the staging table to the target table. If you have to use a dictionary file then you are going to have to take that into account and it will be a challenge (probably a crap load of dynamic sql). Drop the staging table
Never underestimate the power of human stupidity RAH
That's a very interesting idea that should work. I would need to create a temporary SP based on the fields and data types for the conversion. It also pushes the field validation to the SP but that shouldn't be a problem. Thanks for the feedback.
-
I'm looking for a method/example/pseudo-code of importing a CSV file into a SQL Server table with several requirements: 1) I do not know the quantity of columns, column header nor data type of each column prior to the program running. Along with the header fields, I have another file which describes the type and size of each column (like integer, decimal or string) and I can already create the destination table based on this input. 2) Because I don't know the format of the input table before running the program (i.e. the input table changes every time), I can't create a class specifically designed to describe the contents of each record from the CSV file. 3) I already have a method working where a DataTable gets fully populated and the SQLBulkCopy variable uses the .WriteToServer() method to insert all of the records. HOWEVER, there's a problem when the source file gets too large (over 500mb or so) and the result is an OutOfMemory exception. 4) The AdHocGeek has a partial solution which works with strings only but doesn't address integers, decimals, dates, et.al. The problem is that I can't see a way to use an IDataReader into a complex (i.e. many field types) table on the fly. I've added the .ColumnMappings() method to determine the name of the columns with which to map the input columns into the destination SQL Server table. When the application is run however, it gets an error indicating "The given value of type String from the data source cannot be converted to type decimal of the specified target column". I can't determine why the DataTable works and the DataReader doesn't and can't find any examples to prove otherwise. Any ideas or examples would be appreciated.
Allan Watkins wrote:
a DataTable gets fully populated
That sounds like a bad idea; there's no need to have more than one record in memory at a time.
Allan Watkins wrote:
cannot be converted
Sounds like you may need to specify a Parse routine. Take a look at the values that are causing the trouble -- it's possible that they are empty.