Converting file into new format

[Freefall27]

I am using Access 2007 on windows 7 and need assistance with rearranging a file to match other file imports.

Currently pull file # 1 down from vendor product in .csv format.

File # 1
NO HEADER IN FILE RECEIVED
RPDG Landmark Partners Richmond VA 10.1 10.2 10.3 10.4 10.5


The master file is in format below and updated internally on a daily basis. I pull this file down to computer in .csv format.
I need to convert the file #1 data into format below and then compare against master file (same format) for daily changes.
File # 2
Location Network
RPDG Landmark Partners Richmond VA 10.1
RPDG Landmark Partners Richmond VA 10.2
RPDG Landmark Partners Richmond VA 10.3
RPDG Landmark Partners Richmond VA 10.4
RPDG Landmark Partners Richmond VA 10.5

 

[dhookom]

We can't determine from your post if there is one field or many? I expect you could use a union query to convert File #1 format into File #2 format.

Duane
Hook'D on Access
MS Access MVP

 

[Freefall27]

My apologies I should have separated better.

NO HEADER IN FILE RECEIVED
RPDG Landmark Partners Richmond VA - 10.1 - 10.2 - 10.3- 10.4 - 10.5 (6 fields here but there will be many more in some areas ...10.6 -> 10.50...etc


The master file is in format below and updated internally on a daily basis. I pull this file down to computer in .csv format.
I need to convert the file #1 data into format below and then compare against master file (same format) for daily changes.
File # 2
Location Network
RPDG Landmark Partners Richmond VA - 10.1
RPDG Landmark Partners Richmond VA - 10.2
RPDG Landmark Partners Richmond VA - 10.3
RPDG Landmark Partners Richmond VA - 10.4
RPDG Landmark Partners Richmond VA - 10.5

 

[Freefall27]

didn't copy correctly

Location Network
RPDG Landmark Partners Richmond VA - 10.1 (2 fields)

 

[dhookom]

What do you mean by "pull this file down to computer"? Are you importing into tables or linking? Is "File #2" the "master file"?

Duane
Hook'D on Access
MS Access MVP

 

[Freefall27]

Yes, the vendor product has an export utility to save the file in various formats. I save to hard drive in .csv and that file is linked in database.

File #2 is how the data needs to be rearranged in file #1. The master file is in file #2 format and essentially would be the same other than the new entries that need to be provided using unmatched query.

 

[SkipVought]

Your .csv (COMMA Separated Values) file example has no COMMAS???

Did you COPY n PASTE this example (the bast way) or did you type in the examples, the second of which has DASH delimiters???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I would make a new table to hold file 1 data formatted and clear it out after comparisons.

To fill it in you are going to need VBA of some sort and the Split and Ubound functions are going to be key in helping break your packed column up into records.

Whether you use some sort of loop to run multiple queries where you build the SQL statements programatically - one query for each column up to the maximum number of columns or use recordsets is entirely up to you.

To determine your max number of columns you might use something like...

SELECT MAX(Ubound(Split(<Column2>,"-"))) 
From <Table>

That assumes that the dash is your value separator as in your example and also demonstrates the syntax a bit.

 

[SkipVought]

What? A .dsv file? ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I'm assuming it is a CSV where the second column contains multiple values separated by a dash ("-")... But the Original Post has spaces so maybe it is a space as a delimiter instead.

 

[SkipVought]

First you really do need to know EXACTLY what delimiter in you master file is. If this is generated internally, I'd move heaven & earth to find the source data and query that rather than depending on a crosstab REPORT as a source!

Short of that nirvana, you could use this nifty tip from Excel: NORMALIZE Your Table using the PivotTable Wizard faq68-5287.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I see where I got tripped up...

didn't copy correctly

Location Network
RPDG Landmark Partners Richmond VA - 10.1 (2 fields)

I MISREAD that as definitively meaning file 1 has 2 columns/fields when in fact that is file 2 or the master.

Woops... So yeah Unpivot like Skips FAQ in Excel, or import the table and use something like the below expression to find out how many times to loop through appending each column to a new table.

currentdb.tabledefs("<LinkedTable1>").Fields.Count - 1

Access Caveat, there is a column count limit so there may be a limit to using this. Excel Caveat, there is a row limit and you'll have to do the steps each time unless you record/write a macro to do it.

There are also methods for reading the file one line at a time in code and then you can use split and a loop to write out the data to a recordset (table).