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

Thread: csv file / Table fields Do NOT match !!!

  1. #1
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29

    csv file / Table fields Do NOT match !!!

    I am working on importing a .csv file, but having problems with the csv file data fields and table fields. The csv file fields DO NOT match exactly with my table .....is there a workaround ??? How can i ensure that the right values are placed in the right columns ... How can i create a "format file" so that the all the table fields get the right values !!


    Regards
    Monika

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    1. YOu can use Openrowset to query only certain columns.

    http://www.databasejournal.com/featu...0894_3331881_2

    2. In DTS you can Map the right columns that you want

  3. #3
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    Thank you MAK for your reply .
    Well, i am not using DTS . I am creating a web based Aplication where i need to Give a Import Button on one of the Screens which will take the file name as an input and should be able to import that CSV file into my database table.

    Another issue that i am thinking would be the csv file location, as i the file supplied by the user will always be existing on his hard disk, not on the server and utilities like BULK INSERT look for the file on the server .. Pls help if you have answers to my questions.
    Also, pls tell me if there is a way to create this "format file" manually so that i am implicitly attach the rite fields to right columns .

    DTS package does all that , but i cannot use the DTS package at runtime on a website !!

    Regards
    Monika

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    DTS is the right way to do this.

    1. Create a DTS package which can accept different source files (with same format) using GLobal varible and dynamic parameter.

    2. When the User click the button for import ftp the file (internally) to your web box (rename the file to a unique filename) and then run a DTS package to import it (passing the new filename as the parameter prefic it with your webserver path)

    Yes you can execute a DTS package at run time from a website.

  5. #5
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    Hey MAK,

    Thanks for all your help , but let me tell you that i am totally new to DTS package etc and would like to know a little bit more on how to do this ...Its been a while since i am trying to dig in info to implement this.

    Can you suggest some sample code for the same?? I have been reading your articles about the DTS package. Somehow, i feel there is something more to it which i need to implement that in my application.


    Regards
    Monika

  6. #6
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    See attached DTS package.

    If you are running as structured storage file then.

    dtsrun -F"Importcsvdynamic.dts" -A"FileName"="c:\test.csv"

    If you are running as DTS stored in SQl Server then

    dtsrun -N"Importcsvdynamic" -A"FileName"="c:\test.csv" -E -SServerName
    Attached Files Attached Files

  7. #7
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    Thank you MAK.

    Though i have a package ready no w, but another question that i want to ask you is - how do i make the (csv)file available at the server ?? Or is it possible for the SQL Server to read the file from clients' Hard disk as i will otherwise have to copy the file from client's HD to the SQL SERVER !!!

    Also, while exporting the data from the table to a CSV file , ho wcan i store the file on the client's Hard Disk (as per the destination path specified by the client) !!


    Regards,
    monika

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    1. use UNC Path if you are executing the DTS package on the server. Make sure the login used in SQL Server Agent service (if you are scheduling the job in SQL Server) has access to the UNC path.

    example:

    dtsrun -F"Importcsvdynamic.dts" -A"FileName"="\\servername\sharename\test.csv"

    dtsrun -N"Importcsvdynamic" -A"FileName"="\\servername\sharename\test.csv" -E -SServerName

    2. DTS package runs on the local machine if you are executing from the client machine. In that case you can say "c:\test.csv"

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    For export use UNC path.

  10. #10
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    the statement

    dtsrun -N"Importcsvdynamic" -A"FileName"="\\servername\sharename\test.csv" -E -SServerName


    gives syntax error !!


    Pls help!!! I am so close to solving the problem , still stuck !!!


    Regards

  11. #11
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    Originally posted by MAK
    For export use UNC path.

    How can i create a UNC path for the file?
    Last edited by monikan; 06-18-2004 at 09:00 AM.

  12. #12
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Find user's computer name and store it using UNC

    SET @FileName = REPLACE('\\Usersmachinename\c$\monika_'+CONVERT(ch ar(8),GETDATE(),1)+
    '.csv','/','-')

  13. #13
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    Originally posted by MAK
    See attached DTS package.

    If you are running as structured storage file then.

    dtsrun -F"Importcsvdynamic.dts" -A"FileName"="c:\test.csv"

    If you are running as DTS stored in SQl Server then

    dtsrun -N"Importcsvdynamic" -A"FileName"="c:\test.csv" -E -SServerName

    I tried both these methods, but these statements give syntax errors !! Pls help !!!

    Regards

  14. #14
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    What errors?

  15. #15
    Join Date
    May 2004
    Location
    Florida, USA
    Posts
    29
    I get syntax errors . Also, i want to know , how can i make the web client use this as the file(.csv) needs to be present on the SQL SERVER in order for it to run the DTS package. Pls help !!



    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •