Bulk Insert to a database


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Bulk Insert to a database

  1. #1
    Join Date
    Apr 2009
    Posts
    31

    Bulk Insert to a database

    I have a web page that I have a bulk insert to a SQL database. The file uploads and inserts correctly but I'm not getting all of my sample data to insert. It only uploads the first 3 lines.

    Here is the sample data:

    ,512 206 1293,rob stevens,doctor alexander,20100501 12:30,,
    ,512 555 1212,jason reed,doctor smith,20100505 1:00,,
    ,512 444 1111,sean jones,doctor paul,20100601 4:00,,
    ,512 111 1212,zach cochran,doctor stevens,20100605 3:15,,

    What's also odd is that the last field in the table is a "results" field that's supposed to be NULL but when the last line of the 3 lines of data that are being inserted into the database, the last result is not showing NULL but a box character. Is this because I don't have a crlf at the end of line 4?

    Thank you

    Doug

  2. #2
    Join Date
    Nov 2005
    Posts
    27
    Quote Originally Posted by dougancil View Post
    I have a web page that I have a bulk insert to a SQL database. The file uploads and inserts correctly but I'm not getting all of my sample data to insert. It only uploads the first 3 lines.

    Here is the sample data:

    ,512 206 1293,rob stevens,doctor alexander,20100501 12:30,,
    ,512 555 1212,jason reed,doctor smith,20100505 1:00,,
    ,512 444 1111,sean jones,doctor paul,20100601 4:00,,
    ,512 111 1212,zach cochran,doctor stevens,20100605 3:15,,

    What's also odd is that the last field in the table is a "results" field that's supposed to be NULL but when the last line of the 3 lines of data that are being inserted into the database, the last result is not showing NULL but a box character. Is this because I don't have a crlf at the end of line 4?

    Thank you

    Doug
    Hi,

    I think you're on the right track. If you don't have access to the script that is reading data from your file, the easiest way to find out what's going on is to try several files, with different lines, both with and without cr/lf on the last line.

  3. #3
    Join Date
    Apr 2009
    Posts
    31
    Victor,

    Thank you for that information, but I have another development that I was reminded of yesterday.

    I have the following sql command in my asp.net page:

    Dim sqlQueryText As String = _
    "BULK INSERT dialerresults " + _
    "FROM '" & SavePath & "' " + _
    "WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )"

    What I'll be doing now (with this new development) is to be adding another date table in the data, so the data will now read:

    ,512 206 1293,rob stevens,doctor alexander,20100501 12:30, 20100501 12:30
    ,

    What I need is from the two fields with time, the first one I just want to read the date and the second field all I need is the time. I'm using SQL 2000 and I'm sure that I'll have to use cast but have no idea how to add that to my asp.net page to make it work correctly. Can you (or anyone) offer some advice on the best way to accomplish that?

    Thank you

    Doug

  4. #4
    Join Date
    Oct 2008
    Posts
    142

    split() in dotnet or datepart() in tsql

    hi there,

    if you have this line:

    ,512 206 1293,rob stevens,doctor alexander,20100501 12:30, 20100501 12:30
    ,

    please, first notice that looks like there is a carriage return "12:30", so that it might get confused your "bulk insert" command.

    if you have this string for dotnet then you could use string.split() function with comma as a delimiter, which will give you a string array. looks like from this string array elements with index 4 and 5 would be datetime fields. with datetime.parse() method you can convert this strings to a datetime variables (or cdate() in vb.net). once you have these strings as datetime variables, you can get year, month, day, hour, minute, second from datetime.year, datetime.month,..., datetime.second properties of these datetime variables.

    if you have this sample string as a row in a table, you can access the tuple you need and then with datepart() tsql function you can access year, month, day, hour, minute, second,...

    if you dont have this string as a row in sql server 2000, you need a split function to make this work quick and efficient. i am sending a link of a split function for sql server 2000. if you had sql server 2005, you could enable clr for this sql server 2005 machine, and have a dotnet assembly provide this split function. i am sending also a link about this dotnet assembly with clr integrated string functions.

    for sql server 2000 goto
    http://www.google.com/search?hl=en&q...=&oq=&gs_rfai=

    for sql server 2005 goto
    http://www.google.com/search?hl=en&q...=&oq=&gs_rfai=

    i hope this sheds light to help do your work,

    tonci korsano "what you think doesn't matter! what matters is evidence..."

  5. #5
    Join Date
    Apr 2009
    Posts
    31
    Tonci,

    Sorry there is a carriage return at the end of that string, I just didn't put it in the post. So I'm reading over the datepart and it seems that all of this is written for GETDATE but as the bulk insert is going to be static information that is supplied by a text, what I need to know is how to truncate the date and time from either one of these columns:

    20100501 12:30, 20100501 12:30

    So what I'd like to have is from the first column,

    May 1st, 2010
    and the second column

    12:30 PM

    So the data would read in the database

    ,512 206 1293,rob stevens,doctor alexander,May 1st, 2010, 12:30 PM,,

  6. #6
    Join Date
    Oct 2008
    Posts
    142

    convert tsql function

    hi there,

    if 20100501 12:30 is varchar once it makes it to your database, in sql server you can use convert to make 20100501 12:30 to datetime, and once it is datetime, you can use datepart().

    for example:

    declare @myDate varchar(32);
    declare @myRealDate datetime;
    declare @myMonth int;
    declare @myHour int;

    set @myDate = '20100501 12:30';
    set @myRealDate = convert(datetime, @myDate, 101);
    set @myMonth = datepart(month, @myRealDate);
    set @myHour = datepart(hour, @myRealDate);

    print cast(@myMonth as varchar);
    print cast(@myHour as varchar);

    and so on... this tsql code i am sending is all verified and prints accurate results. thus, you can copy and paste this code in ssms if you have sql server.

    once you have all the parts of your date and time, you can construct your date and time strings and stored them as varchar columns in your table.
    i guess you are doing this breakdown in t-sql because you could also do it in dotnet.

    best regards,

    tonci korsano "a supercomputer is to a computer, what superman is to a man. wake up and smell power!"

  7. #7
    Join Date
    Apr 2009
    Posts
    31
    Tonci,

    I'd actually rather do it in asp.net. Here's the code that I have written for my aspx.vb page:

    Code:
    Imports System.IO
    Imports System.Data
    Imports System.Data.SqlClient
    Partial Class _Default
        Inherits System.Web.UI.Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        End Sub
        Protected Sub Submit1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit1.Click
            Dim SaveLocation = "\\MSBWEB3\wwwroot\Webfile1\Data\upload.txt"
            If UploadFile(SaveLocation) Then
                'the file was uploaded: now try saving it to the database
                SaveToDatabase(SaveLocation)
            End If
        End Sub
        Private Function UploadFile(ByVal SavePath As String) As Boolean
            Dim fileWasUploaded As Boolean = False 'indicates whether or not the file was uploaded
    
            'Checking if the file upload control contains a file
            If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
                Try
                    'checking if it was .txt file BEFORE UPLOADING IT!
                    'You used to upload it first...but the file could be a virus
                    If File1.FileName.EndsWith(".txt") = False Then
                        'The file is not the expected type...do not upload it
                        'just post the validation message
                        message.Text = "The file you submitted is not a .txt file, please select a .txt file to upload."
                    Else
                        'The file is a .txt file
                        'checking to see if the file exists already
                        'If it does exist Deleting the existing one so that the new one can be created
                        If IO.File.Exists(SavePath) Then
                            IO.File.Delete(SavePath)
                        End If
    
                        'Now upload the file (save it to your server)
                        File1.PostedFile.SaveAs(SavePath)
    
                        'After saving it check to see if it exists
                        If File.Exists(SavePath) Then
                            'Upload was sucessful
                            message.Text = "Thank you for your submission"
                            fileWasUploaded = True
                        Else
                            'the file was not saved
                            message.Text = "Unable to save the file"
                        End If
                    End If
    
                Catch Exc As Exception
                    'We encountered a problem
                    message.Text = Exc.Message + " " + Exc.StackTrace
                End Try
            Else
                'No file was selected for uploading
                message.Text = "Please select a file to upload"
            End If
            Return fileWasUploaded
        End Function
    
        Private Sub SaveToDatabase(ByVal SavePath As String)
            Try
                Dim sqlQueryText As String = _
                  "BULK INSERT dialerresults " + _
                  "FROM '" & SavePath & "' " + _
                  "WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )"
    
    
                ' and bulk import the data:   
                'If ConfigurationManager.ConnectionStrings("Dialerresults") IsNot Nothing Then
                'Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
                Dim connection As String = "data source=10.2.1.40;initial catalog=IVRDialer;uid=xxxxx;password=xxxxx;"
    
                'I removed the DataTable declaration because you're not using it.
                Using con As New SqlConnection(connection)
                    con.Open()
                    ' execute the bulk import   
                    Using cmd As New SqlCommand(sqlQueryText, con)
    
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
    
                'Else
                'message.Text="ConfigurationManager.ConnectionStrings('Dialerresults') is Nothing!"
                'End If
            Catch ex As Exception
                message.Text = ex.Message
    
            End Try
        End Sub
    
    End Class
    So if you show me how to do that in asp.net. I'd be a lot happier than running it through SQL.

  8. #8
    Join Date
    Oct 2008
    Posts
    142

    you need using System.IO; for this

    somewhere in your asp.net page and before calling your bulk insert command

    using (TextReader reader = File.OpenText("\\MSBWEB3\wwwroot\Webfile1\Data\upload.txt"))
    {
    string text = reader.ReadToEnd();
    }

    string wholeFile[];
    string inputLine[];
    string tempLine[];
    string resultFile = string.empty;

    wholeFile = text.split("\r");

    for (int i = 0; i < wholeFile.Length; i++)
    {
    inputLine = wholeFile[i].split(",");
    tempLine = inputLine[4].split(" ");
    resultFile += inputLine[0] + "," + inputLine[1] + "," + inputLine[2] + "," + inputLine[3] + "," + tempLine[0] + "," + tempLine[1] + "\r";
    }

    // now, check the contents of resultFile to verify they are ok

    // if output is right

    StringWriter sw = new StringWriter(<your new file with its path>);
    sw.write(resultFile);
    sw.close();

    // now, use your new file in your bulk insert command
    // i guess that's it, but obviously you need to implement
    // i know your answer is along these lines of code

    tonci korsano "a supercomputer is to a computer, what superman is to a man. wake up and smell power!"

  9. #9
    Join Date
    Apr 2009
    Posts
    31
    tonci,

    That may prove to be a bit difficult because on the page itself here's all I have:

    Code:
    <&#37;@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
    
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title>Upload Page</title>
        <style type="text/css">
            #form1
            {
                height: 189px;
                width: 919px;
            }
            #File1
            {
                top: 66px;
                }
            #Submit1
            {
                top: 103px;
                left: 591px;
            }
            #TextArea1
            {
                z-index: 1;
                left: 303px;
                top: 45px;
                position: absolute;
            }
        </style>
    <script language="javascript" type="text/javascript">
    // <!CDATA[
    
    function Submit1_onclick() {
    
    }
    
    // ]]>
    </script>
    </head>
    <body>
         <form id="form1" enctype="multipart/form-data" runat="server">
          <asp:Panel ID="UploadControlsSection" runat="server" 
              
              
              style="position: absolute; left: 563px; top: 85px; height: 118px; width: 242px;">
              <asp:FileUpload ID="File1" runat="server" />
              <br />
              <asp:Button ID="Submit1" runat="server" onclick="Submit1_Click" 
                  style="height: 30px; margin-left:60px;" Text="Upload" />
              <br />
              <asp:Label ID="message" runat="server"></asp:Label>
              <br />
         </asp:Panel>
       </form>
    </body>
    </html>
    So as you can see ... it's really pretty basic and since between the submit1_click event and the file getting bulk inserted, it's pretty instantaneous.

  10. #10
    Join Date
    Oct 2008
    Posts
    142
    sorry, that mistake is jargon of dotnet in the country where i am working. i really meant the code-behind page where you call bulk insert.
    something important as well. those array declarations are wrong.
    instead of:

    string[] inputLine;

    it should be:

    string[] inputLine = new string[];

    which goes as well for the other string array declarations.

    best regards,

    tonci korsano

  11. #11
    Join Date
    Apr 2009
    Posts
    31
    So tonci if I understand you correctly,

    string wholeFile[];
    string inputLine[];
    string tempLine[];

    should be

    string wholeFile[]=newstring[];
    string inputLine[]=newstring[];
    string tempLine[]=newstring[];

    Correct?

  12. #12
    Join Date
    Oct 2008
    Posts
    142
    you are missing a space. they should be:

    string wholeFile[] = new string[];
    string inputLine[ ]= new string[];
    string tempLine[] = new string[];

    best regards,

    tonci korsano

  13. #13
    Join Date
    Oct 2008
    Posts
    142
    again there is a typo:

    string wholeFile[] = new string[];
    string inputLine[] = new string[];
    string tempLine[] = new string[];

    best regards,

    tonci korsano.

  14. #14
    Join Date
    Apr 2009
    Posts
    31
    tonci,

    So I assume that this field is going to be filled in [] in all of those parameters correct?

  15. #15
    Join Date
    Apr 2009
    Posts
    31
    tonci,

    What declaration string would I use to add this to my codebehind?

    Here's my aspx.vb page:

    Code:
    Imports System.IO
    Imports System.Data
    Imports System.Data.SqlClient
    Partial Class _Default
        Inherits System.Web.UI.Page
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        End Sub
        Protected Sub Submit1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
            Dim SaveLocation = "\\MSBWEB3\wwwroot\Webfile1\Data\upload.txt"
            If UploadFile(SaveLocation) Then
                'the file was uploaded: now try saving it to the database
                SaveToDatabase(SaveLocation)
            End If
        End Sub
        Private Function UploadFile(ByVal SavePath As String) As Boolean
            Dim fileWasUploaded As Boolean = False 'indicates whether or not the file was uploaded
    
            'Checking if the file upload control contains a file
            If Not File1.PostedFile Is Nothing And File1.PostedFile.ContentLength > 0 Then
                Try
                    'checking if it was .txt file BEFORE UPLOADING IT!
                    'You used to upload it first...but the file could be a virus
                    If File1.FileName.EndsWith(".txt") = False Then
                        'The file is not the expected type...do not upload it
                        'just post the validation message
                        message.Text = "The file you submitted is not a .txt file, please select a .txt file to upload."
                    Else
                        'The file is a .txt file
                        'checking to see if the file exists already
                        'If it does exist Deleting the existing one so that the new one can be created
                        If IO.File.Exists(SavePath) Then
                            IO.File.Delete(SavePath)
                        End If
    
                        'Now upload the file (save it to your server)
                        File1.PostedFile.SaveAs(SavePath)
    
                        'After saving it check to see if it exists
                        If File.Exists(SavePath) Then
                            'Upload was sucessful
                            message.Text = "Thank you for your submission"
                            fileWasUploaded = True
                        Else
                            'the file was not saved
                            message.Text = "Unable to save the file"
                        End If
                    End If
    
                Catch Exc As Exception
                    'We encountered a problem
                    message.Text = Exc.Message + " " + Exc.StackTrace
                End Try
            Else
                'No file was selected for uploading
                message.Text = "Please select a file to upload"
            End If
            Return fileWasUploaded
        End Function
    
        Private Sub SaveToDatabase(ByVal SavePath As String)
            Try
                Dim sqlQueryText As String = _
                  "BULK INSERT dialerresults " + _
                  "FROM '" & SavePath & "' " + _
                  "WITH ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n' )"
    
    
                ' and bulk import the data:   
                'If ConfigurationManager.ConnectionStrings("Dialerresults") IsNot Nothing Then
                'Dim connection As String = ConfigurationManager.ConnectionStrings("Dialerresults").ConnectionString
                Dim connection As String = "data source=10.2.1.40;initial catalog=IVRDialer;uid=xxxxx;password=xxxxxx;"
    
                'I removed the DataTable declaration because you're not using it.
                Using con As New SqlConnection(connection)
                    con.Open()
                    ' execute the bulk import   
                    Using cmd As New SqlCommand(sqlQueryText, con)
    
                        cmd.ExecuteNonQuery()
    
                    End Using
                End Using
    
                'Else
                'message.Text="ConfigurationManager.ConnectionStrings('Dialerresults') is Nothing!"
                'End If
            Catch ex As Exception
                message.Text = "Please contact MSB Customer Service at 512-467-5200."
    
            End Try
        End Sub
    
    End Class

Similar Threads

  1. Database not properly created
    By Arjuna in forum Database
    Replies: 2
    Last Post: 07-25-2007, 03:22 AM
  2. Need help with Bulk Insert/ CSV file
    By Andrew Cushen in forum Database
    Replies: 9
    Last Post: 09-27-2006, 11:19 PM
  3. SQL 7 Bulk Insert Permissions
    By Royster in forum Database
    Replies: 6
    Last Post: 05-11-2005, 10:54 AM
  4. bcp and BULK INSERT
    By Sync in forum Database
    Replies: 1
    Last Post: 02-21-2005, 04:13 AM
  5. BULK INSERT generates Error 21
    By John Banks in forum VB Classic
    Replies: 0
    Last Post: 04-30-2000, 04:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center