It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELEC
T
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see wha
t
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
MauryMaury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=
3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as
a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury|||>> Why don't you bulk insert the file into a working table, setting
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?sql