Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA
|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas

"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extend ed Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can import from xls but what about csv??

Below is the query I use to import from an xls file. What about importing
from a csv file. I change the name from ChallengeResponse.xls to
ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
Select *
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
8.0')...ChallengeResponse$
Thanks in advance,
Eric"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA|||You can use the OLEDB provider for JET, which provides support for ISAM
sources; however, you will need to specify the type by using the Extended
Properties=TEXT instead of EXCEL and then the file name as the original with
the .CSV extension.
Sincerely,
Anthony Thomas
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:eXSJT6GCFHA.2460@.TK2MSFTNGP14.phx.gbl...
"Eric Lovelace" <EricLovelace@.discussions.microsoft.com> wrote in message
news:50366645-7708-4956-9C49-48C585236E06@.microsoft.com...
> Below is the query I use to import from an xls file. What about importing
> from a csv file. I change the name from ChallengeResponse.xls to
> ChallengeResponse.csv but I get an error. Anyone know any tricks to this?
> Select *
> FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data
> Source=C:\cr\dataload\ChallengeResponse.xls;Extended Properties=Excel
> 8.0')...ChallengeResponse$
>
> Thanks in advance,
> Eric
I'm not sure about using OPENDATASOURCE for a CSV, but you can definitely
use bcp as well as DTS.
Rick Sawtell
MCT, MCSD, MCDBA

Can I use the foreach loop to obtain the sub folder names within a folder?

I have a set of folders with the following structure:

MainFolder

1999

SpreadsheetA.xls

SpreadsheetB.xls

SpreadsheetC.xls

2000

SpreadsheetA.xls

SpreadsheetB.xls

SpreadsheetC.xls

etc.

Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.

Thanks,

Sam

If the list of folders is known and finite then you can just type them into the ForEach Item Enumerator.

If the list changes then you'll need to do something a bit cleverer. Like writing your own Enumerator.

-Jamie

|||

sam2005 wrote:

I have a set of folders with the following structure:

MainFolder

1999

SpreadsheetA.xls

SpreadsheetB.xls

SpreadsheetC.xls

2000

SpreadsheetA.xls

SpreadsheetB.xls

SpreadsheetC.xls

etc.

Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.

Thanks,

Sam

Have you tried the 'traverse sub folders' option on the ForEach object (on the collection pane)