Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: 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 ????
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not just bcp it in to a table?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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 ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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

    Code:
    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 ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    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
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •