#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2007
    Posts
    13
    Rep Power
    0

    Append data from xls/csv to sql server online with asp


    Hi, How do i import data from .xls or .csv to my existing table on sql server ?
    Let's say i have 4 Fields in newdata.xls :: name , ph, address, city.
    And in my SQL Server Table - i have five fields in customer table :id (int, autonumber),name , ph, address, city
    Now need to specify newdata.xls and it imports all existing data from newdata.xls and imports it to SQL server.

    So copy all data from newdata.xls and append into customer table.


    or if you know from csv then that's ok too, as i conver xls to csv , so if u know how to append from csv that will do tooo.

    Any help appreciated.
    thanks
  2. #2
  3. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    858
    do you want to do that in ASP or directly in SQL server?
    You can use the DATA Import/Export Wizard if you want to do
    it directly SQL Server.
    Look! Its a ShemZilla


  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2007
    Posts
    13
    Rep Power
    0
    I want to use .asp to read from .xls on server then append into SQL. bec on my hosting plan i don't have access to the MS SQL server, and i need an option to upload xls and then append.asp should read .xls line by line and insert data into SQL server as new records
  6. #4
  7. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    858
    ok, this example will get a file that is comma seperated, split
    it into the seperate fields and build a insert into statement
    and execute it,hope this helps
    Code:
    Dim F1, FSO
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set F1 = FSO.OpenTextFile(sPath & "\import.csv",1)
    
    Do While F1.AtEndOfStream = false
    		
    		dataArr = split(F1.ReadLine,",")
    		
    		sqlInsert = "INSERT INTO theTable (field1, field2) VALUES ('" & dataArr(0) & "','" & dataArr(1) & "')"
    		
    		conn.Execute sqlInsert
    	end if
    Loop
    	F1.Close()
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Discoverer (100 - 499 posts)

    Join Date
    Jul 2007
    Posts
    135
    Rep Power
    18
    Originally Posted by nofriends
    ok, this example will get a file that is comma seperated, split
    it into the seperate fields and build a insert into statement
    and execute it,hope this helps
    Code:
    Dim F1, FSO
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set F1 = FSO.OpenTextFile(sPath & "\import.csv",1)
    
    Do While F1.AtEndOfStream = false
    		
    		dataArr = split(F1.ReadLine,",")
    		
    		sqlInsert = "INSERT INTO theTable (field1, field2) VALUES ('" & dataArr(0) & "','" & dataArr(1) & "')"
    		
    		conn.Execute sqlInsert
    	end if
    Loop
    	F1.Close()
    The problem with this is if I have a coma in my field.

    What I usually do is use the text driver:

    conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=" & sPath & ";"

    Then you can select field like you would in a normal database table and loop though the record and insert them into the sql database.

    The select would look like:

    SELECT myfields FROM import.csv
  10. #6
  11. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2007
    Posts
    13
    Rep Power
    0
    Thanks .
    One problem .......

    In SQL server a field named "price" is :: datatype of "Money".
    In CSV the values for thio field are like "169995.8" or "350000" or "271995.8" .
    & while converting this csv into SQL i am getting error opn that price field.
    How do i fix this error, pls advise.


    Microsoft OLE DB Provider for SQL Server (0x80040E07)
    Disallowed implicit conversion from data type varchar to data type money, table 'buyer.dbo.Client', column 'Price'. Use the CONVERT function to run this query.

    thanks for comments.
  12. #7
  13. No Profile Picture
    Grumpier Old Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2003
    Posts
    10,143
    Rep Power
    186
    You'll have to adjust your loop to add ' around the column value only for text columns, not for numeric valued columns.
    ======
    Doug G
    ======
    I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain
  14. #8
  15. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2007
    Posts
    13
    Rep Power
    0
    Originally Posted by Doug G
    You'll have to adjust your loop to add ' around the column value only for text columns, not for numeric valued columns.
    Thanks Doug,

    So how do add the numeric value from XLS to DB ? i must add the price from XLS how do i do that pls.
  16. #9
  17. No Profile Picture
    Grumpier Old Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Sep 2003
    Posts
    10,143
    Rep Power
    186
    Thanks Doug,

    So how do add the numeric value from XLS to DB ? i must add the price from XLS how do i do that pls.
    You don't have your code add the ' around the numeric value as you build your sql string. Your code must be aware of what columns are textual and what ones are numeric and build the sql appropriately for each column.

Similar Threads

  1. Execute SQL
    By erickh in forum ASP Development
    Replies: 8
    Last Post: March 29th, 2006, 03:44 AM
  2. Difference between MS Access and SQL Server
    By norml318 in forum Microsoft Access Help
    Replies: 1
    Last Post: February 17th, 2006, 05:32 PM
  3. Need help with asp Sql problem
    By bleutiger in forum Programming Help
    Replies: 1
    Last Post: January 22nd, 2005, 09:44 PM
  4. Need help with ASP SQL Problem
    By bleutiger in forum ASP Development
    Replies: 1
    Last Post: January 20th, 2005, 11:13 AM
  5. Web serverístatistical analysis
    By cso in forum ASP Development
    Replies: 0
    Last Post: April 19th, 2003, 11:35 AM

IMN logo majestic logo threadwatch logo seochat tools logo