How to compare data file with column in SQL server database?

Question

I am required to compare data in a file with a column in SQL server database. I have been asked to use Microsoft Access to do the same.

Additional information: File consists of many lines of text. Amongst the text is the a word that matches the column name of SQL server database.

How should go about doing the comparison? If I use MS Access then I would write queries to extract information from SQL server but I don't know how to use (I am not sure if it possible) MS Access to compare information from file with information in database.

UPDATE

File structure:

    ABCDE       12324            ABCDE    ABCDE
    ABCDE       WORDSWORDSWORDS WORDS
    VERSION     NUMBER1.1   IDENTIFIER:129090
    WORDS WORDS WORDS
    WORDS WORDS
    ABCDE       22324            ABCDE    ABCDE
    ABCDE       WORDSWORDSWORDS WORDS
    VERSION NUMBER2.1 IDENTIFIER:129090
    WORDS WORDS WORDS
    WORDS WORDS
    ABCDE       32324            ABCDE    ABCDE
    ABCDE       WORDSWORDSWORDS WORDS
    VERSION XUMBER1.1 IDENTIFIER:129090
    WORDS WORDS WORDS
    WORDS WORDS

Database structure:

Column1 Column2 Column3 Accession_Ver Column4

234     value2  value3  NUMBER1.1         value4
456     value5  value6  NUMBER2.1         value7

I have to compare the value after word 'Version' (i.e NUMBER1.1) in data file with value in column name 'Accession_Ver' (i.e NUMBER1.1) in database. After comparison, if values are different then store them otherwise move on with comparison.

I can use Perl to parse the data file but I am not sure how will I use MS Access to connect the file and database to compare information.

Answer 1

You most likely want to get the file imported into a staging table in the database. Once the data is there you can easily write queries against the data.

Answer 2

It is possible to attach an SQL server table to Access or to get a recordset with ADO (http://www.connectionstrings.com/sql-server-2008). It is also possible to read a textfile line by line in VBA or to attach it to Access, if the structure is consistent.

It seems to me that you may wish to read the file line by line in VBA using, for example, the FileSystemObject, and then use ADO to run a suitable query, based on the line you have examined.

Answer 3

Few ways, but here is my two cents.

1. Extract the required info into a new file in column-format, think csv.
2. Import the new file into a table.
3. Compare within DB.

OR

If you have SQL server 2005/2008 standard/dev/enterprise, than you have SSIS. Within SSIS you can connect to databases, flat files, XMl, etc.. and process records.

Answer 4

If the text file has fixed or delimited format, then you can use Microsoft ODBC text driver (or DataDirect ODBC text driver) to create a DSN for the text file. Then use an ODBC data comparison/sync tool such as Zidsoft CompareData to copmare the two data sources table data