locked
Importing csv files to SQL Server Express RRS feed

  • Question

  • Hello,

    I receive  4 .csv file downloads periodically (3 times per day) via email from our corporate database.  I  open each file in Excel, save as excel files, import to Access, replace the previous tables...run action queries, generate reports combined with production data from CSRs and supervisors. ALL  DONE MANUALLY!!!

     

    Here's where I am now:

    -I've recently switched over to SQL Server Express.

    -Used SSMA to bring tables over from previous Access database.

    -Exercised the option to LINK these tables to the original Access database.(I'll explain why in a moment)

    -Created ADPs for front end data entry use.

    -Imported  old Access database forms into the new ADPs previously used in Access.

    -Connected to the new Server Express.

    -I've eliminated my concurrent user problems by doing this.

    HOWEVER, I am still bound to using old Access/Jet database to generate reports based on periodic downloads from corporate .csv files.

     

    Here's the question:

    What is the best way to import the csv files being sent to me via email into SQL Server Express? I've tried DTS. Seems to me you can't save AND actually use the packages later since it's the Express edition..... Importing manually 4 files, 3 times per day is a very tedious option I'd like to avoid. Any ideas?

    Oh, by the way. Corporate has told me they would be willing to post the files to an FTP site instead of emailing the files. That's about as much help as I'm going to get from them. Can SQL Server Express be set up to run stored procedures (triggers) on a hot folder?

    Thanks for your help.

     

    David

    Thursday, November 22, 2007 2:15 PM

Answers

  • hi David,

     Darkenmoor wrote:

    Hello,

    I receive  4 .csv file downloads periodically (3 times per day) via email from our corporate database.  I  open each file in Excel, save as excel files, import to Access, replace the previous tables...run action queries, generate reports combined with production data from CSRs and supervisors. ALL  DONE MANUALLY!!!

     

    ..

    Here's the question:

    What is the best way to import the csv files being sent to me via email into SQL Server Express? I've tried DTS. Seems to me you can't save AND actually use the packages later since it's the Express edition..... Importing manually 4 files, 3 times per day is a very tedious option I'd like to avoid. Any ideas?

    Oh, by the way. Corporate has told me they would be willing to post the files to an FTP site instead of emailing the files. That's about as much help as I'm going to get from them. Can SQL Server Express be set up to run stored procedures (triggers) on a hot folder?

     

    actually, regarding the "part" of importing csv files, that can be done very simply, using, say, both BCP or Linked Server..

    with B(ulk) C(opy) P(rogram), you just bulk load into the destination db.table the data from the text file, where, via Linked Server, you can query the file and obviously you can INSERT .. SELECT, getting the very same result.. so please see Books Online for BCP and Linked Server...

    the "real problem" is the current "transfer".. if you get that data by e-mail, SQLExpress can non access it.. you have to unpack it to a folder... then SQLEXpress, depending on the NTFS permissions accorded to the account running it, can eventually access that data files..

    and, of course, another problem is the eventual "scheduling" architecture, if you like to rely on such a pattern, as SQLExpress does not provide the SQL Server Agent to schedule such a sort of tasks..

    but you can schedule via the operating system such a task to execute an "import.cmd" file where you execute, via BCP.exe or SqlCMD.exe the appropriate statement(s) and, eventually, cleaning the folder and the like...

    of course you can even write a little program to "watch" for "changes" in a specific folder, so that every time a file is created in that folder you execute the task to import the data.. but this requires the program to run 24x7, so the best shot probably is to implement it as a service..

    regards

    • Marked as answer by Darkenmoor Sunday, February 1, 2009 5:12 PM
    Saturday, November 24, 2007 11:57 AM

All replies

  • hi David,

     Darkenmoor wrote:

    Hello,

    I receive  4 .csv file downloads periodically (3 times per day) via email from our corporate database.  I  open each file in Excel, save as excel files, import to Access, replace the previous tables...run action queries, generate reports combined with production data from CSRs and supervisors. ALL  DONE MANUALLY!!!

     

    ..

    Here's the question:

    What is the best way to import the csv files being sent to me via email into SQL Server Express? I've tried DTS. Seems to me you can't save AND actually use the packages later since it's the Express edition..... Importing manually 4 files, 3 times per day is a very tedious option I'd like to avoid. Any ideas?

    Oh, by the way. Corporate has told me they would be willing to post the files to an FTP site instead of emailing the files. That's about as much help as I'm going to get from them. Can SQL Server Express be set up to run stored procedures (triggers) on a hot folder?

     

    actually, regarding the "part" of importing csv files, that can be done very simply, using, say, both BCP or Linked Server..

    with B(ulk) C(opy) P(rogram), you just bulk load into the destination db.table the data from the text file, where, via Linked Server, you can query the file and obviously you can INSERT .. SELECT, getting the very same result.. so please see Books Online for BCP and Linked Server...

    the "real problem" is the current "transfer".. if you get that data by e-mail, SQLExpress can non access it.. you have to unpack it to a folder... then SQLEXpress, depending on the NTFS permissions accorded to the account running it, can eventually access that data files..

    and, of course, another problem is the eventual "scheduling" architecture, if you like to rely on such a pattern, as SQLExpress does not provide the SQL Server Agent to schedule such a sort of tasks..

    but you can schedule via the operating system such a task to execute an "import.cmd" file where you execute, via BCP.exe or SqlCMD.exe the appropriate statement(s) and, eventually, cleaning the folder and the like...

    of course you can even write a little program to "watch" for "changes" in a specific folder, so that every time a file is created in that folder you execute the task to import the data.. but this requires the program to run 24x7, so the best shot probably is to implement it as a service..

    regards

    • Marked as answer by Darkenmoor Sunday, February 1, 2009 5:12 PM
    Saturday, November 24, 2007 11:57 AM