Matching two files received in vertical and horizontal format

[Freefall69]

I am running Access 2007 on windows 7 and need some assistance with matching up two files received every day since one is vertical and the other horizontal.

File #1 (Areas listed as below)
Account Area identifier
Clarksville 86.32.46.1
Clarksville 85.62.34.19
Smithtown 92.65.22.17
Smithtown 92.65.186.21
Jonesville 92.65.22.17

File #2 (Areas listed as below)
No Headers
Clarksville 86.32.46.1 85.62.34.19
Smithtown 92.65.22.17 92.65.186.21
Jonesville 92.65.22.17

I get each file in csv format daily and have to compare for differences but need to get into common format I believe? There are thousands of records in each file. I would appreciate any direction to get this process going. If you need more information, please let me know.

 

[dhookom]

Apparently there are 2 fields in File1 and 3 in File2? Are there potentially more columns/fields in file2? I believe the common format is to create a union query based on file2 so it is normalized like File1. You can then compare them.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

It does not look like a csv format. For the file to be csv format you need a comma (Comma Separated Values ) as a separator. It looks to me you have text files with the SPACE as a separator.

You can use some VBA to SPLIT a line of data from each file and compare them.

Have fun.

---- Andy

 

[Freefall69]

File #1 lists a separate row for each occurrence of Clarksville.

File #2 lists all IP Addresses for each area across. I have some where there are 26 columns for an area. So yes, there are more columns in file 2 since it will vary by area.
Clarksville 86.32.46.1 85.62.34.19 etc. etc.

Can you offer some more info on creating a union query just based on file#2. I would need to take both files each morning, compare for differences, and then create a query to pull out differences for import back into system. (file #2 is directly out of system that needs to be updated.)

 

[Freefall69]

The files are in csv format and I have just typed out the example using notepad. Both sytem have been built to export files in csv format and now I just need to compare for differences.

 

[dhookom]

I would import File 2 into a table named File2 with the fields are named Field1, Field2, Field3, ... Field30 (or the max number expected)

You could create a union query like:

--- quniFile2 ----

SELECT Field1, Field2
FROM File2
WHERE Field2 is not null
UNION ALL
SELECT Field1, Field3
FROM File2
WHERE Field3 is not null
UNION ALL
SELECT Field1, Field4
FROM File2
WHERE Field4 is not null
UNION ALL
SELECT Field1, Field5
FROM File2
WHERE Field5 is not null
UNION ALL
SELECT Field1, Field6
FROM File2
WHERE Field6 is not null
UNION ALL
SELECT Field1, Field7
FROM File2
WHERE Field7 is not null
--- etc ---
UNION ALL
SELECT Field1, Field30
FROM File2
WHERE Field30 is not null

You can then use an unmatched query involving quniFile2 and File1.

If there is a possibility that more fields might be encountered in the future, you could link to the csv file and use code to step through the records and append field values to a normalized table.


Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

I have just typed out the example using notepad

WHY???

What's wrong with COPY 'n' PASTE to get an accurate representation of your data?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Freefall69]

dhookom,

That works to get file in proper format.

Thank You!