Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSE S#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
dominique deboise's Profile: http://www.mswebservertalk.com/member.php?userid=2231
View this thread: http://www.mswebservertalk.com/t-1870556725
Showing posts with label varying. Show all posts
Showing posts with label varying. Show all posts
Friday, March 9, 2012
dts : how to process a varying list of csv files ?
Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file within
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
Source="e:\tst.csv";Extended
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you are
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can't
find a good help on the web
any one can help ?
Thanks in advance
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominique
dominique deboise
---
dominique deboise's Profile: http://www.msmcse.ms/member.php?userid=2231
View this thread: http://www.msmcse.ms/t-1870556725
dts : how to process a varying list of csv files ?
Hi all,
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file withi
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Dat
Source="e:\tst.csv";Extende
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you ar
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can'
find a good help on the web
any one can help ?
Thanks in advance
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672Have a look at
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"dominique deboise" <dominique.deboise.1r1e0t@.no-mx.msusenet.com> wrote in
message news:dominique.deboise.1r1e0t@.no-mx.msusenet.com...
> Hi all,
> I need to upload multiple csv files into one dts.
> 1)
> Is there a simple standard way to retrieve the list of csv file within
> the dts ?
> 2)
> to process the csv files (various file name) I tried to use
> opendatasource (as I did for excel files) :
> select * from
> OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
> Source="e:\tst.csv";Extended
> properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
>
> Unfortunatly, I does not work , the message is :
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
> Make sure that the path name is spelled correctly and that you are
> connected to the server on which the file resides.]
>
> my path is correct, it might be a syntax & parameter error but I can't
> find a good help on the web
> any one can help ?
>
> Thanks in advance
>
> Dominique
>
> --
> dominique deboise
> ---
> dominique deboise's Profile:
> http://www.msusenet.com/member.php?userid=2231
> View this thread: http://www.msusenet.com/t-1870556725
>|||I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672
I need to upload multiple csv files into one dts.
1)
Is there a simple standard way to retrieve the list of csv file withi
the dts ?
2)
to process the csv files (various file name) I tried to use
opendatasource (as I did for excel files) :
select * from
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Dat
Source="e:\tst.csv";Extende
properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
Unfortunatly, I does not work , the message is :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
Make sure that the path name is spelled correctly and that you ar
connected to the server on which the file resides.]
my path is correct, it might be a syntax & parameter error but I can'
find a good help on the web
any one can help ?
Thanks in advance
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672Have a look at
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"dominique deboise" <dominique.deboise.1r1e0t@.no-mx.msusenet.com> wrote in
message news:dominique.deboise.1r1e0t@.no-mx.msusenet.com...
> Hi all,
> I need to upload multiple csv files into one dts.
> 1)
> Is there a simple standard way to retrieve the list of csv file within
> the dts ?
> 2)
> to process the csv files (various file name) I tried to use
> opendatasource (as I did for excel files) :
> select * from
> OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data
> Source="e:\tst.csv";Extended
> properties="text;HDR=Yes;FMT=Delimited"')...CLASSES#CSV
>
> Unfortunatly, I does not work , the message is :
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: 'e:\tst.csv' is not a valid path.
> Make sure that the path name is spelled correctly and that you are
> connected to the server on which the file resides.]
>
> my path is correct, it might be a syntax & parameter error but I can't
> find a good help on the web
> any one can help ?
>
> Thanks in advance
>
> Dominique
>
> --
> dominique deboise
> ---
> dominique deboise's Profile:
> http://www.msusenet.com/member.php?userid=2231
> View this thread: http://www.msusenet.com/t-1870556725
>|||I have used the Jasper Smith's solution and it works fine.
Thanks for the help
Dominiqu
--
dominique debois
----
dominique deboise's Profile: http://www.msusenet.com/member.php?userid=223
View this thread: http://www.msusenet.com/t-187055672
Wednesday, March 7, 2012
DTS - Import Text file - varying column count
I receive a pipe delimited file without headers, and a header and trailer
row added as a check to each file. When I say header I don't mean column
headings, I mean a row of information that verifies the contents of the file
(example below)
The header info contains 5 columns, trailer info contains 7 columns, and the
normal data contains 28 columns.
The import succeeds when there is data (the header/trailer rows inserts NULL
into the remaining columns which is fine), however when there are no rows,
only the header and trailer rows are in the file. This fails as the columns
from 8 onwards are not there.
Firstly, can I make the DTS not "fail" if the full number of columns are
present? Currently I'm using "on completion" at the upload which continues
the DTS processing, but it fails at the end.
Second option - can I do a quick check to see if there are only 2 rows in
the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
work, then delete the NULL row?
example file:
IMPORT_HEADER|HR|2006|04|18
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
IMPORT_TRAILER|TR|2006|04|18|3|123.99
Thanks,Hi
You may find better advice in the DTS newsgroup
microsoft.public.sqlserver.dts , but you may be able to use an activeX
transformation to determine if you are reading a header row, data row or
footer assuming that the first column defines the row type.
Check out http://www.sqldts.com/default.aspx?279 and possibly
http://www.sqldts.com/default.aspx?266 and
http://www.sqldts.com/default.aspx?282. You should also be able to skip the
insertion of the header and trailer by returning DTSTransformStat_SkipInsert
or DTSTransformStat_NoMoreRows (if the row is the footer or the row is the
header and it says there are no rows within the file!) to make it cleaner
see the topic "DTSTransformStatus" in Books online for more.
John
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:OM91g.40851$Ph2.9628@.newsfe4-gui.ntli.net...
>I receive a pipe delimited file without headers, and a header and trailer
>row added as a check to each file. When I say header I don't mean column
>headings, I mean a row of information that verifies the contents of the
>file (example below)
> The header info contains 5 columns, trailer info contains 7 columns, and
> the normal data contains 28 columns.
> The import succeeds when there is data (the header/trailer rows inserts
> NULL into the remaining columns which is fine), however when there are no
> rows, only the header and trailer rows are in the file. This fails as the
> columns from 8 onwards are not there.
> Firstly, can I make the DTS not "fail" if the full number of columns are
> present? Currently I'm using "on completion" at the upload which continues
> the DTS processing, but it fails at the end.
> Second option - can I do a quick check to see if there are only 2 rows in
> the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
> work, then delete the NULL row?
> example file:
> IMPORT_HEADER|HR|2006|04|18
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> IMPORT_TRAILER|TR|2006|04|18|3|123.99
> Thanks,
>
row added as a check to each file. When I say header I don't mean column
headings, I mean a row of information that verifies the contents of the file
(example below)
The header info contains 5 columns, trailer info contains 7 columns, and the
normal data contains 28 columns.
The import succeeds when there is data (the header/trailer rows inserts NULL
into the remaining columns which is fine), however when there are no rows,
only the header and trailer rows are in the file. This fails as the columns
from 8 onwards are not there.
Firstly, can I make the DTS not "fail" if the full number of columns are
present? Currently I'm using "on completion" at the upload which continues
the DTS processing, but it fails at the end.
Second option - can I do a quick check to see if there are only 2 rows in
the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
work, then delete the NULL row?
example file:
IMPORT_HEADER|HR|2006|04|18
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
datarows|etc|etc|28 columns in total
IMPORT_TRAILER|TR|2006|04|18|3|123.99
Thanks,Hi
You may find better advice in the DTS newsgroup
microsoft.public.sqlserver.dts , but you may be able to use an activeX
transformation to determine if you are reading a header row, data row or
footer assuming that the first column defines the row type.
Check out http://www.sqldts.com/default.aspx?279 and possibly
http://www.sqldts.com/default.aspx?266 and
http://www.sqldts.com/default.aspx?282. You should also be able to skip the
insertion of the header and trailer by returning DTSTransformStat_SkipInsert
or DTSTransformStat_NoMoreRows (if the row is the footer or the row is the
header and it says there are no rows within the file!) to make it cleaner
see the topic "DTSTransformStatus" in Books online for more.
John
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:OM91g.40851$Ph2.9628@.newsfe4-gui.ntli.net...
>I receive a pipe delimited file without headers, and a header and trailer
>row added as a check to each file. When I say header I don't mean column
>headings, I mean a row of information that verifies the contents of the
>file (example below)
> The header info contains 5 columns, trailer info contains 7 columns, and
> the normal data contains 28 columns.
> The import succeeds when there is data (the header/trailer rows inserts
> NULL into the remaining columns which is fine), however when there are no
> rows, only the header and trailer rows are in the file. This fails as the
> columns from 8 onwards are not there.
> Firstly, can I make the DTS not "fail" if the full number of columns are
> present? Currently I'm using "on completion" at the upload which continues
> the DTS processing, but it fails at the end.
> Second option - can I do a quick check to see if there are only 2 rows in
> the file, and if so, maybe throw in a line of 27 pipes in to make the DTS
> work, then delete the NULL row?
> example file:
> IMPORT_HEADER|HR|2006|04|18
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> datarows|etc|etc|28 columns in total
> IMPORT_TRAILER|TR|2006|04|18|3|123.99
> Thanks,
>
Subscribe to:
Posts (Atom)