Opendatasource for text file

Ok .. Gurus ... here's my problem

I am able to successfully run a opendatasource query against a flat file from SQL Server. However the problem I am facing is that the resultset that is returned has a line as one column and one row ... is there any way i can get the opendatasource query to recognize tabs as column seperators ??Why not just bcp it in to a table?|||coz brett .. i ve got a huge problem with BCP ...

It needs a table structure to be defined ... and i dont have that luxury ... I need to take any flat file ... check if it meets my requirements and if it does only then load it into a table ... otherwise display an error...

The data files are going to come in from different locations ... either as a flat file or as a excel file ... dont have control on that ... have got to check the files coming in for different validity criterion ...

The files coming in might be having more columns than needed ... and i need to ignore those.

Hope you get an idea of the mess I am in ... need help desperately ...|||EXCEL or csv?

BIG difference...

I would think about this

CREATE TABLE myTable99(Col1 varchar(8000))
GO

And bcp everything in to that...then interogate the data...

and you say you have a final destination table anyway...

how do get the data from these different source to match in tyhe first place...do they all have different layouts?

How do you know what layout to use?

Do you want the pong software back for your new laptop?|||Either Excel or Tab Delimited Excel file ...

And bcp everything in to that...then interogate the data...

same thing as using a linked server ... forgetting about the performance part ...

final destination table is defined but data is going to come from different sales people ... some have access .. some have msde ... some have excel ...

heres what i have till now

CREATE TABLE [File_Column_Format] (
[DataSource] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ColName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DataType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[length] [int] NULL ,
CONSTRAINT [PK_File_Column_Format] PRIMARY KEY CLUSTERED
(
[DataSource],
[ColName]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [FileNameFormats] (
[DataSource] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Format_type] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

These are the tables i am using to define what I want ... and validate the files I m being sent ... i can validate the excel files using this method ... but what about flat text files ?|||Are you using a config table to define the different file structures?

Also, How many different people?

Can't you just distribute an Access database and have it create the same csv file from everyone?

What a nightmare...

My sympathies...|||Originally posted by Brett Kaiser
Are you using a config table to define the different file structures?


Not for file structures .. but only for the table that the data is going into ... if any of the fields do not match the datatype or size described for it ... throw an error expressing .. row no and col no.
Originally posted by Brett Kaiser
Also, How many different people?

Say about 250

Originally posted by Brett Kaiser
Can't you just distribute an Access database and have it create the same csv file from everyone?

Can you elaborate on that ?

Originally posted by Brett Kaiser
What a nightmare...


[Wakes Up] Scream [/Wakes up]

[Goes back to sleep]zzzzzzzzzz[/Goes back to sleep]
Originally posted by Brett Kaiser
My sympathies...

Really need that ... and I thought ETL was easy :)|||I would just recreate the final destination table in Access...give them a form...so they can do their data entry...have Access export the data as a csv...place the mdb in a read only folder on the server

let them get copies from that location...have a single location that the need to deliver the data..

(they're mailing it to you right?)

That way they can't screw it up...because they will