May 23, 2005 at 5:55 pm
I am attempting to use Bulk Insert to import CSV files into an existing Table. The CSV data contains rows with a variable number of fields. I know the max fields, the field delimiter and the row terminator. I get an EOL error when the number of fields in the input is less than the table fields. Is there a way to get Bulk Insert to just load fields until it sees the row terminator (assuming there are as many or more fields defined in the table than are in the text file)?
strSQL = "BULK INSERT " & TableName & " FROM [" & txtfile & "]"
strSQL = strSQL + " WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')"
gcnCharts.Execute strSQL
I there is not way Bulk Insert can do this, is there some alternate approach to do what I need to do? This needs to be accomplished from within a VB application. I initially tried a plane INSERT INTO, but this just concatenated the text rows into the fields irrespective of table rows.
strSQL = "INSERT INTO [" & tbl & "]"
strSQL = strSQL & "SELECT * FROM " & txtfile
gcnCharts.Execute strSQL
I've tried bcp but the same problem exists. I got the DTS wizard to do what I want, but only from a command window. I find no examples of how to use DTS programmatically in VB to accomplish this task. Can anyone help here - I have no experience using DTS in this manner?
thanks - jack
May 25, 2005 at 6:38 am
I find no examples of how to use DTS programmatically in VB ....
Using the DTS Wizard, when you get to the Save, schedule and replicate package dialog, in the lower frame is a checkbox 'Save DTS Package'. The very last radiobutton selection is to save as a VB6 .bas file.
Once saved, start VB6 by double-clicking .bas file, add a reference to 'Microsoft DTSPackage Object Library' and you're ready to go.
Good Luck.
May 25, 2005 at 10:06 am
Can the process producing the file add the delimiters for the missing fields? Otherwise, if the record length is always less than 8000, the data could be loaded into a single varchar(8000) column of a work or staging table. You would need to write the query to parse the data into the final destination from this table. The bulk load plus query can still be much more performant than VB (e.g., minutes vs. hours). Maybe, a table could be created that has the columns that always will be present plus one "overflow" column large enough to hold everthing else. Once this table is loaded, T-SQL could copy the data to the final destination, only parsing the overflow column.
If you have a lot of data, consider only processing a batch of records at a time. For example, SET ROWCOUNT 10000, loop setup, WHILE (@rowcount <> 0) BEGIN query error handling SELECT @rowcount = @@ROWCOUNT END, etc. I'm assuming here that the query can detect and ignore those records already processed - maybe by either using a join with the destination table or by setting a flag in the source table. The query must not process the same set of records each iteration. Another possibility is to use an identity column primary key and loop through the key values 10K at a time via a where clause something like WHERE KeyId BETWEEN 10000 * (@LoopCounter - 1) + 1 AND 10000 * @LoopCounter.
Randy
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
May 25, 2005 at 10:45 am
Thanks for your suggestions. I've used the DTS Designer to create a solution that can be saved to a VB .bas file which can be used in a VB application. The Designer allows remapping the source/destination fields in any manner required. I don't know how to get this solution using Bulk Insert. I also don't believe you can accomplish this with the DTS Wizard. There's a good set of tutorials on how to do this at http://www.sqldts.com.
Thanks -- jack
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply