Click here to Skip to main content
12,451,205 members (52,397 online)
   

Database

 
QuestionImporting of Huge fixed width text file in sql server table Pin
ibrahim sharief27-Dec-12 9:24
memberibrahim sharief27-Dec-12 9:24 
AnswerRe: Importing of Huge fixed width text file in sql server table Pin
PIEBALDconsult27-Dec-12 14:08
memberPIEBALDconsult27-Dec-12 14:08 
AnswerRe: Importing of Huge fixed width text file in sql server table Pin
Deepak Kr130-Dec-12 20:29
memberDeepak Kr130-Dec-12 20:29 
AnswerRe: Importing of Huge fixed width text file in sql server table Pin
jschell31-Dec-12 9:14
memberjschell31-Dec-12 9:14 
Questionshow Sum(Balance) as ob on Rows Header and add next rows balance in ob Pin
hadeed147227-Dec-12 6:52
memberhadeed147227-Dec-12 6:52 
AnswerRe: show Sum(Balance) as ob on Rows Header and add next rows balance in ob Pin
djj5527-Dec-12 7:53
memberdjj5527-Dec-12 7:53 
GeneralRe: show Sum(Balance) as ob on Rows Header and add next rows balance in ob Pin
hadeed147227-Dec-12 8:03
memberhadeed147227-Dec-12 8:03 
QuestionSQL BulkCopy using IDataReader - how can you cast the fields? Pin
Allan Watkins26-Dec-12 19:12
memberAllan Watkins26-Dec-12 19:12 
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.
AnswerRe: SQL BulkCopy using IDataReader - how can you cast the fields? Pin
Mycroft Holmes26-Dec-12 20:32
memberMycroft Holmes26-Dec-12 20:32 
GeneralRe: SQL BulkCopy using IDataReader - how can you cast the fields? Pin
AllanW1111127-Dec-12 6:02
memberAllanW1111127-Dec-12 6:02 
AnswerRe: SQL BulkCopy using IDataReader - how can you cast the fields? Pin
PIEBALDconsult27-Dec-12 8:48
memberPIEBALDconsult27-Dec-12 8:48 
QuestionSubquery returns more than 1 row Pin
Jassim Rahma24-Dec-12 1:02
memberJassim Rahma24-Dec-12 1:02 
AnswerRe: Subquery returns more than 1 row Pin
Eddy Vluggen24-Dec-12 1:29
memberEddy Vluggen24-Dec-12 1:29 
AnswerRe: Subquery returns more than 1 row Pin
MaulikDusara24-Dec-12 21:44
memberMaulikDusara24-Dec-12 21:44 
GeneralRe: Subquery returns more than 1 row Pin
Eddy Vluggen25-Dec-12 2:41
memberEddy Vluggen25-Dec-12 2:41 
GeneralRe: Subquery returns more than 1 row Pin
Mycroft Holmes26-Dec-12 11:17
memberMycroft Holmes26-Dec-12 11:17 
GeneralRe: Subquery returns more than 1 row Pin
MaulikDusara26-Dec-12 18:45
memberMaulikDusara26-Dec-12 18:45 
GeneralRe: Subquery returns more than 1 row Pin
Eddy Vluggen27-Dec-12 4:24
memberEddy Vluggen27-Dec-12 4:24 
QuestionOn the Naming of Columns for Lookup tables. Pin
Brady Kelly17-Dec-12 22:44
memberBrady Kelly17-Dec-12 22:44 
AnswerRe: On the Naming of Columns for Lookup tables. Pin
Mycroft Holmes17-Dec-12 23:19
memberMycroft Holmes17-Dec-12 23:19 
GeneralRe: On the Naming of Columns for Lookup tables. Pin
Brady Kelly17-Dec-12 23:26
memberBrady Kelly17-Dec-12 23:26 
GeneralRe: On the Naming of Columns for Lookup tables. Pin
Mycroft Holmes18-Dec-12 1:11
memberMycroft Holmes18-Dec-12 1:11 
GeneralRe: On the Naming of Columns for Lookup tables. Pin
Brady Kelly18-Dec-12 1:15
memberBrady Kelly18-Dec-12 1:15 
AnswerRe: On the Naming of Columns for Lookup tables. Pin
PIEBALDconsult18-Dec-12 4:05
memberPIEBALDconsult18-Dec-12 4:05 
GeneralRe: On the Naming of Columns for Lookup tables. Pin
Brady Kelly18-Dec-12 4:12
memberBrady Kelly18-Dec-12 4:12 

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.


Advertise | Privacy | Mobile
Web01 | 2.8.160826.1 | Last Updated 27 Aug 2016
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid