Post reply

Inserting data from XLS & CSV to SQL DB

  • April 23, 2009 at 4:43 am

    #95473

    I have several csv & xls files full of data.

    I need to insert the data into relavite tables:

    XLS --> Table 1

    XLS --> Table 2

    CSV --> Table 3

    CSV --> Table 4

    I dont need anything special done with the data, just a standard import.

    Whats the best way to do this?

    Thanks

  • April 23, 2009 at 6:22 am

    #982803

    Easiest way is to use the Import / Export wizard in SSMS

  • April 23, 2009 at 7:19 am

    #982855

    Thanks,

    I used the Import wizard... looks like it has worked...well 50/50

    Its created a table called 'File-Name-Of-My-ExcelFile$'

    And in the same format as above i.e. including the - & $

    When I do a select from that table it comes back with incorrect syntax near 'File-Name_Of-My-ExcelFile$'

    But if I right click on the table and select 'Open Table' i can see the data but its grayed out...

  • April 23, 2009 at 7:24 am

    #982862

    You should probably rename the table to something a bit more useful in the import process.

    But too select from your current table enclose the table name in []

    select * from [File-Name-Of-My-ExcelFile$]

  • April 23, 2009 at 8:25 am

    #982950

    Thanks, the [] worked.

    I cant seem to rename the CREATE TABLE bit in my SSIS...

    Once I run the first set to create the table with new name.."tblTransaction".

    I run the insert part of the SSIS.

    When I then run a select against my new table "tblTransaction"

    i get the same error, Invalid object name 'dbo.tbltransaction'.....

  • April 23, 2009 at 8:35 am

    #982957

    glad it worked.

    check in SSMS that it has created the table in the correct database, if you see this new table in the explorer then you will be able to select from it..

  • April 23, 2009 at 8:39 am

    #982964

    Its is very strange!!!!!

    I can see, edit, open the table but i cant run a select against it!

    :crazy:

  • April 23, 2009 at 8:57 am

    #982986

    A Little Help Please (4/23/2009)

    Its is very strange!!!!!

    I can see, edit, open the table but i cant run a select against it!

    :crazy:

    Are you sure you are running the select against the right database...

    trying using...

    USE YourDatabase

    at the top of the select statement.

    Though this seems obvious it sometimes traps me..

  • April 23, 2009 at 9:06 am

    #983003

    Ok I think i found out why, it was using spaces between the names.... I duno... either way its select is working now.

    Thanks for your help!!!!!!!!!!!!!!!!!!!!!!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply